50 SQL Server Interview Questions (With Answers)

SQL Server interviews test your understanding of T-SQL, indexing strategies, query optimisation, transaction isolation, high availability, and administration. This guide covers 50 of the most common questions with concise answers and T-SQL code examples.

Quick reference

Architecture

1. Describe the SQL Server engine architecture.

SQL Server consists of two major components:

SQLOS (SQL Server Operating System Layer) — manages threads, memory, I/O, and scheduling. SQL Server uses a cooperative scheduling model (SQLOS schedulers, not OS threads directly).

Relational Engine — includes:

• Query Parser — parses T-SQL into a parse tree
• Algebrizer — resolves names and types, produces a bound tree
• Query Optimiser — generates and picks the cheapest execution plan
• Query Executor — executes the plan using the Storage Engine

Storage Engine — includes:

• Buffer Manager — manages the Buffer Pool (in-memory data and index pages)
• Transaction Manager — handles locking, logging, and ACID
• Access Methods — B-tree traversal, full-table scans
• Log Manager — manages the Write-Ahead Log (WAL) / .ldf file

Client
  ↓
Protocol Layer (TDS)
  ↓
Relational Engine: Parser → Algebrizer → Optimiser → Executor
  ↓
Storage Engine: Buffer Pool ↔ Data Files (.mdf/.ndf) + Log (.ldf)

2. What is the Buffer Pool and how does it work?

The Buffer Pool is SQL Server's main memory cache for 8 KB data and index pages.

• When a query needs a page, SQL Server first checks the Buffer Pool (logical read). If found, it uses it.
• If not found, it reads from disk (physical read) and loads the page into the Buffer Pool.
• A background process called Lazy Writer evicts old pages when memory pressure occurs (LRU-like algorithm called clock sweep).
• Buffer Pool Extensions (BPE) — on Standard edition you can extend the buffer pool to SSD.

-- Check buffer pool hit ratio
SELECT
  object_name,
  counter_name,
  cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio';

A healthy ratio is > 95 %.

3. What is the difference between a data file and a log file?

T-SQL

4. What are CTEs and when should you use them?

A Common Table Expression (CTE) is a named temporary result set scoped to a single statement.

-- Simple CTE
WITH TopSales AS (
    SELECT
        SalesPersonID,
        SUM(TotalDue) AS TotalSales,
        RANK() OVER (ORDER BY SUM(TotalDue) DESC) AS rnk
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT * FROM TopSales WHERE rnk <= 5;

Recursive CTE — for hierarchical data:

WITH OrgChart AS (
    -- Anchor: top-level employees
    SELECT EmployeeID, ManagerID, Name, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive: employees reporting to the above
    SELECT e.EmployeeID, e.ManagerID, e.Name, oc.Level + 1
    FROM Employees e
    INNER JOIN OrgChart oc ON e.ManagerID = oc.EmployeeID
)
SELECT * FROM OrgChart OPTION (MAXRECURSION 100);

Use CTEs for: readability, recursive queries, avoiding repeated subqueries.

5. Explain window functions with examples.

Window functions perform calculations across a set of rows related to the current row without collapsing the result set.

SELECT
    OrderID,
    CustomerID,
    OrderDate,
    TotalDue,

    -- Running total per customer
    SUM(TotalDue) OVER (
        PARTITION BY CustomerID
        ORDER BY OrderDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningTotal,

    -- Rank within customer
    RANK() OVER (
        PARTITION BY CustomerID
        ORDER BY TotalDue DESC
    ) AS SalesRank,

    -- Previous order amount
    LAG(TotalDue, 1, 0) OVER (
        PARTITION BY CustomerID
        ORDER BY OrderDate
    ) AS PrevOrderAmt

FROM Sales.SalesOrderHeader;

6. What is the MERGE statement?

MERGE performs INSERT, UPDATE, and DELETE in a single statement based on matching rows between a source and a target.

MERGE INTO Products AS target
USING ProductUpdates AS source
    ON target.ProductID = source.ProductID
WHEN MATCHED AND target.Price <> source.Price THEN
    UPDATE SET target.Price = source.Price,
               target.UpdatedAt = GETUTCDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, Name, Price)
    VALUES (source.ProductID, source.Name, source.Price)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action, deleted.ProductID, inserted.ProductID;

Caution: MERGE has historically had several bugs in SQL Server. For simple upserts, prefer explicit IF EXISTS ... UPDATE ... ELSE INSERT patterns or INSERT ... ON CONFLICT equivalent workarounds.

7. How does TRY/CATCH work in T-SQL?

BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO Orders (CustomerID, OrderDate) VALUES (1, GETUTCDATE());
    INSERT INTO OrderItems (OrderID, ProductID, Qty)
    VALUES (SCOPE_IDENTITY(), 999, 1); -- might fail (FK)

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    SELECT
        ERROR_NUMBER()    AS ErrorNumber,
        ERROR_SEVERITY()  AS Severity,
        ERROR_STATE()     AS State,
        ERROR_PROCEDURE() AS Procedure,
        ERROR_LINE()      AS Line,
        ERROR_MESSAGE()   AS Message;
END CATCH;

Key error functions: ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_PROCEDURE().

THROW (SQL Server 2012+) re-raises errors:

BEGIN CATCH
    ROLLBACK;
    THROW; -- re-throws the original error
END CATCH;

8. What is the difference between ISNULL and COALESCE?

-- ISNULL
SELECT ISNULL(NULL, 'default'); -- 'default'

-- COALESCE
SELECT COALESCE(NULL, NULL, 'third', 'fourth'); -- 'third'

9. Explain the PIVOT and UNPIVOT operators.

PIVOT rotates rows into columns:

SELECT *
FROM (
    SELECT Year, Quarter, Revenue
    FROM SalesData
) AS src
PIVOT (
    SUM(Revenue)
    FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS pvt;

UNPIVOT rotates columns into rows:

SELECT Year, Quarter, Revenue
FROM SalesPivoted
UNPIVOT (
    Revenue FOR Quarter IN (Q1, Q2, Q3, Q4)
) AS unpvt;

10. What is the difference between DELETE, TRUNCATE, and DROP?

Indexes

11. What is the difference between a clustered and a non-clustered index?

-- Create a clustered index explicitly
CREATE CLUSTERED INDEX CIX_Orders_OrderDate
ON Orders(OrderDate);

-- Create a non-clustered index
CREATE NONCLUSTERED INDEX NIX_Orders_CustomerID
ON Orders(CustomerID)
INCLUDE (OrderDate, TotalDue); -- covering

12. What is a covering index?

A covering index includes all columns needed by a query, so SQL Server can satisfy the query entirely from the index without performing a Key Lookup back to the clustered index.

-- Query
SELECT CustomerID, OrderDate, TotalDue
FROM Orders
WHERE CustomerID = 42;

-- Covering non-clustered index
CREATE NONCLUSTERED INDEX NIX_Orders_Customer_Covering
ON Orders(CustomerID)
INCLUDE (OrderDate, TotalDue);

Without the INCLUDE columns, SQL Server would use the index to find matching rows but then perform an expensive Key Lookup for each row to get OrderDate and TotalDue.

13. What is a Columnstore index and when should you use it?

A Columnstore index stores data column-by-column rather than row-by-row, achieving massive compression and enabling batch mode execution for analytical queries.

-- Clustered columnstore (replaces heap/row store — for DW tables)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON FactSales;

-- Non-clustered columnstore (analytical queries on OLTP table)
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders_Analytics
ON Orders(CustomerID, OrderDate, TotalDue, Quantity);

14. What is index fragmentation and how do you fix it?

Fragmentation occurs when index pages are out of logical order due to INSERT/UPDATE/DELETE operations, causing extra I/O.

-- Check fragmentation
SELECT
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id
    AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 5
ORDER BY ips.avg_fragmentation_in_percent DESC;

Remediation strategy:

-- Reorganise (online, low impact)
ALTER INDEX NIX_Orders_CustomerID ON Orders REORGANIZE;

-- Rebuild (more thorough)
ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = ON);

15. What are statistics and why do they matter?

Statistics are histograms that describe the distribution of data in columns. The Query Optimiser uses statistics to estimate the number of rows that will be returned by predicates, influencing plan choice.

-- View statistics
DBCC SHOW_STATISTICS ('Sales.Orders', 'NIX_Orders_CustomerID');

-- Manually update statistics
UPDATE STATISTICS Sales.Orders WITH FULLSCAN;

-- Auto-update threshold (default: 20 % + 500 rows changed)
-- Enable Trace Flag 2371 / SQL 2016+ dynamic threshold for large tables

Outdated statistics → poor row estimates → bad plans → slow queries.

Transactions and Locking

16. What are the SQL Server transaction isolation levels?

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
-- ... your queries ...
COMMIT;

17. What is RCSI (Read Committed Snapshot Isolation) and why enable it?

RCSI (enabled at database level) changes READ COMMITTED to use row versioning from tempdb instead of shared locks.

• Readers never block writers, writers never block readers.
• Readers see the last committed version of each row.
• Eliminates most blocking without changing application code.

-- Enable RCSI
ALTER DATABASE AdventureWorks SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

-- Check RCSI status
SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'AdventureWorks';

Trade-off: increased tempdb activity and version store size.

18. What causes deadlocks and how do you resolve them?

A deadlock occurs when two (or more) sessions each hold a lock that the other needs.

Session 1: locks Table A, waits for Table B
Session 2: locks Table B, waits for Table A
→ Deadlock! SQL Server kills one session (the "deadlock victim")

Common causes:

Capture deadlocks:

-- Extended Events — system_health session captures deadlocks automatically
SELECT CAST(target_data AS XML)
FROM sys.dm_xe_session_targets t
JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
WHERE s.name = 'system_health'
  AND t.target_name = 'ring_buffer';

19. What is the WITH (NOLOCK) hint and what are its risks?

WITH (NOLOCK) is equivalent to READ UNCOMMITTED — it reads data without acquiring shared locks.

SELECT COUNT(*) FROM Orders WITH (NOLOCK);

Risks:

• Dirty reads — reads uncommitted data that may be rolled back
• Non-repeatable reads — data changes between reads in the same transaction
• Ghost reads / phantom data — reads rows that are being moved during a page split
• Incorrect counts — can count the same row twice or miss rows during page splits

Recommendation: Use RCSI instead. It provides non-blocking reads without the data correctness risks.

20. Explain optimistic vs pessimistic locking in SQL Server.

-- Optimistic locking with rowversion
ALTER TABLE Products ADD RowVer rowversion NOT NULL;

-- At read time: store RowVer
-- At update time: check RowVer hasn't changed
UPDATE Products
SET Price = @NewPrice
WHERE ProductID = @ID AND RowVer = @StoredRowVer;

IF @@ROWCOUNT = 0
    THROW 50001, 'Concurrency conflict — row was modified.', 1;

Performance Tuning

21. How do you read and interpret an execution plan?

An execution plan shows how SQL Server physically executes a query.

-- Text plan
SET SHOWPLAN_TEXT ON;
SELECT * FROM Orders WHERE CustomerID = 1;
SET SHOWPLAN_TEXT OFF;

-- Actual plan (captures runtime stats)
SET STATISTICS PROFILE ON;
SELECT * FROM Orders WHERE CustomerID = 1;
SET STATISTICS PROFILE OFF;

Key operators to recognise:

Warning signs: thick arrows (large row estimates), yellow exclamation marks, missing index hints.

22. How do you identify slow queries?

-- Top 10 queries by total CPU time
SELECT TOP 10
    qs.total_worker_time / qs.execution_count AS avg_cpu_us,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_us,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
              ((CASE qs.statement_end_offset
                    WHEN -1 THEN DATALENGTH(qt.text)
                    ELSE qs.statement_end_offset END
               - qs.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_cpu_us DESC;

-- Enable slow query log equivalent: Query Store (SQL Server 2016+)
ALTER DATABASE AdventureWorks SET QUERY_STORE = ON;
ALTER DATABASE AdventureWorks SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 500
);

23. What is Parameter Sniffing and how do you handle it?

Parameter sniffing occurs when SQL Server compiles a stored procedure plan based on the first parameter values passed, then reuses that plan for all subsequent calls — even when different parameters would benefit from a different plan.

-- Problematic proc
CREATE PROCEDURE GetOrders @CustomerID INT AS
    SELECT * FROM Orders WHERE CustomerID = @CustomerID;
-- If first call was @CustomerID = 1 (1 order), plan uses Index Seek
-- Later call with @CustomerID = 9999 (10k orders) → wrong plan

-- Solutions:
-- 1. OPTION (RECOMPILE) — recompile every execution
SELECT * FROM Orders WHERE CustomerID = @CustomerID
OPTION (RECOMPILE);

-- 2. Local variable workaround (breaks sniffing)
CREATE PROCEDURE GetOrders @CustomerID INT AS
    DECLARE @LocalCust INT = @CustomerID;
    SELECT * FROM Orders WHERE CustomerID = @LocalCust;

-- 3. OPTIMIZE FOR UNKNOWN
SELECT * FROM Orders WHERE CustomerID = @CustomerID
OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN));

-- 4. Multiple plans (Query Store → Force Plan)

24. What is the role of tempdb and why can it become a bottleneck?

tempdb is a shared, system database reset on every SQL Server restart. It stores:

Bottleneck causes: allocation page contention (PFS, GAM, SGAM).

Best practices:

-- Pre-create tempdb data files (1 per core, up to 8)
-- Set equal size and autogrowth for all files
-- Place on fast SSD

-- SQL Server 2016+: Trace Flag 1118 and 1117 are enabled by default
-- Earlier: add TF 1118, 1117 to startup parameters

25. What query hints are commonly used, and when?

SELECT o.OrderID, c.Name
FROM Orders o
INNER HASH JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate > '2025-01-01'
OPTION (MAXDOP 4, RECOMPILE);

Hints bypass the optimiser — use as a last resort after fixing statistics and indexes.

Stored Procedures, Functions, and Triggers

26. What is the difference between a stored procedure and a function?

-- Scalar UDF (avoid in hot queries — row-by-row execution until SQL 2019 UDF inlining)
CREATE FUNCTION dbo.GetFullName (@First NVARCHAR(50), @Last NVARCHAR(50))
RETURNS NVARCHAR(100) AS
BEGIN
    RETURN @First + ' ' + @Last;
END;

-- Inline table-valued function (ITVF) — best performance
CREATE FUNCTION dbo.GetCustomerOrders (@CustomerID INT)
RETURNS TABLE AS
RETURN (
    SELECT OrderID, OrderDate, TotalDue
    FROM Orders
    WHERE CustomerID = @CustomerID
);

27. What are the types of triggers in SQL Server?

-- AFTER UPDATE audit trigger
CREATE TRIGGER trg_Orders_Audit
ON Orders
AFTER UPDATE AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO OrdersAudit (OrderID, OldStatus, NewStatus, ChangedAt, ChangedBy)
    SELECT
        d.OrderID,
        d.Status AS OldStatus,
        i.Status AS NewStatus,
        GETUTCDATE(),
        SYSTEM_USER
    FROM deleted d
    JOIN inserted i ON d.OrderID = i.OrderID
    WHERE d.Status <> i.Status;
END;

Note: inserted and deleted pseudo-tables are always available inside DML triggers.

28. What is the difference between a view and a materialized view (indexed view)?

-- Create an indexed view
CREATE VIEW dbo.SalesByProduct WITH SCHEMABINDING AS
    SELECT ProductID, SUM(Quantity) AS TotalQty, COUNT_BIG(*) AS OrderCount
    FROM dbo.OrderItems
    GROUP BY ProductID;
GO

CREATE UNIQUE CLUSTERED INDEX UIX_SalesByProduct
ON dbo.SalesByProduct(ProductID);

High Availability and Disaster Recovery

29. What is Always On Availability Groups (AG)?

Always On Availability Groups (SQL Server 2012+) provide high availability and disaster recovery by replicating a group of databases to one or more secondary replicas.

Primary Replica        Secondary Replicas
[AG Primary]    →  Synchronous  →  [AG Secondary 1] (HA, auto-failover)
                →  Asynchronous →  [AG Secondary 2] (DR, manual failover)

30. Compare Always On AG, Log Shipping, and Database Mirroring.

Security

31. What is the difference between a Login and a User in SQL Server?

-- Create a SQL Server login
CREATE LOGIN AppLogin WITH PASSWORD = 'Str0ng!Pass';

-- Create a database user mapped to the login
USE AdventureWorks;
CREATE USER AppUser FOR LOGIN AppLogin;

-- Grant permissions
GRANT SELECT, INSERT ON dbo.Orders TO AppUser;
DENY DELETE ON dbo.Orders TO AppUser;

32. What is Row-Level Security (RLS)?

RLS restricts which rows a user can see/modify based on a security predicate (a filter function).

-- 1. Create the filter function
CREATE FUNCTION dbo.fn_SecurityPredicate (@SalesRegion NVARCHAR(50))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN (
    SELECT 1 AS fn_Result
    WHERE @SalesRegion = CAST(SESSION_CONTEXT(N'SalesRegion') AS NVARCHAR(50))
       OR IS_MEMBER('db_owner') = 1
);

-- 2. Create the security policy
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE dbo.fn_SecurityPredicate(SalesRegion)
ON dbo.Orders
WITH (STATE = ON);

-- 3. Set context for each session
EXEC sp_set_session_context N'SalesRegion', N'EMEA';
-- Now queries on Orders only see EMEA rows

33. What is Transparent Data Encryption (TDE)?

TDE encrypts the physical data files (.mdf, .ndf, .ldf) and backups at rest using a Database Encryption Key (DEK) protected by the server certificate.

-- 1. Create master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongP@ssw0rd!';

-- 2. Create certificate
CREATE CERTIFICATE TDE_Cert WITH SUBJECT = 'TDE Certificate';

-- 3. Create database encryption key
USE AdventureWorks;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;

-- 4. Enable TDE
ALTER DATABASE AdventureWorks SET ENCRYPTION ON;

-- Check status
SELECT name, encryption_state, encryption_state_desc
FROM sys.dm_database_encryption_keys;

Critical: Back up the TDE certificate and private key immediately. If lost, the database is unrecoverable.

Administration

34. What are the SQL Server backup types?

Typical backup strategy:

Weekly:  Full backup
Daily:   Differential backup
Hourly:  Transaction Log backup (for point-in-time recovery)

-- Full backup
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\Backups\AdventureWorks_Full.bak'
WITH COMPRESSION, STATS = 10, CHECKSUM;

-- Transaction log backup
BACKUP LOG AdventureWorks
TO DISK = 'D:\Backups\AdventureWorks_Log.trn'
WITH COMPRESSION, STATS = 10;

-- Restore with NORECOVERY (more log backups to apply)
RESTORE DATABASE AdventureWorks
FROM DISK = 'D:\Backups\AdventureWorks_Full.bak'
WITH NORECOVERY, REPLACE, STATS = 10;

-- Final restore — bring database online
RESTORE DATABASE AdventureWorks WITH RECOVERY;

35. What are the SQL Server recovery models?

-- Check current model
SELECT name, recovery_model_desc FROM sys.databases;

-- Change recovery model
ALTER DATABASE AdventureWorks SET RECOVERY FULL;

36. What is SQL Server Agent and what is it used for?

SQL Server Agent is a Windows service that runs scheduled jobs, monitors alerts, and handles operator notifications.

-- Create a simple Agent job via T-SQL
USE msdb;
EXEC sp_add_job @job_name = N'Nightly_Index_Rebuild';

EXEC sp_add_jobstep
    @job_name = N'Nightly_Index_Rebuild',
    @step_name = N'Rebuild Indexes',
    @command = N'ALTER INDEX ALL ON dbo.Orders REBUILD;',
    @database_name = N'AdventureWorks';

EXEC sp_add_schedule
    @schedule_name = N'Nightly_2AM',
    @freq_type = 4,          -- daily
    @active_start_time = 020000; -- 02:00:00

EXEC sp_attach_schedule
    @job_name = N'Nightly_Index_Rebuild',
    @schedule_name = N'Nightly_2AM';

EXEC sp_add_jobserver @job_name = N'Nightly_Index_Rebuild';

Advanced Topics

37. What are Temporal Tables?

Temporal tables (SQL Server 2016+) automatically track the full history of row changes, enabling time-travel queries.

-- Create temporal table
CREATE TABLE Products (
    ProductID   INT           NOT NULL PRIMARY KEY,
    Name        NVARCHAR(100) NOT NULL,
    Price       DECIMAL(10,2) NOT NULL,
    ValidFrom   DATETIME2     GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo     DATETIME2     GENERATED ALWAYS AS ROW END   NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductsHistory));

-- Query as of a point in time
SELECT * FROM Products
FOR SYSTEM_TIME AS OF '2025-01-01T00:00:00';

-- Query a range
SELECT * FROM Products
FOR SYSTEM_TIME BETWEEN '2025-01-01' AND '2025-06-01';

38. What is In-Memory OLTP (Hekaton)?

In-Memory OLTP (SQL Server 2014+) provides lock-free, latch-free data structures stored entirely in RAM, dramatically improving OLTP throughput.

-- Create memory-optimized filegroup
ALTER DATABASE AdventureWorks
ADD FILEGROUP MemoryOptimized CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE AdventureWorks
ADD FILE (NAME = 'MemOptFile', FILENAME = 'D:\Data\MemOpt')
TO FILEGROUP MemoryOptimized;

-- Create in-memory table
CREATE TABLE dbo.HotQueue (
    QueueID   INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
    Payload   NVARCHAR(1000) NOT NULL,
    CreatedAt DATETIME2 NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

-- Natively compiled stored procedure
CREATE PROCEDURE dbo.EnqueueItem
    @Payload NVARCHAR(1000)
WITH NATIVE_COMPILATION, SCHEMABINDING AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
    INSERT INTO dbo.HotQueue (QueueID, Payload, CreatedAt)
    VALUES (NEXT VALUE FOR dbo.QueueSeq, @Payload, SYSUTCDATETIME());
END;

39. How does SQL Server handle JSON?

SQL Server 2016+ includes JSON support for parsing and generating JSON.

-- Parse JSON
SELECT *
FROM OPENJSON('{"orders": [{"id":1,"amt":100},{"id":2,"amt":200}]}', '$.orders')
WITH (
    OrderID INT  '$.id',
    Amount  DECIMAL '$.amt'
);

-- Query JSON column
SELECT
    CustomerID,
    JSON_VALUE(Preferences, '$.Theme')    AS Theme,
    JSON_QUERY(Preferences, '$.Settings') AS SettingsJson
FROM Customers
WHERE JSON_VALUE(Preferences, '$.Newsletter') = 'true';

-- Build JSON output
SELECT OrderID, TotalDue, Status
FROM Orders
WHERE CustomerID = 42
FOR JSON PATH, ROOT('orders');

40. What are computed columns and when should you index them?

A computed column is a virtual column whose value is derived from an expression.

-- Computed column
ALTER TABLE Orders
ADD TotalWithTax AS (TotalDue * 1.21);           -- virtual (not stored)

ALTER TABLE Orders
ADD TotalWithTax AS (TotalDue * 1.21) PERSISTED;  -- stored on disk

-- Index on a computed column (must be PERSISTED and deterministic)
CREATE INDEX NIX_Orders_TotalWithTax ON Orders(TotalWithTax);

Practical Scenarios

41. How do you find duplicate rows and remove them?

-- Find duplicates
SELECT Email, COUNT(*) AS Cnt
FROM Customers
GROUP BY Email
HAVING COUNT(*) > 1;

-- Delete duplicates, keeping the lowest CustomerID
WITH Dupes AS (
    SELECT CustomerID,
           ROW_NUMBER() OVER (PARTITION BY Email ORDER BY CustomerID) AS rn
    FROM Customers
)
DELETE FROM Dupes WHERE rn > 1;

42. How do you implement pagination efficiently?

Avoid OFFSET/FETCH for large offsets — it scans all skipped rows.

-- OFFSET/FETCH (simple but slow at large offsets)
SELECT OrderID, OrderDate, TotalDue
FROM Orders
ORDER BY OrderID
OFFSET 10000 ROWS FETCH NEXT 20 ROWS ONLY;

-- Keyset (cursor) pagination — much faster
-- First page
SELECT TOP 20 OrderID, OrderDate, TotalDue
FROM Orders
ORDER BY OrderID;

-- Next page: pass last OrderID from previous result
SELECT TOP 20 OrderID, OrderDate, TotalDue
FROM Orders
WHERE OrderID > @LastOrderID
ORDER BY OrderID;

43. How do you identify blocking sessions?

-- Current blocking chains
SELECT
    blocking.session_id AS blocking_spid,
    blocked.session_id  AS blocked_spid,
    blocked.wait_time / 1000.0 AS wait_seconds,
    blocked.wait_type,
    blocked_sql.text    AS blocked_query,
    blocking_sql.text   AS blocking_query
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_sessions blocking ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle)  AS blocked_sql
CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) AS blocking_sql
WHERE blocked.blocking_session_id > 0;

-- Kill a blocking session (use with caution)
KILL 55; -- replace with actual SPID

44. How do you perform a schema change without downtime?

Use the expand-contract (parallel change) pattern:

1. Expand — add new column as nullable; update application to write to both old and new column.
2. Migrate — backfill data in batches (avoid large locks):

-- Batched backfill
DECLARE @BatchSize INT = 1000, @LastID INT = 0;
WHILE 1 = 1 BEGIN
    UPDATE TOP (@BatchSize) Orders
    SET NewStatusColumn = OldStatusColumn
    WHERE NewStatusColumn IS NULL AND OrderID > @LastID;
    IF @@ROWCOUNT = 0 BREAK;
    SET @LastID = @LastID + @BatchSize;
    WAITFOR DELAY '00:00:00.1';
END;

3. Contract — once migration is complete and verified, drop old column.

45. How do you monitor SQL Server health?

-- CPU usage by query
SELECT TOP 10
    total_worker_time / execution_count AS avg_cpu_us,
    execution_count,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 200) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_cpu_us DESC;

-- Memory pressure
SELECT
    physical_memory_in_use_kb / 1024.0 AS memory_used_mb,
    page_fault_count
FROM sys.dm_os_process_memory;

-- Wait statistics (overall bottlenecks)
SELECT TOP 20
    wait_type,
    waiting_tasks_count,
    wait_time_ms / 1000.0 AS total_wait_sec,
    max_wait_time_ms / 1000.0 AS max_wait_sec
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK','BROKER_TO_FLUSH','HADR_WORK_QUEUE',
    'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE'
)
ORDER BY wait_time_ms DESC;

SQL Server vs Other Databases

46. SQL Server vs MySQL vs PostgreSQL

47. What are T-SQL-specific features not in standard SQL?

Common Mistakes

SQL Server vs related tools

FAQ

Q: When should I use a stored procedure vs ad-hoc SQL? Stored procedures offer plan caching, reduced network traffic, permission management (grant EXEC without table access), and easier maintenance. Use ad-hoc SQL for simple one-off queries and ORM-generated queries; use stored procedures for complex, frequently executed business logic.

Q: What is the difference between @@IDENTITY, SCOPE_IDENTITY(), and IDENT_CURRENT()? @@IDENTITY returns the last identity value inserted in the current session (any scope, any table — can be wrong if a trigger inserts to another table). SCOPE_IDENTITY() returns the last identity in the current scope (correct, use this). IDENT_CURRENT('tableName') returns the last identity for a specific table, any session — use for diagnostic purposes only.

Q: How does SQL Server handle NULL in indexes? SQL Server stores NULL values in non-clustered indexes. A regular index can be used for IS NULL queries. A filtered index on WHERE col IS NULL or WHERE col IS NOT NULL is more efficient for selective NULL queries:

CREATE INDEX NIX_Orders_NullStatus
ON Orders(CustomerID)
WHERE Status IS NULL;

Q: What is the difference between UNION and UNION ALL? UNION removes duplicate rows (performs a DISTINCT sort — expensive). UNION ALL returns all rows including duplicates (no sort — much faster). Always use UNION ALL unless you explicitly need duplicate elimination.

Q: How do you prevent SQL injection in SQL Server? Always use parameterised queries or stored procedures with parameters. Never concatenate user input into SQL strings.

-- WRONG: SQL injection risk
EXEC('SELECT * FROM Users WHERE Name = ''' + @UserInput + '''');

-- CORRECT: parameterised
EXEC sp_executesql
    N'SELECT * FROM Users WHERE Name = @Name',
    N'@Name NVARCHAR(100)',
    @Name = @UserInput;

Q: What is the Query Store and when should you enable it? Query Store (SQL Server 2016+) persists query plans, execution statistics, and runtime stats in the user database. Use it to: detect plan regressions, force a known-good plan, compare query performance before/after an index change or upgrade. Enable on all production databases — overhead is minimal (< 1–2 % CPU).

ALTER DATABASE AdventureWorks SET QUERY_STORE = ON;
ALTER DATABASE AdventureWorks SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    MAX_STORAGE_SIZE_MB = 1000,
    QUERY_CAPTURE_MODE = AUTO,
    SIZE_BASED_CLEANUP_MODE = AUTO
);