SQL Server Interview Questions and Answers

Answer

A table with no clustered index is a heap: rows sit in unordered pages and every row is addressed by a RID, an eight-byte physical pointer made of file id, page id, and slot number. Creating a clustered index rewrites the entire table so the data pages become the leaf level of a B-tree ordered by the clustering key. There is no separate copy of the data; the clustered index is the table.

Two consequences catch candidates out. First, every existing nonclustered index is rebuilt too, because their row locator changes from the RID to the clustering key. That is why a nonclustered index on a clustered table is silently wider than you think, the clustering key is appended to every leaf row.

Second, the operation needs roughly 1.2 times the table size in free space plus log space, and offline it holds a schema modification lock for the duration. Pick a clustering key that is narrow, static, unique, and ever-increasing. An INT IDENTITY or a sequential key satisfies all four.

A random uniqueidentifier from NEWID() satisfies none of them and produces constant mid-page splits and fragmentation. Interviewers often follow up with why a wide clustering key such as a composite VARCHAR(200) natural key is expensive: because that key is duplicated into the leaf of every nonclustered index on the table, inflating page counts and IO across the whole workload.

-- Heap: rows located by RID, no logical ordering
CREATE TABLE dbo.Orders (
    OrderId     INT IDENTITY(1,1) NOT NULL,
    CustomerId  INT NOT NULL,
    OrderDate   DATETIME2(0) NOT NULL,
    Amount      DECIMAL(12,2) NOT NULL
);

-- Turning it into a B-tree ordered by OrderId
ALTER TABLE dbo.Orders
    ADD CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderId);

-- Confirm what you actually have
SELECT i.name, i.type_desc, i.is_primary_key,
       ps.page_count, ps.avg_fragmentation_in_percent
FROM sys.indexes AS i
CROSS APPLY sys.dm_db_index_physical_stats(
        DB_ID(), i.object_id, i.index_id, NULL, 'LIMITED') AS ps
WHERE i.object_id = OBJECT_ID('dbo.Orders');

Answer

All three enforce uniqueness through a unique index, so the enforcement mechanism is identical. The differences are in NULL handling, defaults, and intent. A PRIMARY KEY forbids NULLs entirely and defaults to CLUSTERED if no clustered index exists yet.

A UNIQUE constraint allows NULL but treats NULL as a value for uniqueness purposes, so exactly one NULL is permitted per key. This surprises people coming from Oracle, where multiple NULLs are allowed in a unique constraint. A unique nonclustered index behaves the same as a unique constraint but is not exposed in sys.key_constraints, cannot be the target of a foreign key, and can carry INCLUDE columns and a filter predicate.

That last point is the practical workaround for the multiple-NULL problem: a filtered unique index with WHERE column IS NOT NULL enforces uniqueness only across non-NULL rows, which is what most application designs actually want for optional fields such as an alternate email or a GSTIN. Interviewers commonly probe two follow-ups. First, can a foreign key reference a unique constraint rather than a primary key?

Yes, as long as the referenced columns are covered by a unique constraint or unique index and are NOT NULL. Second, what happens to the index when you drop the constraint? It goes with it, which is why dropping a UNIQUE constraint on a large table can quietly wreck query plans that were relying on that index for seeks.

CREATE TABLE dbo.Employee (
    EmployeeId  INT IDENTITY(1,1) CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED,
    PAN         CHAR(10)     NOT NULL CONSTRAINT UQ_Employee_PAN UNIQUE,
    AltEmail    NVARCHAR(254) NULL
);

-- UNIQUE allows exactly ONE null. This fails on the second null row:
-- INSERT dbo.Employee (PAN, AltEmail) VALUES ('AAAAA1111A', NULL), ('BBBBB2222B', NULL);

-- Filtered unique index: uniqueness only across rows that have a value
CREATE UNIQUE NONCLUSTERED INDEX UX_Employee_AltEmail
    ON dbo.Employee (AltEmail)
    WHERE AltEmail IS NOT NULL;

Answer

VARCHAR stores one byte per character under the database collation's code page; NVARCHAR stores UTF-16, two bytes per character for the basic multilingual plane. Since SQL Server 2019 you can also set a UTF-8 collation such as Latin1_General_100_CI_AS_SC_UTF8 on a VARCHAR column, which stores Unicode in one to four bytes and is the sensible choice for mostly-ASCII data that occasionally carries Devanagari or emoji. The breakage nobody expects is the implicit conversion.

Data type precedence puts NVARCHAR above VARCHAR, so if your application parameter is NVARCHAR (which is the default for .NET SqlClient string parameters and for JDBC unless you set sendStringParametersAsUnicode=false) and your column is VARCHAR, the engine converts the column, not the parameter. That makes the predicate non-SARGable and turns a clean index seek into a full index scan. On a 40 million row customer table this is the difference between two milliseconds and twenty seconds, and the plan shows a CONVERT_IMPLICIT in the seek predicate plus a plan-level warning.

The second gotcha is collation mismatch across databases or across a linked server, which raises error 468, cannot resolve the collation conflict, on a join. You fix it with an explicit COLLATE clause on the join predicate, but that is also non-SARGable, so the real fix is aligning collations. Interviewers ask this because it is the single most common silent performance bug in Indian enterprise estates where the database was created in 2011 and the application layer was rewritten in .NET later.

CREATE TABLE dbo.Customer (
    CustomerId INT IDENTITY PRIMARY KEY,
    Email      VARCHAR(254) NOT NULL
);
CREATE UNIQUE INDEX UX_Customer_Email ON dbo.Customer (Email);

-- N prefix forces NVARCHAR: the COLUMN gets converted, index seek is lost
SELECT CustomerId FROM dbo.Customer WHERE Email = N'ravi@example.in';
-- Plan: Index Scan + CONVERT_IMPLICIT(nvarchar, Email)

-- Matching the type keeps the seek
SELECT CustomerId FROM dbo.Customer WHERE Email = 'ravi@example.in';

-- Cross-collation join fix (works, but blocks seeks: align collations instead)
SELECT a.Email
FROM dbo.Customer AS a
JOIN Legacy.dbo.Users AS b
  ON a.Email = b.Email COLLATE Latin1_General_CI_AS;

Answer

DELETE is DML: it removes rows one at a time, logs each row in the transaction log, fires AFTER DELETE and INSTEAD OF DELETE triggers, respects a WHERE clause, can be rolled back, and leaves the IDENTITY seed untouched. On a large table it can generate an enormous amount of log and escalate locks to the whole table. TRUNCATE TABLE is DDL: it deallocates the data pages and logs only the extent deallocations, so it is dramatically faster and produces a fraction of the log.

It does not fire DML triggers, cannot have a WHERE clause, resets the IDENTITY seed to the original value, and requires ALTER permission on the table rather than DELETE permission. The persistent myth is that TRUNCATE cannot be rolled back. It absolutely can: run it inside an explicit transaction and ROLLBACK restores the data, because the deallocation itself is logged.

What TRUNCATE cannot do is run against a table referenced by an enabled foreign key, participate in indexed views, or work on a table published for transactional replication. Since SQL Server 2016 you can truncate specific partitions with TRUNCATE TABLE dbo.T WITH (PARTITIONS (2, 4 TO 6)), which is the clean way to expire old data on a partitioned audit table. DROP TABLE removes the object definition, data, indexes, triggers, permissions, and statistics entirely; the foreign key restriction applies here too.

Interviewers usually finish with the operational version of the question: you need to purge 200 million rows from a live OLTP table, what do you do? The expected answer is batched deletes in chunks of a few thousand rows inside their own transactions, with a delay between batches, or partition switching if the table is partitioned.

-- Yes, TRUNCATE is transactional
BEGIN TRAN;
    TRUNCATE TABLE dbo.StagingOrders;
    SELECT COUNT(*) FROM dbo.StagingOrders;   -- 0
ROLLBACK;
SELECT COUNT(*) FROM dbo.StagingOrders;       -- rows are back

-- Partition-level truncate (2016+)
TRUNCATE TABLE dbo.AuditLog WITH (PARTITIONS (1 TO 3));

-- Safe purge on a live OLTP table: batch it
WHILE 1 = 1
BEGIN
    DELETE TOP (5000) FROM dbo.AuditLog
    WHERE CreatedAt < DATEADD(YEAR, -2, SYSUTCDATETIME());

    IF @@ROWCOUNT = 0 BREAK;
    WAITFOR DELAY '00:00:00.200';   -- let log backups and replicas breathe
END

Answer

T-SQL is processed in a logical order that differs from the order you write it: FROM and JOIN first, then ON, then OUTER JOIN row re-addition, then WHERE, then GROUP BY, then HAVING, then SELECT (including column aliases and window functions), then DISTINCT, then ORDER BY, then TOP or OFFSET FETCH. Everything follows from that sequence. A column alias defined in SELECT does not exist when WHERE is evaluated, so WHERE FullPrice > 100 fails with invalid column name even though FullPrice appears three lines above.

The same rule explains why you cannot filter on a window function in WHERE: ROW_NUMBER() is computed in the SELECT phase, after WHERE has already run. The workaround for both is a CTE or derived table, which materialises the projection logically before the outer WHERE applies. ORDER BY is the one clause that can see SELECT aliases, because it runs after SELECT.

HAVING is not a fancier WHERE either: WHERE filters rows before grouping, HAVING filters groups after aggregation, so pushing a non-aggregate predicate into HAVING forces the engine to aggregate rows it will then discard. Note that this is the logical order, not the physical execution order. The optimizer is free to reorder anything as long as the result set is identical, which is why a predicate written in HAVING sometimes still gets pushed down. Interviewers use this question as a fast filter because a candidate who cannot explain it will also write correlated subqueries and OUTER JOIN filters incorrectly.

-- Fails: FullPrice does not exist yet when WHERE runs
-- SELECT Amount * 1.18 AS FullPrice FROM dbo.Orders WHERE FullPrice > 1000;

-- Works: CTE completes the SELECT phase first
WITH Priced AS (
    SELECT OrderId,
           Amount * 1.18 AS FullPrice,
           ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) AS rn
    FROM dbo.Orders
)
SELECT OrderId, FullPrice
FROM Priced
WHERE FullPrice > 1000
  AND rn = 1
ORDER BY FullPrice DESC;

-- WHERE before grouping, HAVING after aggregation
SELECT CustomerId, SUM(Amount) AS Total
FROM dbo.Orders
WHERE OrderDate >= '2026-04-01'      -- row filter, cheap
GROUP BY CustomerId
HAVING SUM(Amount) > 500000;         -- group filter, needs the aggregate

Answer

Because of where the predicate sits relative to outer row re-addition. In a LEFT JOIN the ON clause decides which right-hand rows match; any left row with no match is then re-added with NULLs in every right-hand column. WHERE runs after that step.

So a predicate on a right-hand column placed in ON filters the match candidates and preserves unmatched left rows, while the same predicate in WHERE evaluates against those NULL-filled rows, and since NULL = anything is UNKNOWN, the left rows get discarded. The net effect is that your LEFT JOIN silently degrades into an INNER JOIN. This is one of the highest-frequency bugs in reporting SQL, and it costs money in real life: a monthly sales report that is supposed to show every branch including the ones with zero transactions quietly drops those branches, so nobody notices the underperforming locations.

The rule to state in an interview is precise: predicates on the outer (preserved) table belong in WHERE, predicates on the inner (nullable) table belong in ON. The one deliberate exception is the anti-join idiom, LEFT JOIN with WHERE right.key IS NULL, which finds left rows with no match at all. Interviewers may follow up by asking about NOT IN versus NOT EXISTS: NOT IN returns an empty result set if the subquery produces a single NULL, because the comparison evaluates to UNKNOWN for every row, whereas NOT EXISTS handles NULLs correctly and usually optimizes into the same anti-semi join operator.

-- Keeps every branch, counts only 2026 orders (correct)
SELECT b.BranchName, COUNT(o.OrderId) AS Orders2026
FROM dbo.Branch AS b
LEFT JOIN dbo.Orders AS o
       ON o.BranchId = b.BranchId
      AND o.OrderDate >= '2026-01-01'
GROUP BY b.BranchName;

-- Silently becomes an INNER JOIN: zero-order branches disappear
SELECT b.BranchName, COUNT(o.OrderId) AS Orders2026
FROM dbo.Branch AS b
LEFT JOIN dbo.Orders AS o ON o.BranchId = b.BranchId
WHERE o.OrderDate >= '2026-01-01'
GROUP BY b.BranchName;

-- Deliberate anti-join: branches with no orders at all
SELECT b.BranchName
FROM dbo.Branch AS b
LEFT JOIN dbo.Orders AS o ON o.BranchId = b.BranchId
WHERE o.OrderId IS NULL;

Answer

On a box-product SQL Server instance the default is READ COMMITTED using locking, not versioning. A read acquires a shared (S) lock on each row or page as it reads it and releases that lock as soon as the read moves past, so it does not hold shared locks for the length of the transaction. Writes take exclusive (X) locks that are held until commit or rollback, plus intent locks (IX, IS) at the page and table level so the engine can detect conflicts without scanning every row lock.

This means a reader blocks behind an uncommitted writer, which is the classic source of application timeouts. Two important qualifications. First, Azure SQL Database defaults to READ COMMITTED SNAPSHOT (RCSI) instead, so a query that never blocks in Azure may block on-premises with identical code, and that difference alone causes migration incidents.

Second, READ COMMITTED with locking does not guarantee statement-level consistency for scans: a long-running SELECT can miss rows or read them twice if another transaction moves rows in the index while the scan is in flight. The standard interview trap is READ UNCOMMITTED, often written as the WITH (NOLOCK) hint. Candidates describe it as a performance trick; the honest answer is that it permits dirty reads, missing rows, duplicate rows, and even error 601 when a scan hits a page that moved. If you need readers not to block, turn on RCSI at the database level rather than sprinkling NOLOCK through the codebase.

-- Where you actually are
SELECT name,
       is_read_committed_snapshot_on,
       snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();

-- Session-level change
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- The right fix for reader/writer blocking (needs a brief exclusive lock)
ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

-- Inspect live locks for a session
SELECT resource_type, resource_description, request_mode, request_status
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;

Answer

A stored procedure is a compiled batch that can do anything: DML, DDL, transactions, dynamic SQL, multiple result sets, output parameters, and a return code. It cannot be used inside a SELECT. A scalar UDF returns one value and historically ran once per row with its own execution context, which made it the most reliable way to destroy a query's performance; it also silently forced the whole plan to serial execution.

SQL Server 2019 introduced Froid, scalar UDF inlining, which rewrites qualifying scalar functions into relational expressions so they can be optimized as part of the outer query. It only applies when the function meets a long list of conditions (no time-dependent functions, no side effects, no EXECUTE AS, and so on), and you can check sys.sql_modules.is_inlineable to see whether yours qualifies. An inline table-valued function is the one to reach for: it is a single RETURN SELECT with no BEGIN or END, and the optimizer expands it into the calling query like a parameterised view, so it can use statistics and choose good join orders.

A multi-statement TVF declares a @table return variable and fills it procedurally; it is a black box to the optimizer, and although SQL Server 2017 added interleaved execution to get a real row count for these in some plans, they still usually perform worse than the inline form. The interview answer to memorise: use inline TVFs for reusable query logic, stored procedures for anything with side effects, and treat scalar and multi-statement functions as things to refactor away.

-- Inline TVF: optimizer expands it, statistics apply
CREATE OR ALTER FUNCTION dbo.fn_OrdersForCustomer (@CustomerId INT)
RETURNS TABLE
AS
RETURN
    SELECT OrderId, OrderDate, Amount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
GO

SELECT c.Name, o.OrderId, o.Amount
FROM dbo.Customer AS c
CROSS APPLY dbo.fn_OrdersForCustomer(c.CustomerId) AS o;
GO

-- Is your scalar UDF eligible for 2019+ inlining?
SELECT OBJECT_NAME(object_id) AS fn, is_inlineable
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsScalarFunction') = 1;

Answer

A CTE is not a temporary object at all. It is a named subquery that exists for the duration of one statement, and the optimizer typically inlines it into the plan, which means referencing the same CTE three times can execute it three times. It is a readability tool and the only way to write recursion in T-SQL, not a materialisation tool.

A local temp table (#t) is a real table in tempdb with real pages, real statistics, and the ability to carry indexes and constraints. Because it has statistics, the optimizer gets accurate cardinality estimates, which is exactly what you want when you are staging an intermediate result of unknown size. The cost is recompiles: changing row counts in a temp table triggers statement recompilation inside a procedure.

A table variable (@t) also lives in tempdb but historically had a fixed cardinality estimate of one row, which produced nested loop plans against millions of rows. SQL Server 2019 added table variable deferred compilation, which compiles the first statement referencing the variable using the real row count, but there are still no column statistics, so estimates past the first predicate remain poor. Table variables do have two genuine advantages: they are not affected by transaction rollback, which makes them useful for logging inside a transaction you may roll back, and they are required as table-valued parameters. The rule of thumb interviewers want: fewer than about a hundred rows and simple access, use a table variable; larger intermediate sets, joins, or anything you need to index, use a temp table; single-statement readability or recursion, use a CTE.

-- Temp table: statistics, indexes, survives across statements in the batch
CREATE TABLE #Recent (
    OrderId INT PRIMARY KEY,
    CustomerId INT NOT NULL,
    Amount DECIMAL(12,2) NOT NULL
);
CREATE INDEX IX_Recent_Customer ON #Recent (CustomerId) INCLUDE (Amount);

INSERT #Recent (OrderId, CustomerId, Amount)
SELECT OrderId, CustomerId, Amount
FROM dbo.Orders
WHERE OrderDate >= DATEADD(DAY, -30, SYSUTCDATETIME());

-- Table variable: no column stats, but immune to rollback
DECLARE @Audit TABLE (Step NVARCHAR(50), LoggedAt DATETIME2(3));

-- Recursive CTE: the one job only a CTE can do
WITH OrgChart AS (
    SELECT EmployeeId, ManagerId, 0 AS Depth
    FROM dbo.Employee WHERE ManagerId IS NULL
    UNION ALL
    SELECT e.EmployeeId, e.ManagerId, oc.Depth + 1
    FROM dbo.Employee AS e
    JOIN OrgChart AS oc ON e.ManagerId = oc.EmployeeId
)
SELECT * FROM OrgChart OPTION (MAXRECURSION 100);

Answer

IDENTITY is a column property: it generates values on insert, is scoped to one table, and cannot be updated or easily reset without DBCC CHECKIDENT. SEQUENCE, added in SQL Server 2012, is a standalone schema object you can share across tables, call before the insert (NEXT VALUE FOR), cycle, cache with a chosen size, and restart. Sequences are the right choice when you need the key value in application code before writing the row, when multiple tables must draw from one number series such as a shared document number across invoices and credit notes, or when you need gapless-ish control over caching.

Neither guarantees no gaps. Both cache values in memory for performance, and a rollback consumes numbers permanently. The famous jump is the identity cache: SQL Server pre-allocates a block of identity values (1000 for INT and BIGINT, 10000 for SMALLINT) and if the instance restarts unexpectedly or a failover occurs, the unused portion of that block is lost, so the next value is 1001 rather than 4.

Since SQL Server 2017 you can turn this off per database with ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF, or globally with trace flag 272, at some insert-throughput cost. For sequences the equivalent knob is NO CACHE or a smaller CACHE size. The important thing to say in an interview is that surrogate keys must never be assumed contiguous. If the business needs a gapless invoice series for GST filings, that is a separate table with its own transaction, not an identity column.

-- Shared number series across two tables
CREATE SEQUENCE dbo.DocNumber
    AS BIGINT START WITH 1000001 INCREMENT BY 1 CACHE 50;

DECLARE @doc BIGINT = NEXT VALUE FOR dbo.DocNumber;  -- known before insert
INSERT dbo.Invoice (DocNo, CustomerId) VALUES (@doc, 42);

-- Identity: retrieve safely (SCOPE_IDENTITY, never @@IDENTITY)
INSERT dbo.Orders (CustomerId, Amount) VALUES (42, 1999.00);
SELECT SCOPE_IDENTITY() AS NewOrderId;

-- Stop the 1000-value jump after failover (2017+)
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF;

-- Inspect and correct the seed
DBCC CHECKIDENT ('dbo.Orders', NORESEED);

Answer

TRY...CATCH catches errors with severity 11 to 19 that occur in the same execution scope. Inside CATCH you have ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_LINE(), ERROR_PROCEDURE() and ERROR_STATE(). What it does not catch is important interview material: compile errors and object-name resolution errors in the same batch, severity 20 and above (which terminate the connection), and attentions such as client-side query timeouts.

THROW, added in 2012, is the modern re-raise: bare THROW inside a CATCH rethrows the original error with the original number and line, while THROW 50001, 'message', 1 raises a custom error and always uses severity 16. RAISERROR is the legacy form and is still needed for two things: formatted messages with placeholders, and RAISERROR (..., 10, 1) WITH NOWAIT to emit progress messages immediately from a long-running procedure instead of buffering them. The transaction piece is where candidates lose marks.

Some errors leave the transaction doomed, meaning XACT_STATE() returns -1 and the only legal action is ROLLBACK, not COMMIT. SET XACT_ABORT ON makes almost any runtime error abort the whole transaction automatically, which is exactly what you want in procedures that do multi-statement writes, and it is mandatory for distributed transactions. A robust template checks XACT_STATE() in the CATCH block, rolls back if the transaction is doomed or still open, logs, and rethrows so the calling application actually sees a failure instead of a silent partial write.

CREATE OR ALTER PROCEDURE dbo.usp_TransferFunds
    @FromId INT, @ToId INT, @Amount DECIMAL(12,2)
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRAN;
            UPDATE dbo.Account SET Balance = Balance - @Amount
            WHERE AccountId = @FromId AND Balance >= @Amount;

            IF @@ROWCOUNT = 0
                THROW 50001, 'Insufficient balance or account missing', 1;

            UPDATE dbo.Account SET Balance = Balance + @Amount
            WHERE AccountId = @ToId;
        COMMIT;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK;

        INSERT dbo.ErrorLog (ErrNo, ErrMsg, ErrProc, ErrLine, LoggedAt)
        VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(),
                ERROR_LINE(), SYSUTCDATETIME());

        THROW;   -- preserve original error number and line
    END CATCH
END

Answer

All four are ranking window functions that require an OVER clause with ORDER BY. ROW_NUMBER assigns a strictly sequential number with no ties, so two rows with identical values still get different numbers, and which one gets 1 is non-deterministic unless the ORDER BY is a total ordering. RANK assigns the same number to ties and then skips: 1, 1, 3.

DENSE_RANK assigns the same number to ties without skipping: 1, 1, 2. NTILE(n) splits the partition into n roughly equal buckets, distributing remainder rows to the earlier buckets. The bug that shows up in real code is deduplication.

If you write a dedupe query with RANK() OVER (PARTITION BY Email ORDER BY CreatedAt) and then delete everything with rank greater than 1, you keep every row that tied on CreatedAt, so duplicates survive and the pipeline that runs after it fails a unique index build. ROW_NUMBER is the correct function there because it guarantees exactly one row per partition. Conversely, if a sales dashboard asks for the top three salary bands, DENSE_RANK is correct and ROW_NUMBER is wrong because it would arbitrarily cut off employees sharing the third-highest salary.

Interviewers also probe the frame clause on aggregate windows: SUM(x) OVER (ORDER BY d) defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which lumps together peer rows with equal ORDER BY values and uses an on-disk spool, whereas ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is row-precise and measurably faster. Always write ROWS explicitly for running totals.

SELECT Name, Dept, Salary,
       ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Salary DESC) AS rn,
       RANK()       OVER (PARTITION BY Dept ORDER BY Salary DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY Dept ORDER BY Salary DESC) AS drnk,
       NTILE(4)     OVER (PARTITION BY Dept ORDER BY Salary DESC) AS quartile
FROM dbo.Employee;

-- Correct dedupe: ROW_NUMBER guarantees one survivor per key
WITH Dupes AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY Email
                              ORDER BY CreatedAt DESC, CustomerId DESC) AS rn
    FROM dbo.Customer
)
DELETE FROM Dupes WHERE rn > 1;

-- Running total: state ROWS explicitly, RANGE is slower and ties-aware
SELECT OrderDate, Amount,
       SUM(Amount) OVER (ORDER BY OrderDate
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunTotal
FROM dbo.Orders;

Answer

ISNULL is T-SQL specific, takes exactly two arguments, and returns the data type of the first argument. COALESCE is ANSI standard, takes any number of arguments, returns the highest-precedence data type among them, and is internally expanded into a CASE expression. That expansion has two consequences interviewers like: COALESCE evaluates its first non-null argument's expression potentially more than once, so COALESCE((SELECT MAX(x) FROM big), 0) can run the subquery twice, and COALESCE can return NULL when all inputs are NULL whereas ISNULL of a non-nullable second argument produces a NOT NULL result.

The type-truncation trap is the more common production bug: ISNULL(CAST(NULL AS VARCHAR(3)), 'ABCDEF') returns 'ABC' because the result takes the first argument's type, while COALESCE returns 'ABCDEF'. On comparisons, NULL is unknown, not a value, so NULL = NULL is UNKNOWN and only IS NULL or IS DISTINCT FROM (added in SQL Server 2022) can test for it. Aggregates silently ignore NULLs, which is why AVG over a column with NULLs is not the same as SUM divided by COUNT(*), and it produces the ANSI warning 8153 about nulls eliminated by aggregate.

GROUP BY, DISTINCT, UNION and unique constraints all treat NULLs as equal to each other, so NULL semantics are deliberately inconsistent between comparison contexts and grouping contexts. Do not rely on SET ANSI_NULLS OFF to make NULL = NULL work; it is deprecated, ignored by many code paths, and disallowed for indexed views and filtered indexes.

-- Type of the result differs: silent truncation with ISNULL
SELECT ISNULL(CAST(NULL AS VARCHAR(3)), 'ABCDEF') AS WithIsNull,   -- 'ABC'
       COALESCE(CAST(NULL AS VARCHAR(3)), 'ABCDEF') AS WithCoalesce; -- 'ABCDEF'

-- NULL-safe equality: SQL Server 2022 added IS DISTINCT FROM
SELECT * FROM dbo.Staging AS s
JOIN dbo.Target AS t ON t.Id = s.Id
WHERE t.MiddleName IS DISTINCT FROM s.MiddleName;   -- treats NULL as comparable

-- Pre-2022 idiom for the same thing
WHERE EXISTS (SELECT t.MiddleName EXCEPT SELECT s.MiddleName);

-- Aggregates skip NULLs
SELECT COUNT(*) AS Rows_, COUNT(Rating) AS Rated, AVG(Rating) AS AvgOfRated
FROM dbo.Feedback;

Answer

DATETIME has a 3.33 millisecond resolution, stores values rounded to increments of .000, .003 and .007, spans only 1753 to 9999, and always occupies 8 bytes. DATETIME2(n) has 100 nanosecond precision, a range starting at year 0001, and takes 6 to 8 bytes depending on the declared scale, so DATETIME2(0) is actually smaller than DATETIME. The rounding is the trap: inserting '2026-08-11 23:59:59.999' into a DATETIME column stores '2026-08-12 00:00:00.000', which quietly shifts a transaction into the next day and breaks day-boundary reports and end-of-month cutoffs.

That is why the correct date-range predicate is always a half-open interval, WHERE d >= '2026-08-01' AND d < '2026-09-01', never BETWEEN with a 23:59:59.997 upper bound. DATETIMEOFFSET adds a timezone offset and is what you want for anything user-facing across regions; store UTC with SYSUTCDATETIME() and convert at the edge with AT TIME ZONE 'India Standard Time'. Migration surprises: DATETIME2 does not support arithmetic like GETDATE() + 1, you must use DATEADD; implicit conversion between DATETIME and DATETIME2 in a predicate can produce a non-SARGable comparison in older compatibility levels; and older ORMs and drivers occasionally map DATETIME2 as a string. The other rule to state is to always use unambiguous literal formats: 'yyyy-MM-dd' and 'yyyy-MM-ddTHH:mm:ss' are safe under every language and DATEFORMAT setting, while 'dd/MM/yyyy' is interpreted differently depending on the login's language, a real hazard on Indian systems where users assume day-first.

-- DATETIME rounds to 3.33ms increments and can jump the date boundary
DECLARE @old DATETIME  = '2026-08-11 23:59:59.999';
DECLARE @new DATETIME2(3) = '2026-08-11 23:59:59.999';
SELECT @old AS RoundedUp, @new AS Exact;   -- 2026-08-12 00:00:00.000 | 2026-08-11 23:59:59.999

-- Always use half-open ranges, never BETWEEN on datetimes
SELECT COUNT(*) FROM dbo.Orders
WHERE OrderDate >= '2026-08-01' AND OrderDate < '2026-09-01';

-- Store UTC, render IST at the edge
SELECT OrderId,
       OrderDateUtc AT TIME ZONE 'UTC'
                    AT TIME ZONE 'India Standard Time' AS OrderDateIst
FROM dbo.Orders;

Answer

A normal view is just a stored SELECT; the engine expands it into the calling query at compile time and stores no data. An indexed view (a materialised view in other database dialects) is a view with a unique clustered index on it, which means the result set is physically stored and maintained synchronously as the base tables change. The prerequisites are strict and interviewers expect them named: the view must be created WITH SCHEMABINDING, all objects must be referenced by two-part names, the first index must be UNIQUE CLUSTERED, SET options must match a specific set at both create and modify time (ANSI_NULLS, QUOTED_IDENTIFIER, ARITHABORT and friends ON, NUMERIC_ROUNDABORT OFF), and the SELECT cannot use OUTER JOIN, subqueries, UNION, DISTINCT, TOP, ROW_NUMBER, MIN, MAX or COUNT(*) in an aggregate view.

If you aggregate you must include COUNT_BIG(*) so the engine can maintain the rows incrementally. The cost side matters more than the definition: every INSERT, UPDATE and DELETE on a base table now also maintains the view's index inside the same transaction, so write throughput drops and the view becomes a new blocking hotspot. Enterprise edition can use an indexed view automatically even when the query does not name it (automatic view matching); Standard edition requires the query to reference the view directly with the NOEXPAND hint, and in practice NOEXPAND is worth adding on every edition because it also gives the optimizer the view's own statistics. The pragmatic answer: indexed views are excellent for expensive, frequently-read aggregates over slowly-changing tables, and a bad idea on hot OLTP tables.

CREATE OR ALTER VIEW dbo.vw_DailySales
WITH SCHEMABINDING
AS
SELECT o.BranchId,
       CAST(o.OrderDate AS DATE) AS SalesDate,
       SUM(o.Amount) AS TotalAmount,
       COUNT_BIG(*)  AS OrderCount      -- mandatory for aggregate indexed views
FROM dbo.Orders AS o                    -- two-part name required
GROUP BY o.BranchId, CAST(o.OrderDate AS DATE);
GO

CREATE UNIQUE CLUSTERED INDEX CX_vw_DailySales
    ON dbo.vw_DailySales (BranchId, SalesDate);
GO

-- On Standard edition you must name the view and hint NOEXPAND
SELECT BranchId, SalesDate, TotalAmount
FROM dbo.vw_DailySales WITH (NOEXPAND)
WHERE SalesDate >= '2026-08-01';

Answer

No. SQL Server has exactly one real transaction per session; nested BEGIN TRAN statements only increment @@TRANCOUNT. The inner COMMIT does not commit anything, it just decrements the counter, and durability happens only when the counter reaches zero on the outermost COMMIT. ROLLBACK is the asymmetric part and the reason this question is asked: an unqualified ROLLBACK TRANSACTION rolls back everything and sets @@TRANCOUNT to zero regardless of nesting depth.

So a helper procedure that opens its own transaction and rolls back on error silently destroys the caller's work, and then the caller's COMMIT fails with error 3902, the COMMIT REQUEST has no corresponding BEGIN TRANSACTION. The safe pattern in reusable procedures is to detect whether you are already inside a transaction, and if so create a save point with SAVE TRANSACTION rather than a new BEGIN, then ROLLBACK TRANSACTION SavePointName on error, which unwinds only your own work. Two caveats to mention: save points do not work in distributed transactions, and they cannot rescue a doomed transaction where XACT_STATE() is -1, so you still need the XACT_STATE check.

It is also worth noting that SQL Server transactions do not release locks at a save point rollback, only at the final commit or full rollback, so long transactions with save points still hold locks and grow the version store. In practice, most teams standardise on a simpler rule: only the outermost caller opens a transaction, inner procedures never do, and every procedure runs with SET XACT_ABORT ON.

CREATE OR ALTER PROCEDURE dbo.usp_AddOrderLine @OrderId INT, @Sku NVARCHAR(30)
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;

    DECLARE @outer BIT = CASE WHEN @@TRANCOUNT > 0 THEN 1 ELSE 0 END;
    IF @outer = 1 SAVE TRANSACTION AddOrderLine;
    ELSE BEGIN TRAN;

    BEGIN TRY
        INSERT dbo.OrderLine (OrderId, Sku) VALUES (@OrderId, @Sku);
        IF @outer = 0 COMMIT;
    END TRY
    BEGIN CATCH
        IF @outer = 0 AND XACT_STATE() <> 0 ROLLBACK;
        ELSE IF XACT_STATE() = 1 ROLLBACK TRANSACTION AddOrderLine;  -- only my work
        THROW;
    END CATCH
END

Answer

SQL Server Management Studio (SSMS) is still the primary client for administration: object explorer, Activity Monitor, execution plans, maintenance plans, Always On dashboards, and the Import and Export wizard. For cross-platform work most teams now use the mssql extension in Visual Studio Code, which gives IntelliSense, query history and plan viewing on macOS and Linux, and Azure Data Studio remains in many estates. sqlcmd is the scripting workhorse for CI pipelines and works on Linux and inside containers; bcp handles high-speed bulk import and export. SQL Server Profiler is deprecated in favour of Extended Events, which you can drive from SSMS or with CREATE EVENT SESSION scripts.

For deployment, the two accepted approaches are state-based and migration-based. State-based means a SQL Server Data Tools (SSDT) database project compiled to a DACPAC, then deployed with SqlPackage.exe /Action:Publish, which diffs the target and generates the change script; you gate it with a DeployReport step in the pipeline so nobody ships an accidental table drop. Migration-based means ordered, hand-written, idempotent scripts run by Flyway, Liquibase, DbUp, or EF Core migrations, which is what most .NET teams in Indian product companies use because it makes data movement explicit. The answer interviewers want to hear includes the safety rails: schema changes reviewed in version control, never applied by hand in production SSMS, always with a rollback script, and long-running index or column changes scheduled with ONLINE = ON or done through a shadow table when the edition does not support online operations.

# Cross-platform CLI, works in CI containers
sqlcmd -S tcp:sqlprod01,1433 -d SalesDb -G -Q "SELECT @@VERSION;"

# What WILL change, before you change it
SqlPackage /Action:DeployReport \
  /SourceFile:"SalesDb.dacpac" \
  /TargetConnectionString:"Server=sqlprod01;Database=SalesDb;Authentication=Active Directory Default;" \
  /OutputPath:"deployreport.xml" \
  /p:BlockOnPossibleDataLoss=True

# Then publish
SqlPackage /Action:Publish /SourceFile:"SalesDb.dacpac" \
  /TargetConnectionString:"..." /p:BlockOnPossibleDataLoss=True

# Bulk export/import without SSIS
bcp SalesDb.dbo.Orders out orders.dat -S sqlprod01 -T -n

Answer

The ANSI syntax is ORDER BY ... OFFSET n ROWS FETCH NEXT m ROWS ONLY, available since SQL Server 2012 and preferable to the old ROW_NUMBER window filter for readability. Two rules make it correct.

First, OFFSET/FETCH requires an ORDER BY, and that ORDER BY must be deterministic; if you page by a non-unique column such as OrderDate, rows can appear on two pages or on none as the engine's tie-breaking varies between executions. Always append a unique tiebreaker like the primary key. Second, TOP without ORDER BY guarantees nothing at all, it is not a shortcut for the first n rows in insertion order.

The performance problem is inherent to offset pagination: to return rows 100001 to 100020 the engine must produce and discard the first 100000 rows, so page latency grows linearly with page number and a deep-paging crawler can pin a CPU. For an admin grid where users only touch the first few pages this is fine. For an API, an infinite scroll, or an export job, switch to keyset pagination (also called seek pagination): remember the sort key of the last row returned and use a WHERE predicate to seek straight to it, which stays constant-time regardless of depth as long as an index covers the sort order. The trade-off to mention is that keyset pagination cannot jump to an arbitrary page number and needs careful handling of composite keys, which is why most real systems use OFFSET/FETCH for the UI grid and keyset for machine consumers.

-- Offset pagination: simple, but page 5000 scans and discards 100k rows
SELECT OrderId, OrderDate, Amount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
ORDER BY OrderDate DESC, OrderId DESC     -- unique tiebreaker is mandatory
OFFSET @Page * @Size ROWS FETCH NEXT @Size ROWS ONLY;

-- Keyset pagination: constant cost at any depth
SELECT TOP (@Size) OrderId, OrderDate, Amount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
  AND (OrderDate < @LastDate
       OR (OrderDate = @LastDate AND OrderId < @LastOrderId))
ORDER BY OrderDate DESC, OrderId DESC;

-- The index that makes either one a seek
CREATE NONCLUSTERED INDEX IX_Orders_Cust_Date
    ON dbo.Orders (CustomerId, OrderDate DESC, OrderId DESC)
    INCLUDE (Amount);

Answer

Get the actual plan, not the estimated one, either with Include Actual Execution Plan in SSMS, SET STATISTICS XML ON, or from Query Store for something that already ran. Then work right to left, top to bottom, because that is data flow order. The first thing to check is not cost, it is the gap between estimated rows and actual rows on each operator.

A large discrepancy is the root cause of most bad plans: it drives the wrong join type, an undersized memory grant, and spills. The second thing is thick arrows, which represent high row counts, feeding into expensive operators. Third, look for the specific patterns that indicate fixable problems: a Key Lookup with a high execute count means your nonclustered index is missing INCLUDE columns; a Sort or Hash Match with a warning triangle means a spill to tempdb; a Table Spool or Eager Spool often signals Halloween protection or an inefficient correlated subquery; an implicit conversion warning on the root SELECT node means a data type mismatch is killing a seek.

Ignore the cost percentages in SSMS. They are calculated from the estimates, not the actual work, so a plan whose estimate is wrong by three orders of magnitude will confidently show the expensive operator at 0%. Since SQL Server 2016 you also get actual elapsed time and actual IO per operator in the properties pane, which is far more trustworthy. Finally, check the plan properties on the root node for the compiled parameter values, the degree of parallelism, the memory grant requested versus used, and whether the plan was trimmed for a timeout, all of which change how you interpret everything downstream.

SET STATISTICS XML ON;
SET STATISTICS IO, TIME ON;
EXEC dbo.usp_GetCustomerOrders @CustomerId = 4711;
SET STATISTICS XML OFF;

-- Plans already in cache, ranked by average duration
SELECT TOP (20)
       DB_NAME(pa.dbid) AS db,
       OBJECT_NAME(pa.objectid, pa.dbid) AS obj,
       qs.execution_count,
       qs.total_worker_time / qs.execution_count AS avg_cpu_us,
       qs.total_elapsed_time / qs.execution_count AS avg_elapsed_us,
       qs.total_logical_reads / qs.execution_count AS avg_reads,
       qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY (SELECT CAST(value AS INT) AS dbid, NULL AS objectid) AS x
CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
WHERE pa.attribute = 'dbid'
ORDER BY avg_elapsed_us DESC;

Answer

Key columns form the sorted B-tree and exist at every level of the index; INCLUDE columns exist only in the leaf level and are not sorted. That single fact drives the whole decision. A column belongs in the key if it is used for seeking (equality or range predicates), for ordering (ORDER BY, GROUP BY, window PARTITION BY), or for merge joins.

A column belongs in INCLUDE if the query only needs to output it, so putting it in the leaf avoids a Key Lookup while keeping the intermediate levels narrow and the index small. Key order matters and follows a rule candidates often get backwards: equality predicates first, then the range predicate, then any ordering columns. An index on (Status, CreatedAt) can seek WHERE Status = 'OPEN' AND CreatedAt > x, but an index on (CreatedAt, Status) cannot seek on Status because the range on the leading column already scattered the values.

There are hard limits worth naming: 32 key columns and 1700 bytes of key for a nonclustered index (900 for clustered), while INCLUDE columns do not count against the key size and can be large types. The other half of the answer is knowing when not to add an index. Every nonclustered index is maintained on every write, consumes buffer pool memory, and lengthens index maintenance windows.

Before adding one, check sys.dm_db_index_usage_stats for existing indexes that are almost the same, and consider widening an existing index instead of creating a fifth near-duplicate. Treat the missing index DMV suggestions as raw material, not instructions; they ignore existing indexes entirely and routinely propose 12-column monsters.

-- Equality columns first, then the range column, then output columns
CREATE NONCLUSTERED INDEX IX_Orders_Branch_Status_Date
    ON dbo.Orders (BranchId, Status, OrderDate)   -- seek and order
    INCLUDE (CustomerId, Amount, GstAmount);      -- output only, no lookup

-- Are your existing indexes earning their keep?
SELECT OBJECT_NAME(i.object_id) AS tbl, i.name,
       us.user_seeks, us.user_scans, us.user_lookups, us.user_updates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS us
       ON us.object_id = i.object_id
      AND us.index_id  = i.index_id
      AND us.database_id = DB_ID()
WHERE i.type_desc = 'NONCLUSTERED'
  AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY us.user_updates DESC;   -- high updates, zero seeks = drop candidate

-- Filtered index for a skewed status column
CREATE NONCLUSTERED INDEX IX_Orders_Pending
    ON dbo.Orders (OrderDate) INCLUDE (CustomerId, Amount)
    WHERE Status = 'PENDING';

Answer

When a stored procedure or parameterised query compiles, the optimizer sniffs the parameter values from that first execution and builds a plan optimised for them, then caches it. If the data is skewed the cached plan can be terrible for later values. The textbook case: a procedure filtered by BranchId, first called for a small branch with 40 rows, gets a nested loop with a key lookup; the next call for Mumbai with four million rows reuses that plan and runs for eleven minutes instead of two seconds.

The symptom is a query that is intermittently slow with no code change, and the giveaway in the plan is a large gap between estimated and actual rows plus compiled parameter values in the root node that differ from the runtime values. The real fixes, in the order you should try them: fix the underlying cardinality problem first, because a missing index or stale statistics often makes both plans acceptable. Then enable Query Store and force a known-good plan, which is the least invasive production fix and reversible in seconds.

Then OPTION (RECOMPILE) on the specific statement if compile cost is small relative to execution time; this gets an optimal plan every run and also enables parameter embedding. OPTIMIZE FOR UNKNOWN or the database-scoped PARAMETER_SNIFFING = OFF setting gives you an average-density plan, which is mediocre for everyone but predictable, useful when consistency matters more than peak speed. Splitting the procedure into branches with separate sub-procedures per data-volume class works when the skew is known and stable. Finally, SQL Server 2022 added Parameter Sensitive Plan optimization, which caches multiple plan variants for a single statement based on predicate cardinality buckets, and it addresses the classic single-equality-predicate case automatically when the database is on compatibility level 160.

-- 1. See what the plan was compiled for
SELECT TOP (10) qs.plan_handle, qs.execution_count,
       qs.min_elapsed_time, qs.max_elapsed_time, qt.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.text LIKE '%usp_GetOrdersByBranch%'
ORDER BY qs.max_elapsed_time DESC;

-- 2. Statement-level recompile (cheapest correct fix for small compile cost)
SELECT OrderId, Amount FROM dbo.Orders
WHERE BranchId = @BranchId
OPTION (RECOMPILE);

-- 3. Average-density plan instead of a sniffed one
OPTION (OPTIMIZE FOR (@BranchId UNKNOWN));

-- 4. Blunt instrument: disable sniffing for the whole database
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;

-- 5. 2022 Parameter Sensitive Plan optimization needs modern compat level
ALTER DATABASE SalesDb SET COMPATIBILITY_LEVEL = 160;

Answer

This is the most common real-world SQL Server support ticket, and there are four credible explanations you should be able to rank. The usual culprit is SET option difference producing a second cached plan. SSMS connects with ARITHABORT ON, while older .NET SqlClient and ODBC connections historically connect with ARITHABORT OFF.

SET options are part of the plan cache key, so the two clients get separate cache entries, and the application's entry may hold a bad sniffed plan while yours is freshly compiled and good. That is why running the query in SSMS appears to fix it and why people wrongly conclude the application is at fault. The second explanation is genuinely different parameters: the app passes an NVARCHAR parameter where you typed a literal, so the application's version has an implicit conversion and scans.

The third is blocking, the app runs inside a longer transaction that already holds locks or waits behind another session, which SSMS is not doing. The fourth is data volume: the app requests 200000 rows and the client-side ADO.NET buffering plus network round trips dominate, so the server-side query is fast and the end-to-end call is not. The diagnostic path is to reproduce with the application's SET options (SET ARITHABORT OFF before your test), compare plan_handle values in sys.dm_exec_query_stats for the two versions, and check sys.dm_exec_requests for blocking_session_id and wait_type while the slow call runs. Confirm whether the app's command timeout produced an attention event in the Extended Events system_health session, because a client timeout leaves the transaction open until the connection is reset.

-- Reproduce the application's plan cache entry
SET ARITHABORT OFF;
SET ANSI_WARNINGS ON;
EXEC dbo.usp_GetOrdersByBranch @BranchId = 12;

-- Two cache entries for the same text = SET option mismatch
SELECT cp.plan_handle, cp.usecounts, cp.cacheobjtype,
       pa.attribute, pa.value
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.text LIKE '%usp_GetOrdersByBranch%'
  AND pa.attribute IN ('set_options', 'user_id');

-- What is the session actually waiting on right now?
SELECT r.session_id, r.status, r.wait_type, r.wait_time,
       r.blocking_session_id, r.cpu_time, r.logical_reads,
       SUBSTRING(t.text, (r.statement_start_offset/2)+1, 200) AS stmt
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID;

Answer

SARGable means Search ARGument able: the predicate can be evaluated by seeking into an index rather than by computing a value for every row. A predicate stops being SARGable the moment you wrap the indexed column in a function, do arithmetic on it, or force a data type conversion on it. YEAR(OrderDate) = 2026, ISNULL(Status, 'X') = 'X', Amount * 1.18 > 1000, CAST(CustomerId AS VARCHAR) = '4711' and LEFT(Sku, 3) = 'IND' all produce scans.

Leading wildcards are the other classic: LIKE '%phone' cannot seek because a B-tree is ordered from the left, whereas LIKE 'phone%' can. The fixes are mechanical. Move the function to the constant side of the comparison, so YEAR(OrderDate) = 2026 becomes OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'.

Replace ISNULL in a predicate with an OR IS NULL clause or a filtered index. Fix the type mismatch at the parameter rather than casting the column. For the leading-wildcard case, either use full-text search with CONTAINS, or add a persisted computed column holding the reversed string and index that.

Two subtleties senior interviewers push on: CONVERT is sometimes still SARGable when the conversion is order-preserving and the optimizer can prove it, notably CONVERT(DATE, datetime2column) = @d has been treated as a range seek since SQL Server 2008, and OR predicates across different columns often prevent a single seek, so rewriting as UNION ALL of two seekable branches can be dramatically faster than a single OR. Always confirm the rewrite with an actual plan; a rewrite that looks SARGable but still scans usually has an implicit conversion hiding in the seek predicate.

-- NOT SARGable: function on the column forces an index scan
SELECT OrderId FROM dbo.Orders WHERE YEAR(OrderDate) = 2026;
SELECT OrderId FROM dbo.Orders WHERE DATEDIFF(DAY, OrderDate, GETDATE()) <= 7;
SELECT OrderId FROM dbo.Orders WHERE CAST(CustomerId AS VARCHAR(10)) = '4711';

-- SARGable rewrites
SELECT OrderId FROM dbo.Orders
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01';

SELECT OrderId FROM dbo.Orders
WHERE OrderDate >= DATEADD(DAY, -7, CAST(SYSUTCDATETIME() AS DATE));

SELECT OrderId FROM dbo.Orders WHERE CustomerId = 4711;

-- OR across columns: two seeks beat one scan
SELECT OrderId FROM dbo.Orders WHERE CustomerId = @c
UNION ALL
SELECT OrderId FROM dbo.Orders WHERE ReferenceNo = @r AND CustomerId <> @c;

-- Persisted computed column keeps a derived predicate seekable
ALTER TABLE dbo.Orders ADD OrderYear AS YEAR(OrderDate) PERSISTED;
CREATE INDEX IX_Orders_Year ON dbo.Orders (OrderYear);

Answer

A statistics object holds a histogram of up to 200 steps on the leading column plus density vectors for column combinations, and the optimizer uses it to guess how many rows each operator will produce. Every downstream decision, join type, join order, memory grant, parallelism, depends on that guess. AUTO_CREATE_STATISTICS builds single-column stats on the fly for predicates that lack them, and AUTO_UPDATE_STATISTICS invalidates a stats object once enough rows have changed.

The old threshold was 20 percent of the table plus 500 rows, which meant a 100 million row table needed 20 million modifications before a refresh, so estimates went stale for months. The modern behaviour, from SQL Server 2016 with compatibility level 130 or higher, uses the square-root formula, roughly SQRT(1000 * rowcount), which triggers far more often on big tables. Auto-update is still not enough in three situations.

First, ascending key columns such as a datetime or identity: rows inserted today fall beyond the last histogram step, so the optimizer estimates one row for a query filtering on today's data, then picks a nested loop that runs a million times. Trace flag 2371 and the ascending key detection help but do not remove the problem; a targeted stats update after a load is the reliable answer. Second, filtered indexes and filtered statistics do not get updated by the same row-modification accounting you expect. Third, statistics sampled at the default rate on a very large table can produce a histogram that misses skew entirely; PERSIST_SAMPLE_PERCENT, added in 2016 SP1, lets you pin a higher sample rate so later automatic updates do not silently revert to the default sample.

-- What the optimizer is actually looking at
DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_OrderDate') WITH HISTOGRAM;

SELECT s.name, sp.last_updated, sp.rows, sp.rows_sampled,
       sp.modification_counter, sp.persisted_sample_percent
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID('dbo.Orders')
ORDER BY sp.modification_counter DESC;

-- Targeted refresh after a nightly load (ascending-key columns)
UPDATE STATISTICS dbo.Orders IX_Orders_OrderDate
    WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

-- Async updates stop queries paying the refresh cost inline
ALTER DATABASE SalesDb SET AUTO_UPDATE_STATISTICS_ASYNC ON;

-- Multi-column stats for correlated predicates the optimizer treats as independent
CREATE STATISTICS ST_Orders_Branch_Status ON dbo.Orders (BranchId, Status);

Answer

Fragmentation has two forms. Logical fragmentation is out-of-order pages caused by page splits, which hurt read-ahead on sequential scans. Low page density (internal fragmentation) means pages are half empty, so the same data occupies more pages, wastes buffer pool memory, and increases IO for every access.

ALTER INDEX REORGANIZE is an always-online, single-threaded operation that defragments the leaf level in place and compacts LOB pages; it is fully logged per page move, can be stopped and restarted without losing work, and does not update statistics. ALTER INDEX REBUILD recreates the index, rebuilds all levels, applies the FILLFACTOR, and updates statistics with a full scan as a side effect; it is offline by default (schema modification lock for the duration) unless you use WITH (ONLINE = ON), which is Enterprise, Azure SQL, and since SQL Server 2019 also available for clustered columnstore and with the RESUMABLE option. The honest 2026 answer to the second half of the question is that logical fragmentation matters far less on NVMe and cloud block storage, where random reads are cheap, and the old 5 percent reorganize / 30 percent rebuild rule from an old whitepaper is cargo cult.

What still matters is page density, because a 55 percent full index wastes 45 percent of the memory it occupies, and stale statistics, which people accidentally maintained by rebuilding. The modern practice is to run a smart maintenance solution (Ola Hallengren's IndexOptimize is the de facto standard) with page-count and density thresholds, prioritise statistics updates over rebuilds, and set a sensible FILLFACTOR only on indexes with random inserts. Rebuilding everything nightly on an Always On availability group just floods the log and the redo queue on secondaries.

-- Look at density, not just fragmentation percent
SELECT OBJECT_NAME(ps.object_id) AS tbl, i.name AS ix,
       ps.page_count, ps.avg_fragmentation_in_percent,
       ps.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ps
JOIN sys.indexes AS i
  ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE ps.page_count > 1000
ORDER BY ps.avg_page_space_used_in_percent ASC;

-- Online, resumable rebuild: pause it when the window closes
ALTER INDEX IX_Orders_Branch_Status_Date ON dbo.Orders
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 30 MINUTES,
              FILLFACTOR = 90, SORT_IN_TEMPDB = ON);

ALTER INDEX IX_Orders_Branch_Status_Date ON dbo.Orders PAUSE;
ALTER INDEX IX_Orders_Branch_Status_Date ON dbo.Orders RESUME;

-- Always-online, restartable, but does not refresh statistics
ALTER INDEX ALL ON dbo.Orders REORGANIZE;
UPDATE STATISTICS dbo.Orders WITH RESAMPLE;

Answer

A deadlock is a cycle: session A holds a lock B wants while B holds a lock A wants, so the lock monitor picks a victim (lowest estimated rollback cost, unless you raise DEADLOCK_PRIORITY) and kills it with error 1205. Start by getting the deadlock graph, and you almost never need to enable anything, because the system_health Extended Events session captures xml_deadlock_report by default on every instance and keeps a rolling window in the ring buffer. Query it, open the graph in SSMS, and read four things: the two statements involved, the resources (which index, which page or key), the isolation level of each session, and the lock modes.

Most production deadlocks reduce to one of three patterns. First, opposite access order: two procedures update Orders then Payments while another updates Payments then Orders, fixed by standardising object access order across the codebase. Second, a lookup deadlock, where one session seeks a nonclustered index then looks up the clustered index while another updates the clustered index first, fixed by making the reading index covering so the lookup disappears.

Third, the read-then-update conversion deadlock, where two sessions take a shared lock on the same row and both then try to convert to exclusive, fixed by taking the update lock up front with WITH (UPDLOCK, HOLDLOCK) in the initial read, which is also the correct pattern for an upsert. Beyond the specific fix, reduce the surface: shorten transactions, do not put user interaction or an external HTTP call inside a transaction, index the foreign key columns so cascading checks do not scan, and consider RCSI so readers do not participate in write conflicts at all. Applications should also retry error 1205 with a small randomised backoff, because deadlocks can never be eliminated entirely.

-- Pull recent deadlock graphs from the always-on system_health session
SELECT CAST(event_data AS XML) AS deadlock_graph
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report';

-- Upsert without the read-then-update conversion deadlock
BEGIN TRAN;
    SELECT @current = Balance
    FROM dbo.Account WITH (UPDLOCK, HOLDLOCK)   -- take the U lock immediately
    WHERE AccountId = @id;

    IF @current IS NULL
        INSERT dbo.Account (AccountId, Balance) VALUES (@id, @amount);
    ELSE
        UPDATE dbo.Account SET Balance = @current + @amount WHERE AccountId = @id;
COMMIT;

-- Make this session the preferred victim in a known-losing batch job
SET DEADLOCK_PRIORITY LOW;

Answer

Blocking is not a deadlock. Nothing is killed, sessions simply wait, so the application looks hung and eventually hits its command timeout. The diagnostic chain starts with sys.dm_exec_requests, where blocking_session_id points at whoever is holding the lock; follow it recursively until you find a session whose blocking_session_id is zero, that is the head blocker.

Look at its status: if it is running, it is doing real work and you may just need to wait or tune it. If it is sleeping with an open transaction (open_transaction_count above zero in sys.dm_tran_active_transactions or sys.sysprocesses), you have the classic application bug where someone opened a transaction and never committed, usually because an exception path skipped the COMMIT or a client timeout abandoned the connection back to the pool with the transaction still open. That second case is what SET XACT_ABORT ON and connection pooling reset should prevent.

In practice most teams run Adam Machanic's sp_WhoIsActive, which assembles the blocking chain, the running statement text, the wait type, the transaction log usage and the open transaction count in one result set. For post-mortem work, the blocked process report Extended Event with the blocked process threshold configured (say 15 seconds) writes a report you can read the next morning. Fixes range from immediate to structural: KILL the head blocker if it is an abandoned session and you accept the rollback, then fix the actual cause. Shorten transactions, move reads out of write transactions, enable RCSI so readers stop blocking behind writers, index the predicates so that an UPDATE locks 5 rows rather than escalating to the table, and never hold a transaction open across a call to an external service.

-- The live blocking chain
SELECT r.session_id, r.blocking_session_id, r.status, r.wait_type,
       r.wait_time / 1000.0 AS wait_s, r.command,
       DB_NAME(r.database_id) AS db,
       SUBSTRING(t.text, (r.statement_start_offset/2)+1, 300) AS stmt
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
   OR r.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests);

-- Sleeping sessions sitting on an open transaction
SELECT s.session_id, s.login_name, s.host_name, s.program_name,
       s.status, s.last_request_end_time, t.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id
OUTER APPLY (SELECT COUNT(*) AS open_transaction_count
             FROM sys.dm_tran_session_transactions st
             WHERE st.session_id = s.session_id) AS t
WHERE s.is_user_process = 1 AND s.status = 'sleeping' AND t.open_transaction_count > 0;

-- Capture blocking for the morning after
EXEC sp_configure 'blocked process threshold (s)', 15; RECONFIGURE;

Answer

Both use row versioning in tempdb, but they version at different granularities. RCSI is a database-level setting that changes what READ COMMITTED means: every statement sees a consistent snapshot as of the moment that statement started, and readers never take shared locks, so they never block behind writers. It requires no application change, which is why it is the standard fix for reader/writer blocking.

SNAPSHOT isolation is opt-in per transaction with SET TRANSACTION ISOLATION LEVEL SNAPSHOT, and gives a consistent view as of the moment the transaction started, across all statements in it. That extra guarantee comes with update conflict detection: if your snapshot transaction updates a row that another transaction modified and committed after your snapshot began, you get error 3960, snapshot isolation transaction aborted due to update conflict, and your application must retry. RCSI never raises 3960 because each statement re-reads a fresh snapshot; the trade-off is that a multi-statement transaction under RCSI can see different data in statement two than in statement one.

The shared cost is the version store in tempdb: every UPDATE and DELETE writes the previous row version there plus a 14-byte pointer added to the row, which causes a one-time growth in the data pages. Size tempdb accordingly and monitor sys.dm_tran_version_store_space_usage, because a single long-running reporting query can pin the version store and grow tempdb until the drive fills. On SQL Server 2019 and later, the version store for Accelerated Database Recovery lives inside the user database rather than tempdb, which changes the sizing conversation. Enabling RCSI needs a moment of exclusive database access, so schedule it and use WITH ROLLBACK IMMEDIATE only when you know what you are terminating.

-- Database-wide: readers stop blocking, no code changes
ALTER DATABASE SalesDb SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

-- Opt-in per transaction, with conflict detection
ALTER DATABASE SalesDb SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
    SELECT * FROM dbo.Orders WHERE BranchId = 12;   -- consistent for whole tran
    UPDATE dbo.Orders SET Status = 'CLOSED' WHERE OrderId = 991;
    -- may raise 3960 if another tran committed a change to OrderId 991
COMMIT;

-- Who is holding the version store open?
SELECT DB_NAME(database_id) AS db, reserved_page_count, reserved_space_kb
FROM sys.dm_tran_version_store_space_usage;

SELECT transaction_id, elapsed_time_seconds, session_id
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;

Answer

Each lock structure costs roughly 100 bytes of memory, so holding a million row locks is expensive. SQL Server therefore escalates: when a single statement acquires about 5000 locks on one object, or when lock memory exceeds a threshold of overall server memory, it attempts to convert the fine-grained row and page locks into a single table lock (or partition lock if partition-level escalation is configured). It retries every additional 1250 locks if the first attempt fails because another session holds an incompatible lock.

The practical effect is that a DELETE or UPDATE touching 6000 rows suddenly locks the whole table and every other session queues behind it, which reads to the application as a total outage on that table for the duration. This is the mechanism behind most of the blocking incidents caused by nightly batch jobs. There are three levers.

First and best, batch your DML so each statement stays under the threshold, which is the real answer interviewers want, because it also keeps log growth and rollback risk bounded. Second, ALTER TABLE ... SET (LOCK_ESCALATION = AUTO) escalates to the partition rather than the table on a partitioned table, and DISABLE turns escalation off entirely for that table, which you should only do with a memory plan, because uncontrolled lock counts can push the instance into lock memory pressure.

Third, trace flags 1211 and 1224 disable escalation instance-wide, which is almost never the right answer in an interview. Note that escalation is per statement, not per transaction, so ten batched statements inside one transaction each get their own 5000-lock budget, but the locks accumulate and are all held to commit, so batching without committing between batches only solves half the problem.

-- Watch escalation happen
SELECT resource_type, request_mode, COUNT(*) AS lock_count
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
GROUP BY resource_type, request_mode;
-- OBJECT / X after ~5000 row locks = escalated to a full table lock

-- Partition-level escalation instead of whole-table
ALTER TABLE dbo.AuditLog SET (LOCK_ESCALATION = AUTO);

-- Off for a specific hot table (know your lock memory budget first)
ALTER TABLE dbo.Cart SET (LOCK_ESCALATION = DISABLE);

-- The real fix: commit per batch so locks are released
WHILE 1 = 1
BEGIN
    BEGIN TRAN;
        UPDATE TOP (2000) dbo.Orders
        SET Status = 'ARCHIVED'
        WHERE Status = 'CLOSED' AND OrderDate < '2024-01-01';
        IF @@ROWCOUNT = 0 BEGIN COMMIT; BREAK; END
    COMMIT;
END

Answer

Query Store is a per-database flight recorder for the query optimizer. It persists query text, every plan the optimizer produced for that query, runtime statistics aggregated into intervals (duration, CPU, logical reads, memory grant, degree of parallelism, row counts), and wait statistics categorised per query since SQL Server 2017. Because it lives in the user database, it survives restarts and failovers, unlike the plan cache.

It is on by default for new databases from SQL Server 2022 onward, and Microsoft has kept extending it: query hints applied without touching application code via sys.sp_query_store_set_hints, and the ability to capture data on readable secondaries. The regression workflow after a patch or a statistics change is the reason it exists. Open Regressed Queries in SSMS or query sys.query_store_runtime_stats directly, sort by total duration for the period before and after, and find queries where a new plan_id appeared and the metrics got worse.

Confirm the two plans side by side in the plan comparison view, then force the good one with sp_query_store_force_plan. Forcing is immediate, survives restart, and is reversible with sp_query_store_unforce_plan. Two operational cautions to raise in an interview.

Forced plans can fail to apply, for instance when the index they used has been dropped, and you find that in the last_force_failure_reason_desc column, so monitor it rather than assuming a forced plan is still in effect. And Query Store forcing is a tourniquet, not a cure: fix the index, the statistics, or the parameter sensitivity behind the regression and then unforce. Set QUERY_CAPTURE_MODE to AUTO on busy OLTP systems so trivial ad hoc queries do not fill the store, and give MAX_STORAGE_SIZE_MB enough headroom that it does not flip to READ_ONLY silently.

ALTER DATABASE SalesDb SET QUERY_STORE = ON
    (OPERATION_MODE = READ_WRITE,
     QUERY_CAPTURE_MODE = AUTO,
     MAX_STORAGE_SIZE_MB = 4096,
     DATA_FLUSH_INTERVAL_SECONDS = 900,
     INTERVAL_LENGTH_MINUTES = 15);

-- Find queries with more than one plan and a big spread in duration
SELECT q.query_id, qt.query_sql_text, p.plan_id,
       rs.count_executions,
       rs.avg_duration / 1000.0 AS avg_ms,
       rs.last_execution_time, p.is_forced_plan
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
WHERE q.query_id IN (SELECT query_id FROM sys.query_store_plan
                     GROUP BY query_id HAVING COUNT(*) > 1)
ORDER BY rs.avg_duration DESC;

EXEC sys.sp_query_store_force_plan @query_id = 4211, @plan_id = 9067;

-- Verify it is still actually being applied
SELECT plan_id, is_forced_plan, force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan WHERE is_forced_plan = 1;

Answer

There are three recovery models. SIMPLE truncates the log at each checkpoint, so you cannot take log backups and cannot restore to a point in time; your only recovery points are full and differential backups. FULL logs everything and keeps log records until a log backup writes them out, which is what enables point-in-time restore.

BULK_LOGGED behaves like FULL but minimally logs certain bulk operations, so it makes an index rebuild or a BULK INSERT cheap while blocking point-in-time restore to any moment inside that bulk operation. For a 15-minute RPO the design is straightforward: FULL recovery model, a weekly full backup, daily differentials, and log backups every 15 minutes, all with CHECKSUM and verified by regular test restores. Restore order is full, then the most recent differential, then every log backup in sequence with NORECOVERY, then the final one with STOPAT and RECOVERY.

Say out loud that a backup you have never restored is not a backup; the restore rehearsal is the part interviewers listen for. The 400 GB log question is the standard operational trap. In FULL recovery a log file grows without bound if nobody takes log backups, and that is exactly what happens when someone switches a database to FULL to enable an availability group and never adds the log backup job.

Check log_reuse_wait_desc to find the actual reason, because it might not be LOG_BACKUP: an open transaction (ACTIVE_TRANSACTION), an unread replication or CDC log reader (REPLICATION), an AG secondary that is behind (AVAILABILITY_REPLICA), or a long-running snapshot transaction all pin the log. Fix the cause first. Only then shrink once with DBCC SHRINKFILE, and immediately re-grow the log to a sane fixed size with a large growth increment so you do not create thousands of virtual log files.

-- Why can the log not be reused?
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases WHERE name = 'SalesDb';

SELECT * FROM sys.dm_db_log_space_usage;

-- 15 minute RPO chain
BACKUP DATABASE SalesDb TO DISK = 'F:\bak\SalesDb_full.bak'
    WITH INIT, CHECKSUM, COMPRESSION, STATS = 5;

BACKUP DATABASE SalesDb TO DISK = 'F:\bak\SalesDb_diff.bak'
    WITH DIFFERENTIAL, INIT, CHECKSUM, COMPRESSION;

BACKUP LOG SalesDb TO DISK = 'F:\bak\SalesDb_log_202608111215.trn'
    WITH CHECKSUM, COMPRESSION;

-- Point-in-time restore to just before the bad UPDATE
RESTORE DATABASE SalesDb FROM DISK = 'F:\bak\SalesDb_full.bak' WITH NORECOVERY, REPLACE;
RESTORE DATABASE SalesDb FROM DISK = 'F:\bak\SalesDb_diff.bak' WITH NORECOVERY;
RESTORE LOG      SalesDb FROM DISK = 'F:\bak\SalesDb_log_202608111215.trn'
    WITH STOPAT = '2026-08-11T12:07:30', RECOVERY;

Answer

tempdb is shared by every database on the instance and absorbs temp tables, table variables, worktables for sorts and hashes, spills, the row version store for RCSI and snapshot isolation, online index build sorts, and the internal objects for cursors and service broker. Because allocations happen constantly, the allocation bitmap pages become a hotspot. Every data file has PFS pages (page free space, one per 8088 pages), GAM and SGAM pages (global and shared global allocation maps, one per 64000 extents), and these pages must be latched to be modified.

When hundreds of sessions create and drop temp tables at once, they queue on PAGELATCH_UP waits against page 2:1:1 or 2:1:3, which is allocation contention, not IO. The fix is multiple equally sized data files so allocations round-robin across separate bitmap pages. The guidance is one file per logical core up to eight, then add four at a time if contention persists, all the same size with the same growth increment so proportional fill keeps them balanced.

Since SQL Server 2016 the installer offers this at setup and the behaviour of trace flags 1117 (grow all files together) and 1118 (full extent allocation) is on by default for tempdb, so quoting those trace flags as a fix marks you as out of date unless you say they are now defaults. SQL Server 2019 added memory-optimized tempdb metadata, ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON, which removes the separate metadata contention on system tables that shows up as waits on sysschobjs. Other essentials: put tempdb on the fastest local storage available, pre-size the files rather than relying on autogrowth, never enable autoshrink, and never rely on tempdb being empty, because a single runaway spill or version store can consume the whole drive and take the instance down.

-- Allocation contention shows as PAGELATCH_UP on 2:1:1 / 2:1:3
SELECT session_id, wait_type, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%'
  AND resource_description LIKE '2:%';

-- Who is consuming tempdb right now?
SELECT su.session_id,
       (su.user_objects_alloc_page_count - su.user_objects_dealloc_page_count) * 8 / 1024 AS user_mb,
       (su.internal_objects_alloc_page_count - su.internal_objects_dealloc_page_count) * 8 / 1024 AS internal_mb,
       t.text
FROM sys.dm_db_session_space_usage AS su
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = su.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
ORDER BY internal_mb DESC;

-- Equal-sized files, pre-sized, uniform growth
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev,  SIZE = 8192MB, FILEGROWTH = 1024MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev2, SIZE = 8192MB, FILEGROWTH = 1024MB);

-- 2019+: removes tempdb system-table metadata contention (needs a restart)
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Answer

Columnstore stores data column by column in compressed segments grouped into rowgroups of up to 1,048,576 rows, with segment-level metadata that records the min and max value per segment. That layout gives three wins for analytics: compression of ten times or more because a single column's values are similar, segment elimination so a predicate on a date column skips entire rowgroups without reading them, and batch mode execution which processes 900 rows per CPU instruction cycle group instead of one row at a time. A fact table scan that takes 90 seconds on rowstore commonly runs in two on clustered columnstore.

Use it for fact tables, reporting warehouses, and large append-only history tables where queries aggregate over millions of rows and touch few columns. Do not use it as the primary structure for OLTP tables that do singleton lookups and frequent updates, because an UPDATE on columnstore is implemented as a delete plus an insert and inflates the delete bitmap. The nuances interviewers probe are about the delta store: rows inserted in batches smaller than 102,400 land in a rowstore delta rowgroup marked OPEN, and only get compressed when the tuple mover runs or you rebuild or reorganize with COMPRESS_ALL_ROW_GROUPS.

Trickle inserts therefore produce hundreds of tiny rowgroups and destroy scan performance, so bulk load with a batch size above 102,400 and TABLOCK for parallel minimally logged insert. Also worth naming: a nonclustered columnstore index on a rowstore OLTP table gives you real-time operational analytics without a separate warehouse, and since SQL Server 2016 batch mode also works alongside rowstore, with SQL Server 2019 adding batch mode on rowstore for qualifying large scans even without any columnstore index present.

-- Fact table: clustered columnstore is the whole table
CREATE TABLE dbo.FactSales (
    SalesDate DATE NOT NULL, BranchId INT NOT NULL,
    Sku NVARCHAR(30) NOT NULL, Qty INT NOT NULL, Amount DECIMAL(12,2) NOT NULL
);
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales ON dbo.FactSales;

-- Rowgroup health: OPEN rowgroups and tiny row counts are the problem signal
SELECT state_desc, COUNT(*) AS rowgroups, AVG(total_rows) AS avg_rows,
       SUM(deleted_rows) AS deleted
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('dbo.FactSales')
GROUP BY state_desc;

-- Fix trickle-insert fragmentation
ALTER INDEX CCI_FactSales ON dbo.FactSales
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

-- Operational analytics on an OLTP table without a warehouse
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders
    ON dbo.Orders (OrderDate, BranchId, Amount)
    WHERE Status = 'CLOSED';

Answer

A DML trigger is a special stored procedure attached to INSERT, UPDATE or DELETE. AFTER triggers fire once per statement after the modification and after constraints are checked; INSTEAD OF triggers fire in place of the modification and are the only way to make a multi-table view updatable. Inside the trigger you get two pseudo-tables, inserted and deleted; an UPDATE populates both, with deleted holding the before image and inserted the after image.

You can also write DDL triggers on CREATE, ALTER and DROP events, which are the easy way to build a schema change audit, and logon triggers, which are a good way to lock yourself out of an instance if you get them wrong. The bug that appears in almost every codebase is writing the trigger as if it fires once per row. Someone writes SELECT @OrderId = OrderId FROM inserted, which picks an arbitrary single row, then updates one record; the moment a batch statement inserts 500 rows the trigger silently processes one of them and the other 499 are wrong.

The correct form is always set based: join inserted and deleted to the target table. Two other trigger facts worth knowing: @@ROWCOUNT at the top of a trigger reflects the triggering statement, and an early exit for IF @@ROWCOUNT = 0 avoids doing work for no-op statements, but you must SET NOCOUNT ON first or the row counts confuse client libraries. Also, UPDATE(ColumnName) tells you whether a column was named in the SET clause, not whether the value actually changed, so use it as a cheap filter and still compare inserted to deleted for real change detection. Nested and recursive triggers are configurable, default nesting depth is 32, and a trigger that rolls back terminates the whole batch.

CREATE OR ALTER TRIGGER dbo.trg_Orders_Audit
ON dbo.Orders
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF @@ROWCOUNT = 0 RETURN;

    -- WRONG: assumes one row
    -- DECLARE @id INT; SELECT @id = OrderId FROM inserted;

    -- RIGHT: set based, handles a 500-row batch correctly
    INSERT dbo.OrderStatusAudit (OrderId, OldStatus, NewStatus, ChangedAt, ChangedBy)
    SELECT i.OrderId, d.Status, i.Status, SYSUTCDATETIME(), ORIGINAL_LOGIN()
    FROM inserted AS i
    JOIN deleted  AS d ON d.OrderId = i.OrderId
    WHERE UPDATE(Status)              -- cheap filter: was Status in the SET list
      AND (i.Status <> d.Status
           OR (i.Status IS NULL) <> (d.Status IS NULL));   -- real change only
END
GO

-- DDL trigger: audit every schema change on the database
CREATE OR ALTER TRIGGER trg_DdlAudit ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
    INSERT dbo.DdlAudit (EventData, LoggedAt, LoginName)
    VALUES (EVENTDATA(), SYSUTCDATETIME(), ORIGINAL_LOGIN());

Answer

Dynamic SQL is justified when the shape of the statement genuinely varies: an optional-parameter search screen where users can filter on any subset of ten columns, a generic archival routine that operates on a table name passed in, or DDL generation. It is not justified as a shortcut for passing values, and that distinction is the whole answer. Values go in as parameters via sp_executesql; only identifiers get concatenated, and identifiers must be validated.

Use QUOTENAME() to bracket a table or column name and, better, validate the name against sys.objects or sys.columns before you use it, because QUOTENAME protects against quote-breaking but not against someone naming a real object something you did not intend to touch. EXEC ('string') has no parameter interface at all, so every value gets concatenated into the text, which is both an injection vector and a plan-cache disaster: each distinct literal produces a separate cached plan and bloats the cache with single-use entries. sp_executesql takes a parameter definition list, so values are passed as real parameters, the text is stable, and the plan is reused. The optional-parameter search case has a second twist worth mentioning: writing it as a single static query with WHERE (@City IS NULL OR City = @City) repeated ten times gives one plan that is wrong for most parameter combinations, so either add OPTION (RECOMPILE) or build the WHERE clause dynamically so each real combination gets its own good plan. Also remember that dynamic SQL runs under the caller's permissions by default and breaks ownership chaining, so a procedure that reads a table through dynamic SQL needs the caller to have SELECT on that table unless you use EXECUTE AS or module signing with a certificate.

CREATE OR ALTER PROCEDURE dbo.usp_SearchOrders
    @BranchId INT = NULL, @Status NVARCHAR(20) = NULL, @MinAmount DECIMAL(12,2) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX) =
        N'SELECT OrderId, CustomerId, Amount, Status FROM dbo.Orders WHERE 1 = 1';

    IF @BranchId  IS NOT NULL SET @sql += N' AND BranchId = @BranchId';
    IF @Status    IS NOT NULL SET @sql += N' AND Status = @Status';
    IF @MinAmount IS NOT NULL SET @sql += N' AND Amount >= @MinAmount';

    -- values are PARAMETERS, never concatenated
    EXEC sys.sp_executesql @sql,
         N'@BranchId INT, @Status NVARCHAR(20), @MinAmount DECIMAL(12,2)',
         @BranchId = @BranchId, @Status = @Status, @MinAmount = @MinAmount;
END
GO

-- Identifier injected safely: validate, then QUOTENAME
DECLARE @tbl SYSNAME = N'AuditLog', @dyn NVARCHAR(MAX);
IF OBJECT_ID(N'dbo.' + QUOTENAME(@tbl), 'U') IS NULL THROW 50010, 'Unknown table', 1;
SET @dyn = N'TRUNCATE TABLE dbo.' + QUOTENAME(@tbl);
EXEC sys.sp_executesql @dyn;

Answer

SQL Server 2016 added JSON as a set of functions over text rather than a storage type: ISJSON validates, JSON_VALUE extracts a scalar, JSON_QUERY extracts an object or array, JSON_MODIFY updates a path, OPENJSON shreds JSON into rows with an explicit WITH schema, and FOR JSON PATH or FOR JSON AUTO serialises a result set. SQL Server 2022 added JSON_PATH_EXISTS, JSON_OBJECT and JSON_ARRAY constructors and the ISJSON type-checking arguments. More recent versions introduced a native json data type with binary storage, which avoids reparsing the text on every access and is a genuine improvement for document-heavy workloads, so mention it as a newer option and check availability on your target build.

Whether you should store JSON at all is the interesting half of the question. It is right for genuinely sparse or schemaless attributes: an integration payload you must retain verbatim for audit, per-tenant custom fields, an event body whose shape changes with the producer. It is wrong for anything you filter or join on regularly, because a JSON_VALUE predicate is not SARGable against a plain nvarchar column.

The workaround is a persisted computed column over JSON_VALUE with a normal index on it, which gives seek performance on the specific attributes that matter while keeping the rest flexible. Two practical warnings: JSON stored in NVARCHAR(MAX) is off-row once it exceeds 8000 bytes, so every read is an extra LOB fetch, and OPENJSON without a WITH clause returns everything as NVARCHAR(4000) key/value/type triples, so always declare the schema for both correctness and cardinality estimation, since OPENJSON otherwise guesses 50 rows.

CREATE TABLE dbo.WebhookEvent (
    EventId  BIGINT IDENTITY PRIMARY KEY,
    Payload  NVARCHAR(MAX) NOT NULL CONSTRAINT CK_Payload_Json CHECK (ISJSON(Payload) = 1),
    -- index the one attribute you actually filter on
    OrderRef AS JSON_VALUE(Payload, '$.order.reference') PERSISTED
);
CREATE INDEX IX_WebhookEvent_OrderRef ON dbo.WebhookEvent (OrderRef);

-- Shred an array into rows: always declare the schema
SELECT e.EventId, li.Sku, li.Qty, li.Price
FROM dbo.WebhookEvent AS e
CROSS APPLY OPENJSON(e.Payload, '$.order.lineItems')
    WITH (Sku   NVARCHAR(30)   '$.sku',
          Qty   INT            '$.qty',
          Price DECIMAL(12,2)  '$.price') AS li
WHERE e.OrderRef = 'GS-100244';

-- Patch a path in place
UPDATE dbo.WebhookEvent
SET Payload = JSON_MODIFY(Payload, '$.status', 'processed')
WHERE EventId = 91;

-- Serialise a result set for an API response
SELECT OrderId, Amount, Status
FROM dbo.Orders WHERE CustomerId = 4711
FOR JSON PATH, ROOT('orders');

Answer

These solve four different problems and interviewers ask the comparison precisely because candidates conflate them. Transparent Data Encryption encrypts the database files, log and backups at rest using a database encryption key protected by a certificate in master. It defends against someone stealing an MDF file or a backup tape, and it is transparent to applications, but a user who can query the database sees plaintext, so it does nothing against a rogue DBA or a compromised login.

Always Encrypted is the one that does: encryption and decryption happen in the client driver using a column master key that the database server never holds, so DBAs, and Microsoft in the case of Azure SQL, see ciphertext only. The cost is functionality; deterministic encryption allows equality comparison and joins but leaks value distribution, randomized encryption is safer but permits no server-side operations at all, and the Enclave-based variants (secure enclaves with VBS or Intel SGX) restore range comparisons and pattern matching. Dynamic Data Masking is presentation only: it rewrites the returned value for users without UNMASK permission, so a support agent sees XXXX-1234, but it is trivially defeated by a WHERE clause that infers the underlying value, which is why you must never describe it as encryption.

Row-Level Security uses an inline table-valued function as a security predicate bound with CREATE SECURITY POLICY, so a multi-tenant table filters rows automatically based on SESSION_CONTEXT or the login, and it enforces on FILTER (silently hides) and BLOCK (raises an error on writes). In a regulated Indian setup handling PAN, Aadhaar or card data, the realistic stack is TDE plus Always Encrypted on the sensitive columns plus RLS for tenant isolation plus SQL Server Audit for accountability, and DDM only as a convenience for internal tooling.

-- Multi-tenant isolation with Row-Level Security
CREATE FUNCTION dbo.fn_TenantPredicate (@TenantId INT)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN SELECT 1 AS ok
       WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS INT)
          OR IS_MEMBER('db_owner') = 1;
GO

CREATE SECURITY POLICY dbo.TenantFilter
    ADD FILTER PREDICATE dbo.fn_TenantPredicate(TenantId) ON dbo.Orders,
    ADD BLOCK  PREDICATE dbo.fn_TenantPredicate(TenantId) ON dbo.Orders AFTER INSERT
    WITH (STATE = ON);
GO

-- The application sets the context once per connection
EXEC sys.sp_set_session_context @key = N'TenantId', @value = 42, @read_only = 1;

-- Masking is display-only, never a security boundary
ALTER TABLE dbo.Customer
    ALTER COLUMN PAN ADD MASKED WITH (FUNCTION = 'partial(0, "XXXXX", 4)');
GRANT UNMASK TO ComplianceRole;

Answer

Wait statistics answer the only question that matters: what were worker threads waiting on instead of running. Start with sys.dm_os_wait_stats, filter out the roughly forty benign background waits (SLEEP_TASK, XE_TIMER_EVENT, BROKER_TASK_STOP, HADR_FILESTREAM_IOMGR_IOCOMPLETION and friends), rank the rest by wait_time_ms, and convert to waits per core per second so the numbers mean something. sys.dm_os_wait_stats is cumulative since startup, so clear it with DBCC SQLPERF and re-measure over a controlled interval, or use sys.dm_db_wait_stats on Azure SQL. Then map the top waits.

PAGEIOLATCH_SH or PAGEIOLATCH_EX means reads are going to disk, which is either genuinely slow storage or, far more often, insufficient buffer pool and a missing index causing large scans, so cross-check page life expectancy and the top queries by logical reads before blaming the SAN. PAGELATCH_UP on 2:1:x is tempdb allocation contention. WRITELOG means log flush latency, check the storage latency for the log volume in sys.dm_io_virtual_file_stats and whether delayed durability or a faster log disk is appropriate.

LCK_M_X and friends are blocking, so go to the blocking chain. RESOURCE_SEMAPHORE means queries are queuing for memory grants, which points at bad cardinality estimates requesting huge grants. THREADPOOL means worker starvation, usually from massive blocking or too low a MAXDOP with high concurrency, and it is dangerous because it can prevent you from even logging in (use the dedicated admin connection).

CXCONSUMER is generally benign, while CXPACKET combined with skewed parallel row distribution points at bad estimates, and the modern fix is fixing the estimate rather than setting MAXDOP to 1. SOS_SCHEDULER_YIELD in volume means CPU pressure with no waiting at all. Pair the wait analysis with Query Store, because since SQL Server 2017 Query Store attributes waits per query, which turns a server-level symptom into a specific statement to fix.

-- Reset, run the workload for a measured interval, then read
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
WAITFOR DELAY '00:10:00';

SELECT TOP (15) wait_type,
       wait_time_ms / 1000.0 AS wait_s,
       (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_s,
       signal_wait_time_ms / 1000.0 AS cpu_queue_s,
       waiting_tasks_count,
       wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_ms_per_wait
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('SLEEP_TASK','BROKER_TASK_STOP','XE_TIMER_EVENT',
       'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','DIRTY_PAGE_POLL','HADR_FILESTREAM_IOMGR_IOCOMPLETION',
       'LAZYWRITER_SLEEP','LOGMGR_QUEUE','CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH',
       'XE_DISPATCHER_WAIT','BROKER_TO_FLUSH','SLEEP_SYSTEMTASK','WAITFOR')
ORDER BY wait_time_ms DESC;

-- Is PAGEIOLATCH real storage latency or just too many reads?
SELECT DB_NAME(vfs.database_id) AS db, mf.physical_name,
       vfs.io_stall_read_ms  / NULLIF(vfs.num_of_reads, 0)  AS avg_read_ms,
       vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) AS avg_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
  ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
ORDER BY avg_read_ms DESC;

Answer

Intelligent Query Processing is a family of optimizer features gated by database compatibility level, so the first practical point is that upgrading the instance gives you nothing until you raise COMPATIBILITY_LEVEL, and you should raise it with Query Store already collecting a baseline so you can force pre-upgrade plans if something regresses. The 2017 and 2019 features are adaptive joins (defer the nested loop versus hash decision until the build input row count is known), interleaved execution for multi-statement TVFs, batch mode on rowstore, memory grant feedback in batch mode then row mode, and scalar UDF inlining. SQL Server 2022 made the big change: memory grant feedback became persistent, stored in Query Store rather than lost on eviction or restart, and percentile-based so it uses recent history instead of only the last execution.

It added degree of parallelism feedback, which lowers DOP for queries whose parallelism buys nothing, cardinality estimation feedback, which detects when a model assumption such as correlation or containment was wrong and applies a hint, and Parameter Sensitive Plan optimization. Optimized plan forcing reduces recompilation cost for forced plans. What you still tune by hand: indexing, because no IQP feature will invent a covering index; data type mismatches and non-SARGable predicates, because those are written in your code; statistics on ascending keys; and physical design decisions such as partitioning or columnstore.

IQP also has a real failure mode worth mentioning, feedback features need repeated executions of a stable query to converge, so ad hoc workloads with unique text see little benefit, and all feedback is discarded on failover unless it has been persisted to Query Store. Check sys.query_store_plan_feedback and the query_feedback_analysis extended events to see whether feedback actually applied.

-- IQP is gated on compatibility level, not on the binaries
ALTER DATABASE SalesDb SET COMPATIBILITY_LEVEL = 160;

-- Query Store must be on for persisted memory grant feedback
ALTER DATABASE SalesDb SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

-- Turn a single feature off without dropping the whole compat level
ALTER DATABASE SCOPED CONFIGURATION
    SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;
ALTER DATABASE SCOPED CONFIGURATION
    SET DOP_FEEDBACK = ON;

-- Did feedback actually apply to this query?
SELECT f.feature_desc, f.state_desc, f.feedback_data, p.query_id, p.plan_id
FROM sys.query_store_plan_feedback AS f
JOIN sys.query_store_plan AS p ON p.plan_id = f.plan_id
ORDER BY f.create_time DESC;

-- Per-query opt-out when a feature misbehaves
SELECT * FROM dbo.Orders WHERE BranchId = @b
OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'));

Answer

PSP optimization, introduced in SQL Server 2022 at compatibility level 160, attacks the single-plan-for-a-skewed-predicate problem without any code change. At compile time the optimizer identifies up to three eligible equality predicates on columns with skewed statistics, then creates a dispatcher plan for the statement. The dispatcher holds a predicate-cardinality boundary derived from the histogram and maps runtime parameter values into one of three buckets: low, medium and high cardinality.

Each bucket gets its own compiled query variant, cached separately, so the Mumbai branch and a small branch each execute a plan built for their real row counts. You can see it working because the plan XML contains a Dispatcher node with the low and high boundary values, and sys.dm_exec_query_stats shows multiple plan handles for one query hash. The limits matter in an interview answer.

Only equality predicates qualify, not ranges or LIKE. At most three predicates per statement. The column needs skewed statistics for the feature to engage at all, and if the skew is in a correlated pair of columns rather than one column, PSP will not help.

Three buckets is coarse: a workload with genuinely continuous cardinality variation still gets a plan built for the wrong end of a bucket. So OPTION (RECOMPILE) remains the better answer when the statement runs infrequently relative to its cost (a nightly report where a 40 millisecond compile is irrelevant next to a four minute run), when the predicate is a range or a complex multi-column filter, when parameter embedding lets the optimizer fold literals and simplify the plan, or when you need a guaranteed optimal plan rather than a bucketed approximation. It is the wrong answer for a small statement executed 3000 times a second, where the compile CPU becomes the bottleneck and you will see a spike in SOS_SCHEDULER_YIELD and cache misses.

ALTER DATABASE SalesDb SET COMPATIBILITY_LEVEL = 160;   -- PSP requires 160

-- Skewed column: one branch has 4 million rows, most have a few hundred
CREATE INDEX IX_Orders_Branch ON dbo.Orders (BranchId) INCLUDE (Amount, Status);
UPDATE STATISTICS dbo.Orders IX_Orders_Branch WITH FULLSCAN;

CREATE OR ALTER PROCEDURE dbo.usp_OrdersByBranch @BranchId INT
AS
    SELECT OrderId, Amount, Status FROM dbo.Orders WHERE BranchId = @BranchId;
GO

-- Multiple plans for ONE query hash is the PSP signature
SELECT qs.query_hash, COUNT(DISTINCT qs.query_plan_hash) AS variants,
       SUM(qs.execution_count) AS execs
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE t.text LIKE '%usp_OrdersByBranch%'
GROUP BY qs.query_hash;

-- Opt out for one statement if the bucketing hurts
SELECT OrderId FROM dbo.Orders WHERE BranchId = @BranchId
OPTION (USE HINT ('DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION'));

Answer

Partitioning is a manageability feature first and a performance feature second, and stating it that way is what separates a senior answer. The design: a partition function on the audit timestamp with monthly RANGE RIGHT boundaries, a partition scheme mapping partitions to filegroups (one filegroup per month if you want to take piecemeal or read-only filegroup backups of old data, otherwise a single filegroup is simpler and still gives you switching), and the partitioning column must be part of every unique index key including the primary key, otherwise indexes end up non-aligned and partition switching is blocked. Use RANGE RIGHT with datetime boundaries so the boundary value belongs to the upper partition, which makes month boundaries intuitive and avoids off-by-one errors.

Retention then becomes metadata-only: create an empty staging table with an identical structure, identical indexes, and the same filegroup as the oldest partition, SWITCH that partition into the staging table, and drop or archive the staging table. The switch is a metadata operation that completes in milliseconds regardless of row count, versus hours of DELETE with log growth and blocking. The monthly maintenance job also splits a new empty boundary at the top and merges the emptied boundary at the bottom, and the golden rule is that SPLIT and MERGE must only ever touch empty partitions, because splitting a populated partition physically moves data while holding a schema modification lock.

On the query side, partition elimination only happens when the predicate is on the partitioning column in a SARGable form, so a query filtering on CreatedAt with a function around it scans every partition. Two more items interviewers listen for: partitioning does not replace indexing, and Enterprise edition is not required since SQL Server 2016 SP1, which put partitioning into Standard edition.

-- Monthly RANGE RIGHT boundaries
CREATE PARTITION FUNCTION PF_AuditMonth (DATETIME2(0))
AS RANGE RIGHT FOR VALUES
    ('2024-09-01','2024-10-01','2024-11-01','2024-12-01','2025-01-01');

CREATE PARTITION SCHEME PS_AuditMonth
AS PARTITION PF_AuditMonth ALL TO ([PRIMARY]);

CREATE TABLE dbo.AuditLog (
    AuditId   BIGINT IDENTITY NOT NULL,
    CreatedAt DATETIME2(0)    NOT NULL,
    Payload   NVARCHAR(MAX)   NULL,
    CONSTRAINT PK_AuditLog PRIMARY KEY CLUSTERED (CreatedAt, AuditId)  -- partition col in the key
) ON PS_AuditMonth (CreatedAt);

-- Expiry in milliseconds instead of a multi-hour DELETE
CREATE TABLE dbo.AuditLog_Stage (
    AuditId BIGINT NOT NULL, CreatedAt DATETIME2(0) NOT NULL, Payload NVARCHAR(MAX) NULL,
    CONSTRAINT PK_AuditLog_Stage PRIMARY KEY CLUSTERED (CreatedAt, AuditId)
) ON [PRIMARY];

ALTER TABLE dbo.AuditLog SWITCH PARTITION 2 TO dbo.AuditLog_Stage;
DROP TABLE dbo.AuditLog_Stage;

-- Roll the window forward: only ever split and merge EMPTY partitions
ALTER PARTITION SCHEME PS_AuditMonth NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION PF_AuditMonth() SPLIT RANGE ('2026-10-01');
ALTER PARTITION FUNCTION PF_AuditMonth() MERGE RANGE ('2024-09-01');

Answer

Start by separating the two goals. High availability means automatic failover with no data loss, which requires synchronous commit replicas: the primary does not acknowledge a commit until the secondary has hardened the log record. Disaster recovery across a distant region means asynchronous commit, because synchronous commit over a link with 30 milliseconds of latency adds that latency to every single commit, and a payments workload doing 2000 commits per second will collapse.

A typical design for an Indian deployment: two synchronous replicas in the primary region (Mumbai) with automatic failover, plus one asynchronous replica in a second region (Hyderabad or Chennai) with manual failover, sitting on a Windows Server Failover Cluster with a file share or cloud witness so the quorum survives losing any one node. Configure a listener so applications connect to a name rather than a server, set MultiSubnetFailover=true in the connection string for cross-subnet setups, and add Connect Timeout headroom. Read-scale is the tempting part and also the trap: readable secondaries are not synchronised for reads, they are eventually consistent, so a write on the primary followed immediately by a read on ApplicationIntent=ReadOnly can miss the row.

Never route read-your-own-writes traffic to a secondary in a payments flow. Two more failure modes to name. Readable secondaries add 14 bytes of version information to rows on the primary, causing a one-time page split storm on tables with no free space.

And an AG secondary that falls behind pins the primary's transaction log, showing up as log_reuse_wait_desc of AVAILABILITY_REPLICA, so the primary's log grows until the disk fills, which is how an HA feature causes an outage. Monitor the redo queue and log send queue, test failover on a schedule, and remember that AGs replicate databases, not instance-level objects, so logins, SQL Agent jobs, linked servers and credentials must be synchronised separately or contained databases used.

-- Replica roles and synchronisation health
SELECT ar.replica_server_name, ar.availability_mode_desc, ar.failover_mode_desc,
       ars.role_desc, ars.synchronization_health_desc, ars.connected_state_desc
FROM sys.availability_replicas AS ar
JOIN sys.dm_hadr_availability_replica_states AS ars
  ON ars.replica_id = ar.replica_id;

-- Is a secondary pinning the primary's log?
SELECT DB_NAME(drs.database_id) AS db, ar.replica_server_name,
       drs.synchronization_state_desc,
       drs.log_send_queue_size, drs.redo_queue_size, drs.redo_rate
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas AS ar ON ar.replica_id = drs.replica_id
ORDER BY drs.redo_queue_size DESC;

SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = 'PaymentsDb';
-- AVAILABILITY_REPLICA here means a lagging secondary is holding your log

-- Connection string for the listener (cross-subnet)
-- Server=tcp:agl-payments,1433;Database=PaymentsDb;MultiSubnetFailover=True;
--   ApplicationIntent=ReadWrite;Connect Timeout=30;Encrypt=True;

Answer

Classic recovery uses ARIES: analysis, redo from the oldest uncommitted transaction, then undo. The undo phase must walk the transaction log backwards through every log record of every uncommitted transaction, so a batch job that ran for four hours and got killed produces roughly four hours of single-threaded rollback, and if that happens during startup the database stays in recovering state, unavailable, for the entire time. Worse, the log cannot truncate while a long transaction is active, so the log file grows unbounded.

Accelerated Database Recovery, introduced in SQL Server 2019 and on by default in Azure SQL, changes this by adding a persistent version store inside the user database plus a logical revert mechanism. Every row modification writes the prior version into the PVS in the same database, so rollback becomes a matter of marking the transaction aborted and letting readers logically revert to the last committed version, rather than physically undoing each change. The results are the three things to name: near-instant rollback and near-instant recovery regardless of transaction size, aggressive log truncation because the log no longer has to retain records for the undo phase, and a bounded log even with a long-running transaction open.

The cost is real and worth being honest about in an interview. The PVS consumes space in the user database, sometimes a great deal on update-heavy workloads, and if the cleanup process cannot keep up (often because a long-running snapshot or an open transaction blocks it) PVS growth becomes the new disk problem. Monitor sys.dm_tran_persistent_version_store_stats.

There is also a modest write overhead per update. Enable it on databases with long transactions, large batch jobs, or aggressive RTO targets; it is less compelling on a small OLTP database whose transactions are already milliseconds long.

-- On by default in Azure SQL; opt in on-premises
ALTER DATABASE SalesDb SET ACCELERATED_DATABASE_RECOVERY = ON;

-- Put the persistent version store on its own filegroup for large workloads
ALTER DATABASE SalesDb ADD FILEGROUP PVS_FG;
ALTER DATABASE SalesDb ADD FILE
    (NAME = SalesDb_pvs, FILENAME = 'E:\\data\\SalesDb_pvs.ndf', SIZE = 8GB)
    TO FILEGROUP PVS_FG;
ALTER DATABASE SalesDb SET ACCELERATED_DATABASE_RECOVERY = ON (PERSISTENT_VERSION_STORE_FILEGROUP = PVS_FG);

-- Watch PVS growth and whether cleanup is keeping up
SELECT DB_NAME(database_id) AS db,
       persistent_version_store_size_kb / 1024 AS pvs_mb,
       online_index_version_store_size_kb / 1024 AS online_ix_mb,
       oldest_active_transaction_id,
       oldest_aborted_transaction_id,
       current_aborted_transaction_count
FROM sys.dm_tran_persistent_version_store_stats
WHERE database_id = DB_ID('SalesDb');

Answer

Establish the timeline before touching anything: exactly which night did it change, and what else changed then. A statistics auto-update, an index rebuild, a compatibility level change, a data volume threshold crossed, a patch, or a new AG secondary all fit the description of no code change. Query Store gives you the before-and-after directly, compare the plan_id and runtime stats for the statement across the two dates, and if the plan changed you already have a candidate.

Second, isolate: is the job slow the whole way through or at one statement? SQL Agent job history gives step durations, and adding RAISERROR WITH NOWAIT progress markers or writing step timings to a log table pinpoints the statement. Third, classify the wait: while it runs, sample sys.dm_exec_requests for wait_type and blocking_session_id every few seconds.

CXPACKET plus a huge logical read count is a plan problem; LCK_M_S is blocking from a concurrent job; PAGEIOLATCH plus tempdb growth is a spill; ASYNC_NETWORK_IO means the client is not consuming rows fast enough and the server is fine. Fourth, look at the actual plan for the offending statement and check the four usual suspects in order: an estimate that is now wrong by orders of magnitude (statistics stale on an ascending date key, which is exactly the pattern for a nightly job filtering on yesterday), an implicit conversion introduced by a schema change to a joined table, a Key Lookup that became expensive as row counts grew past the tipping point where the optimizer would have preferred a scan, and a hash or sort spill because the memory grant was sized from that bad estimate. In practice the ascending-key statistics case accounts for a large share of these incidents. The fix sequence is: update statistics on the date column with FULLSCAN, re-measure, then if the plan is still wrong force the known-good plan from Query Store as a tourniquet, then address the root cause with a covering index or a query rewrite, and finally add a statistics update step at the end of the upstream load so it does not recur.

-- Before and after, straight from Query Store
SELECT qt.query_sql_text, p.plan_id,
       CAST(rsi.start_time AS DATE) AS day_,
       rs.count_executions,
       rs.avg_duration / 1000000.0 AS avg_sec,
       rs.avg_logical_io_reads,
       rs.avg_query_max_used_memory
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi
  ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE qt.query_sql_text LIKE '%usp_NightlyReconcile%'
ORDER BY rsi.start_time DESC;

-- Stale stats on the ascending date key: the classic cause
SELECT s.name, sp.last_updated, sp.rows, sp.rows_sampled, sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID('dbo.LedgerEntry');

UPDATE STATISTICS dbo.LedgerEntry WITH FULLSCAN;

-- Tourniquet while you fix the real cause
EXEC sys.sp_query_store_force_plan @query_id = 8812, @plan_id = 14907;

Answer

Two release lines are worth being current on. SQL Server 2022 is still the workhorse in most Indian enterprise estates, and the features that come up are the ones already discussed: Parameter Sensitive Plan optimization, persistent and percentile memory grant feedback, DOP and cardinality estimation feedback, contained availability groups (which finally replicate logins and Agent jobs with the AG), ledger tables for tamper evidence backed by a Merkle tree and optional blockchain-style digests, Azure Synapse Link for near real-time analytics without ETL, and backup to S3-compatible object storage. SQL Server 2025 is the follow-on release and its headline theme is bringing AI workloads into the engine: a native vector data type with vector index support and functions such as VECTOR_DISTANCE for similarity search, so you can keep embeddings alongside the relational rows they describe instead of running a separate vector database; a native json data type with binary storage that removes the reparse cost of NVARCHAR JSON; regular expression functions including REGEXP_LIKE, REGEXP_REPLACE and REGEXP_SUBSTR, which is a long-standing gap versus PostgreSQL and Oracle; and optional parameter plan optimization, which extends the PSP idea to procedures with optional parameters.

Confirm exact feature availability and edition limits against the build you are targeting before you promise anything in a design review, because feature gating between Enterprise, Standard and Azure SQL varies. The credible interview answer is not a feature list recital; it is knowing which of these would change a decision. Vector support means an existing SQL Server shop can prototype retrieval-augmented search without adding infrastructure.

The native json type changes whether you normalise a payload. Contained AGs remove a class of failover incidents. Everything else is incremental.

-- SQL Server 2022: ledger table for tamper-evident audit
CREATE TABLE dbo.PaymentLedger (
    PaymentId BIGINT NOT NULL PRIMARY KEY CLUSTERED,
    Amount    DECIMAL(18,2) NOT NULL,
    Payee     NVARCHAR(120) NOT NULL
)
WITH (SYSTEM_VERSIONING = ON, LEDGER = ON);

EXEC sys.sp_verify_database_ledger;

-- Contained AG: logins and Agent jobs travel with the group
CREATE AVAILABILITY GROUP AG_Payments WITH (CLUSTER_TYPE = WSFC, CONTAINED)
    FOR DATABASE PaymentsDb
    REPLICA ON N'SQLP1' WITH (ENDPOINT_URL = N'TCP://SQLP1:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC);

-- SQL Server 2025 direction: vectors stored next to relational data
-- CREATE TABLE dbo.JobDescription (
--     JobId INT PRIMARY KEY,
--     Body  NVARCHAR(MAX),
--     Embedding VECTOR(1536)
-- );
-- SELECT TOP (10) JobId, VECTOR_DISTANCE('cosine', Embedding, @queryVector) AS dist
-- FROM dbo.JobDescription ORDER BY dist;