Wednesday, November 26, 2025

, ,

MS SQL Server Interview Questions - Part 1

 Microsoft SQL Server continues to be one of the most widely used relational database platforms in the industry. Whether you are preparing for roles such as SQL Developer, MSBI Developer, Data Engineer, Database Administrator (DBA), or Support Engineer, this comprehensive set of interview questions will help you cover all fundamental to advanced concepts.

This guide is structured topic-wise for easier learning and quick revision.

1. Basic SQL Concepts

1. What is SQL Server?

A relational database management system (RDBMS) developed by Microsoft that supports transaction processing, analytics, and business intelligence workloads.

2. What are tables, rows, and columns?

  • Table: Structured set of data.

  • Rows: Records in a table.

  • Columns: Attributes of the data.

3. What are Primary Keys?

A unique identifier for each row in a table. Cannot contain NULL values.

4. What is a Foreign Key?

A field that establishes a relationship between two tables.

5. What is a Unique Key?

Ensures all values in the column are distinct; allows a single NULL.

2. SQL Queries (DML / DQL)

6. Difference between WHERE and HAVING?

  • WHERE: Filters rows before grouping.

  • HAVING: Filters groups after aggregation.

7. What is a JOIN? Name different types.

Combines rows from multiple tables.
Types: INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF JOIN.

8. What is a Subquery?

A query within another query.

9. Write a query to find the second highest salary.

SELECT MAX(Salary)
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

10. How do you find duplicate records?

SELECT Name, COUNT(*) 
FROM Employees 
GROUP BY Name 
HAVING COUNT(*) > 1;

3. DDL Commands

11. What is the difference between TRUNCATE and DELETE?

  • DELETE: Logs each row, supports WHERE, slower.

  • TRUNCATE: Removes all rows, minimal logging, resets identity.

12. What is the ALTER command used for?

Used to modify an existing table schema (add/modify/drop columns).

4. Constraints

13. Types of Constraints?

PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.

14. What is a CHECK constraint?

Ensures values meet a condition.

5. Normalization & Schema Design

15. What is Normalization?

A process to organize data to reduce redundancy and improve data integrity.

16. Name different Normal Forms.

1NF, 2NF, 3NF, BCNF, 4NF, 5NF.

17. What is Denormalization?

Introducing redundancy to improve read performance.

6. Transactions & ACID Properties

18. Define ACID properties.

Atomicity, Consistency, Isolation, Durability.

19. What is a Transaction?

A logical unit of work executed as a single operation.

20. What are Isolation Levels?

Read Uncommitted, Read Committed, Repeatable Read, Serializable, Snapshot.

7. Functions (Scalar & Aggregate)

21. Difference between Scalar & Aggregate functions?

  • Scalar: Returns a single value.

  • Aggregate: Works on a set of rows.

22. Example of Scalar Function?

LEN(), UPPER(), GETDATE()

23. Example of Aggregate Function?

SUM(), AVG(), MAX(), COUNT()

8. Stored Procedures, Functions, Views, Triggers

24. What is a Stored Procedure?

Precompiled set of SQL statements that can accept parameters.

25. What is the difference between Function and Stored Procedure?

FunctionStored Procedure
Must return valueOptional return
Cannot modify DB stateCan modify DB
Can be used in SELECTCannot be used in SELECT

26. What is a View?

A virtual table built from a query.

27. What is a Trigger?

Automatic execution of SQL code when an event occurs (Insert/Update/Delete).

9. Indexing (Clustered & Non-Clustered)

28. What is an Index?

Improves query performance by optimizing search operations.

29. Difference between Clustered and Non-Clustered Index?

  • Clustered: Defines physical order of data. One per table.

  • Non-Clustered: Logical order stored separately. Many per table.

30. What is a Covering Index?

An index containing all columns required by a query.

10. Performance Tuning

31. How do you analyze slow queries?

Using execution plans, SET STATISTICS IO/TIME, indexing analysis.

32. What are Execution Plans?

A graphical/text representation showing how SQL Server executes a query.

33. What causes Table Scans?

Missing indexes, poor filter conditions.

34. How do you reduce fragmentation?

Rebuild or reorganize indexes.

11. Joins – Advanced Questions

35. What is the difference between INNER and OUTER JOIN?

  • INNER: Returns matching rows only.

  • OUTER: Returns matching + non-matching rows.

36. What is CROSS APPLY vs OUTER APPLY?

Used with table-valued functions to join with correlated queries.

12. SQL Server Architecture

37. What is SQL Server Agent?

Used for scheduling jobs (backups, scripts, maintenance).

38. What is TempDB?

A system database used for temporary objects and sorts.

39. What is a Checkpoint?

Process of writing dirty pages from buffer cache to disk.

13. Backups & Recovery

40. Types of Backups?

Full, Differential, Transaction Log, File/Filegroup.

41. What is Point-in-Time Recovery?

Restoring the database to a specific moment using T-Logs.

14. Security

42. What is Authentication vs Authorization?

  • Authentication: Identity verification.

  • Authorization: Access rights.

43. What are SQL Server Roles?

Server-level and database-level predefined roles for access control.

15. Advanced Topics

44. What is Partitioning?

Splitting a large table into smaller pieces.

45. What is Replication?

Copying and distributing data across servers.

46. What is AlwaysOn Availability Group?

High availability and disaster recovery feature.

47. What is SQL Profiler?

Tool for tracing SQL Server events.

48. What is Deadlock?

Two processes waiting on each other’s resources.

49. How do you detect Deadlocks?

Using SQL Profiler, Extended Events, or by reviewing error logs.

50. How do you resolve Deadlocks?

Optimize queries, add indexes, ensure consistent locking order.

Monday, November 24, 2025

, ,

SSRS Report Migration

 

Migrate SQL Server Reporting Services (SSRS)

Abstract

This document presents a structured overview of the available approaches for migrating SQL Server Reporting Services (SSRS) from an existing environment to a target server. It highlights best practices, explains the technical workflow involved in each method, and discusses key considerations and challenges associated with SSRS migration. The objective is to provide a reliable reference for database administrators and BI engineers managing enterprise reporting infrastructure.

Migration Approaches

Four primary approaches are commonly used when migrating SSRS workloads:

  1. Manual step-by-step migration of reports and configurations

  2. Migration using Visual Studio (SSDT) by loading and redeploying RDL files

  3. Automated migration using the rs.exe scripting utility

  4. Migration via third-party tools

Each of these is discussed in detail below, along with associated advantages and limitations.

Approach 1: Manual Step-by-Step Migration

This is the most controlled method and is commonly used in environments that require precise validation of content, folder structure, permissions, and configurations.

Advantages

  • Ensures all reports are deployed accurately to their intended folders.

  • Offers high visibility and control throughout the migration process.

Disadvantages

  • Time-consuming, especially for large SSRS ecosystems.

High-Level Workflow

1. Backup SSRS Databases (Source Server)

Both SSRS databases must be backed up:

-- Backup ReportServer
BACKUP DATABASE [ReportServer]
TO DISK = N'C:\backups\ReportServer_migration.bak'
WITH INIT, NAME = N'ReportServer-Full Database Backup',
SKIP, COMPRESSION, STATS = 10;

-- Backup ReportServerTempDB
BACKUP DATABASE [ReportServerTempDB]
TO DISK = N'C:\backups\ReportServerTempDB_migration.bak'
WITH INIT, NAME = N'ReportServerTempDB-Full Database Backup',
SKIP, COMPRESSION, STATS = 10;

2. Backup SSRS Encryption Key

Using the Reporting Services Configuration Manager:

  1. Open Encryption Keys

  2. Click Backup

  3. Provide a backup location

  4. Set a password

  5. Confirm and complete the backup

Restore on Target Server

1. Stop Reporting Services

Using SSRS Configuration Manager → Stop the service.

2. Restore SSRS Databases

Restore both ReportServer and ReportServerTempDB databases to the target SQL instance.

RESTORE DATABASE [ReportServer]
FROM DISK = N'C:\backups\ReportServer_migration.bak'
WITH MOVE 'ReportServer' TO '...\ReportServer.mdf',
MOVE 'ReportServer_log' TO '...\ReportServer_log.ldf',
REPLACE, STATS = 5;

ALTER DATABASE [ReportServer] SET COMPATIBILITY_LEVEL = 140;
ALTER AUTHORIZATION ON DATABASE::[ReportServer] TO [sa];

DBCC CHECKDB([ReportServer]) WITH NO_INFOMSGS;

Repeat for the TempDB.

Synchronize Users and Fix Orphan Accounts

Query users:

SELECT name FROM ReportServer.sys.sysusers;
SELECT name FROM ReportServerTempDB.sys.sysusers;

Remove outdated service accounts:

DROP SCHEMA [NT SERVICE\ReportServer$SQL2012];
DROP USER   [NT SERVICE\ReportServer$SQL2012];

Add required service identities and assign roles on the target:

CREATE USER [NT SERVICE\SQLServerReportingServices]
FOR LOGIN [NT SERVICE\SQLServerReportingServices];

ALTER ROLE [RSExecRole] ADD MEMBER [NT SERVICE\SQLServerReportingServices];
ALTER ROLE [db_owner]   ADD MEMBER [NT SERVICE\SQLServerReportingServices];

Restore Encryption Key

After database restoration, restore the encryption key:

  1. Open Encryption Keys

  2. Select Restore

  3. Provide key backup file and password

Fix “Scale-Out Deployment Not Supported” Error

Delete old instance entries:

DELETE FROM [ReportServer].[dbo].[Keys]
WHERE InstanceName LIKE 'SQL2012';

Final Validation

  • Start SSRS

  • Open the Web Portal

  • Validate folder hierarchy, reports, and subscriptions

  • Execute smoke tests on all critical reports

Approach 2: Deploying Reports Using Visual Studio (SSDT)

Advantages

  • Clean deployment structure with ability to reorganize content

  • Easy elimination of outdated or unused reports

  • Enables proper source control integration (Azure DevOps, GitHub, etc.)

Disadvantages

  • Requires more manual steps for configuration

  • Time-intensive for large environments

Steps

1. Backup Deployed Reports via rs.exe Script

Run the backup script:

rs -s http://localhost/reportserver 
   -i D:\Scripts\Backup_Reports.rss 
   -e Mgmt2010 
   -v backupFolder="D:\Scripts\BackupReports" 
   -v parentFolder=""

2. Create Visual Studio Solution

  • Create one project per report folder

  • Import RDL files

  • Configure shared data sources

  • Configure shared datasets

3. Use Azure DevOps for Source Control

Version-control RDL files, data sources, and data sets.

4. Configure Deployment Settings

Using Configuration Manager → set target server URL, folder paths.

5. Build and Deploy

Deploy either full solution or individual reports.

Approach 3: Migration Using rs.exe Utility

This is the fastest automated option but requires scripting knowledge.

Advantages

  • Very fast for bulk migration

  • Repeatable and script-friendly

Disadvantages

  • Complex for beginners

  • Requires careful credential and parameter handling

Example rs.exe Command

rs.exe -i ssrs_migration.rss -e Mgmt2005 
      -s http://MyServer/ReportServer2008 
      -u MyDomain\Reports -p MyPassword 
      -v ts="http://MyServer/ReportServer2012" 
      -v tu="MyDomain\Reports2012" 
      -v tp="MyPassword"

Approach 4: Migration Using Third-Party Tools

Common tools include:

1. Reporting Services Scripter

Useful for:

  • Extracting folder structures

  • Extracting RDL files

  • Migrating subscriptions

Documentation:
https://www.mssqltips.com/sqlservertip/2627/migrating-sql-reporting-services-to-a-new-server/

References

  1. Microsoft – SSRS Migration Guide

  2. Moving SSRS Databases to Another Server

  3. RS.exe Utility Documentation

  4. SSRS Migration Sample Scripts (GitHub)

  5. SQLShack – Low-Risk Database Migration Practices

  6. Reporting Services Backward Compatibility

  7. SSRS Configuration Tool Overview

🎯 Your Article is Now R&D-Ready

This rewritten version:

✔ Reads like a technical document

✔ Showcases real enterprise SQL Server domain expertise

✔ Fits perfectly into your Documentation Portal theme

✔ Provides clean structure, easy navigation, and strong clarity

✔ Demonstrates practical SSRS migration knowledge for career/portfolio use

Sunday, November 23, 2025

, , , , , , ,

How to Check SQL Server Version Using T-SQL

 

Understanding SQL Server Versions and How to Query Them

Microsoft SQL Server continues to evolve with new features, performance improvements, and security enhancements in each version. As developers and administrators, knowing the exact version of SQL Server running in your environment is essential for:

  • Compatibility and upgrade planning

  • Troubleshooting and patch management

  • Feature availability validation

  • Performance and security considerations

This post explains how SQL Server versioning works and demonstrates queries used to retrieve version information from your database instance.

📌 SQL Server Version Structure

SQL Server uses a structured versioning format:

Major Version . Minor Version . Build Number . Revision

For example:

Microsoft SQL Server 2019 (RTM) – 15.0.2000.5

🔎 Querying SQL Server Version Information

You can retrieve version details using several built-in commands.

1️⃣ Basic Version Query

Returns full version string including edition and OS.

SELECT @@VERSION;

Example Output:

Microsoft SQL Server 2022 (RTM-CU8) Enterprise Edition 
on Windows Server 2022 Datacenter

2️⃣ Query Server Properties in Detail

More structured metadata including product level, build number, and edition.

SELECT  
    SERVERPROPERTY('ProductVersion') AS [Version],
    SERVERPROPERTY('ProductLevel')   AS [Patch Level],
    SERVERPROPERTY('Edition')        AS [Edition],
    SERVERPROPERTY('EngineEdition')  AS [Engine Edition],
    SERVERPROPERTY('ProductName')    AS [Product Name];

3️⃣ Check Compact Version Components

Useful for verifying compatibility.

SELECT  
    PARSENAME(CONVERT(VARCHAR(100), SERVERPROPERTY('ProductVersion')), 4) AS [Major],
    PARSENAME(CONVERT(VARCHAR(100), SERVERPROPERTY('ProductVersion')), 3) AS [Minor],
    PARSENAME(CONVERT(VARCHAR(100), SERVERPROPERTY('ProductVersion')), 2) AS [Build],
    PARSENAME(CONVERT(VARCHAR(100), SERVERPROPERTY('ProductVersion')), 1) AS [Revision];

🧩 Why Version Awareness Matters

Version CheckBenefit to Developer
Feature AvailabilityKnowing if a feature like Temporal Tables (SQL 2016+) is supported
Security & Patch StatusEnsuring databases are protected against vulnerabilities
Performance ImprovementsUtilizing new query engine enhancements
Deployment PlanningPreventing backward-compatibility issues

🎯 Developer Tip

Always document version information before:

It helps in validating prerequisites and planning rollback strategies if needed.

Conclusion

Identifying your SQL Server version is a simple but powerful step in ensuring your environment is secure, compatible, and fully optimized. Using the above queries, you can instantly assess the platform your databases rely upon.