SQL Server Interview Questions (2026): T-SQL & Performance Q&A

T-SQL is Microsoft's procedural extension of SQL, and the differences that trip people up are mostly syntactic. Variables are declared with a leading @ (DECLARE @count INT), TOP N limits row count instead of LIMIT, IDENTITY handles auto-incrementing columns instead of AUTO_INCREMENT or SERIAL, and T-SQL adds real procedural constructs: IF/ELSE, WHILE loops, TRY/CATCH error handling, and cursors. GO isn't actually part of T-SQL at all, it's a batch separator that SSMS and sqlcmd recognize client-side; the server never sees it.

It does, more than the two-second answer suggests. ISNULL is T-SQL-proprietary, takes exactly two arguments, and returns a data type based on the first argument, which can silently truncate a longer string if you're not careful. COALESCE is ANSI-standard, takes any number of arguments, and returns the highest-precedence type among all of them. I default to COALESCE unless I have a specific reason not to, mostly because the type behavior is more predictable and it isn't proprietary if the code ever needs to move.

INNER JOIN returns only rows that match in both tables. LEFT OUTER JOIN returns every row from the left table plus matching rows from the right, filling in NULL where there's no match. RIGHT OUTER JOIN is the mirror image, and in practice almost nobody writes it, since you can always flip the table order and use LEFT instead. FULL OUTER JOIN returns every row from both sides, matched where possible and NULL-padded where not. CROSS JOIN returns the full Cartesian product, every row from the left paired with every row from the right, which is rarely what you want unless you're deliberately generating combinations.

A self join joins a table to itself, using two aliases so SQL Server can tell the two "copies" apart. The classic example is an employees table with a manager_id column that points back to another row in the same table: to list each employee next to their manager's name, you join employees to employees a second time on manager_id = employee_id.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

WHERE filters individual rows before grouping happens, and an aggregate value doesn't exist yet at that stage, there's nothing to compare it against. HAVING runs after GROUP BY has produced its groups, so it's the clause that actually has an aggregate value available to filter on. Logically, SQL Server evaluates a query in the order FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, which is also why you can reference a column alias in ORDER BY but not in WHERE.

SELECT department_id, COUNT(*) AS headcount
FROM employees
WHERE status = 'active'
GROUP BY department_id
HAVING COUNT(*) > 10;

A clustered index determines the physical storage order of the table's data. It isn't a separate structure sitting next to the table, it is the table, organized as a B-tree where the leaf level holds the actual rows. A table can have exactly one clustered index, and a table with none is called a heap. A non-clustered index is a separate structure: its leaf level holds the index key plus a row locator, either the clustering key if the table has one, or a physical row ID if it's a heap. You can have up to 999 non-clustered indexes on a table, though I've never seen a real schema get anywhere close to that number without something else being wrong. See Microsoft's clustered and non-clustered indexes documentation for the full structural breakdown.

An estimated plan is built from statistics without running the query at all, so its row counts are the optimizer's compile-time guesses. An actual plan runs the query and reports what really happened: true row counts per operator, actual execution counts, and (since SQL Server 2016) live query statistics baked directly into the plan output. The single most useful thing to check first in any plan is where estimated and actual row counts diverge sharply on one operator. That gap is usually where the real problem is hiding.

A stored procedure can run DML with side effects (INSERT, UPDATE, DELETE against real tables), use TRY/CATCH for error handling, return multiple result sets, use output parameters, and can't be called from inside a SELECT statement. A function has to be effectively side-effect free (it can only modify local table variables, never real tables), can be used inline in a SELECT, WHERE, or JOIN, and historically got invoked once per row, sometimes called RBAR, row by agonizing row, which was a genuine performance liability before SQL Server 2019 shipped scalar UDF inlining.

A CTE, defined with WITH, is a named result set scoped to the single statement right after it. It isn't materialized or cached: if you reference the same CTE twice in one query, SQL Server can execute the underlying logic twice, which is the opposite of how a temp table behaves. Recursive CTEs (an anchor member, a recursive member, joined by UNION ALL) are the standard way to walk an org chart or a bill-of-materials tree.

WITH OrgChart AS (
    SELECT employee_id, manager_id, name, 0 AS depth
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.name, oc.depth + 1
    FROM employees e
    JOIN OrgChart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM OrgChart ORDER BY depth;

The ANSI standard defines four: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. SQL Server defaults to READ COMMITTED out of the box, which blocks dirty reads (you never see another transaction's uncommitted changes) but still allows non-repeatable reads and phantom reads within the same transaction. SQL Server also adds two row-versioning-based levels beyond the standard four: SNAPSHOT and READ COMMITTED SNAPSHOT, which behave differently enough that they deserve separate treatment. Full definitions are in Microsoft's SET TRANSACTION ISOLATION LEVEL documentation.

Atomicity, Consistency, Isolation, Durability. Atomicity means a transaction's statements either all commit or all roll back together, nothing partial. Consistency means a transaction moves the database from one valid state to another, respecting constraints and triggers along the way. Isolation is what the four isolation levels above actually control, how much of one in-flight transaction another transaction can see. Durability is what the transaction log guarantees specifically: once SQL Server reports a COMMIT as successful, that change is written to the log on disk and will survive a crash or power loss, even if the data file itself hasn't been flushed yet.

Blocking is normal: one transaction holds a lock a second transaction needs, so the second one waits until the first commits, rolls back, or the wait times out. A deadlock is two or more transactions each holding a lock the other one needs, a circular wait where neither can ever proceed on its own. SQL Server's lock monitor checks for that condition roughly every five seconds by default, more often once it starts finding them, and kills one transaction (the "deadlock victim," normally the one with the lowest estimated rollback cost) with error 1205.

A CTE isn't materialized once and reused, it's expanded inline everywhere it's referenced, closer to a macro than a stored result. Reference the same CTE three times in one statement and SQL Server can run the underlying query three separate times. A temp table, by contrast, computes once, stores the result physically in tempdb, and every later reference just reads that stored data. A CTE wrapped around an expensive query and then self-joined twice is a real, easy-to-miss performance trap.

DELETE is DML: it accepts a WHERE clause, fires DML triggers, and is fully logged row by row, so a big DELETE against a huge table generates a huge log. TRUNCATE behaves closer to a DDL operation: no WHERE clause allowed, it doesn't fire DML triggers, it deallocates whole data pages instead of logging individual row deletions (so it's minimally logged and much faster on large tables), it resets an IDENTITY column back to its seed value, and it requires ALTER permission on the table rather than DELETE permission.

An AFTER trigger fires once the triggering INSERT, UPDATE, or DELETE has already completed against the table, so the data is already there when the trigger's logic runs. AFTER triggers only work on tables, not views. An INSTEAD OF trigger fires in place of the triggering statement, meaning the original DML never actually happens unless the trigger's own code does it, which makes INSTEAD OF the standard way to make an otherwise non-updatable view (one built on a join, for instance) accept INSERT or UPDATE statements.

First normal form requires atomic values, no repeating groups or comma-separated lists stuffed into a single column. Second normal form requires that every non-key column depends on the entire primary key, not just part of a composite key, which only matters once your primary key has more than one column. Third normal form requires that non-key columns depend only on the key, not on each other, so a customers table storing both zip_code and city violates 3NF if city can be derived from zip_code, because city now depends on another non-key column instead of directly on the customer's primary key.

SIMPLE recovery model truncates the transaction log automatically after each checkpoint, which keeps log management effectively hands-off but means you can only restore to the point of your last full or differential backup, no point-in-time recovery. FULL recovery model keeps every logged transaction until a log backup runs, which is what makes point-in-time restore possible, at the cost of needing regular log backups so the log file itself doesn't grow without bound. BULK_LOGGED is a narrow middle ground that minimally logs certain bulk operations (index rebuilds, bulk inserts) for speed, while still supporting log backups, though it gives up point-in-time recovery for the specific window a minimally-logged operation ran in. Production databases that actually matter almost always run FULL, specifically so a log backup taken minutes before an incident isn't the most recent recovery point available.

TOP N grabs the first N rows after sorting and works fine for "give me the 10 highest," but it doesn't do true paging on its own. OFFSET-FETCH, added in SQL Server 2012, skips a specified number of rows and then fetches the next batch, which is what you actually need for page 2, page 3, and so on.

/* Page 3 of results, 20 rows per page, newest first */
SELECT order_id, customer_id, order_date
FROM orders
ORDER BY order_date DESC
OFFSET 40 ROWS
FETCH NEXT 20 ROWS ONLY;

One gotcha that catches people in interviews: TOP without an ORDER BY has no guaranteed row order at all. SQL Server will happily return whatever 10 rows the storage engine hands back first, and that can change between runs.

Putting the filter in ON happens during the join itself, before NULL-padding kicks in, so unmatched left rows still show up with NULL in the filtered column. Putting the same filter in WHERE happens after the join has already produced its NULL-padded rows, and WHERE has no special NULL handling, so any row where that column is NULL gets thrown out entirely. That silently turns your LEFT JOIN back into something that behaves like an INNER JOIN. I've seen this exact bug ship to production more than once: a report that was supposed to show customers with zero orders quietly stopped showing them the day someone added AND o.status = 'completed' to the WHERE clause instead of the ON clause.

/* Wrong: silently drops customers with no orders */
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'completed';

/* Right: keeps every customer, filters only matched orders */
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
    AND o.status = 'completed';

Often, yes. SQL Server's optimizer frequently rewrites a correlated subquery into a join-like plan internally, especially for EXISTS, so the "subqueries run once per outer row" mental model people bring in from procedural languages usually isn't what actually happens at execution time. That said, "often" isn't "always," and I wouldn't bet a production query's performance on the optimizer always making that rewrite. If you're staring at a plan and a subquery genuinely is running once per row (look for a Nested Loops operator with a suspiciously high execution count on the inner side), that's your evidence it didn't get rewritten this time, and it's worth trying the join form directly to see if the plan changes.

GROUP BY the columns that define a duplicate, then HAVING COUNT(*) > 1. That's the whole trick, and it's asked constantly because it's a genuinely useful pattern outside interviews too, cleaning up a staging table before it gets loaded into one with a unique constraint is a routine task on real projects.

SELECT email, COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

If you also need the actual duplicate row IDs so you can delete all but one, ROW_NUMBER() partitioned by the duplicate-defining columns, then deleting everything where the row number is greater than 1, does that in a single statement without a self join.

Plain GROUP BY gives you one row per unique combination of grouping columns. ROLLUP adds subtotal rows going from most detailed up to a single grand total, following the hierarchy of the columns you listed, so GROUP BY ROLLUP (region, product) gives you region-and-product rows, region-only subtotals, and one grand total row. CUBE goes further and produces subtotals for every possible combination of the grouping columns, not just the hierarchical path ROLLUP follows. The GROUPING() function tells you whether a NULL in the result came from an actual NULL value in the data or from a ROLLUP/CUBE subtotal row, which matters because both look identical otherwise.

A covering index is a non-clustered index that contains every column a specific query needs, either in the key itself or added via INCLUDE. When a query is fully covered, SQL Server answers it entirely from the non-clustered index and never touches the underlying table or clustered index. INCLUDE columns sit at the leaf level only, aren't sorted, and don't count toward the 16-column and 900-byte limits on index key size, which makes them the cheap way to widen a covering index without bloating the B-tree's upper levels.

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON dbo.Orders (customer_id)
INCLUDE (order_date, total_amount, status);

SARGable means "Search ARGument-able," meaning the predicate is written in a form the optimizer can use to seek an index directly. Wrap an indexed column in a function, like WHERE YEAR(order_date) = 2026, and the optimizer can't seek on it anymore, because it would have to evaluate that function against every row first. Same problem with implicit type conversions on the column side. Interviewers ask this constantly because it's one of the most common ways a perfectly indexed column still ends up doing a full scan, and rewriting it is almost free once you know the pattern.

/* Non-sargable: index on order_date can't be seeked */
SELECT order_id FROM orders WHERE YEAR(order_date) = 2026;

/* Sargable: rewritten as a range, seekable on the index */
SELECT order_id FROM orders
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

A statistics object is a histogram plus density information describing how values are distributed across a column or index key, and it's what the optimizer actually uses to estimate how many rows a predicate will match before it picks a plan. By default, SQL Server auto-updates statistics once roughly 20% of a table's rows have changed since the last update (small tables use a different, more aggressive threshold), which is also why a bulk load followed immediately by a query can hit a genuinely bad plan: the insert already happened, but the auto-update hasn't kicked in yet. AUTO_UPDATE_STATISTICS_ASYNC changes the trade-off, letting the current query run against slightly stale statistics while the update happens in the background for the next one, rather than making every query wait for a fresh sample first.

A multi-statement scalar or table-valued function is opaque to the optimizer. It gets treated as a black box, executed separately per row, with a flat cardinality estimate that has no relationship to what the function actually returns. An inline table-valued function is different: SQL Server expands it into the calling query the way it would a parameterized view, so the optimizer can push predicates through it and pick indexes based on the real logic inside. My rule of thumb: reach for an inline TVF over a multi-statement one whenever the logic is simple enough to express as a single SELECT, which covers more cases than people assume going in.

ROW_NUMBER assigns strictly increasing unique numbers no matter what, so tied rows get an arbitrary tiebreak based purely on the ORDER BY you gave it (add a real tiebreaker column if you need that to be deterministic). RANK gives tied rows the same rank and then skips ahead by the number of ties, so two rows tied for rank 1 push the next row to rank 3. DENSE_RANK gives tied rows the same rank without skipping, so the next distinct value gets rank 2.

SELECT employee_id, department_id, salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn,
    RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS drnk
FROM employees;

LAG and LEAD look at a row before or after the current one within a defined ORDER BY, without a self join or a correlated subquery. Before these existed (SQL Server 2012), comparing a row's value to the previous row's value meant joining the table to itself on some kind of row-offset condition, which was slow and awkward to write correctly. Now it's one window function call.

SELECT order_date, revenue,
    LAG(revenue) OVER (ORDER BY order_date) AS prev_day_revenue,
    revenue - LAG(revenue) OVER (ORDER BY order_date) AS day_over_day_change
FROM daily_sales;

SUM() as a window function with an ORDER BY inside OVER gives you a running total by default, no explicit frame clause required, because SQL Server's default frame for an ordered window is RANGE UNBOUNDED PRECEDING to CURRENT ROW. PARTITION BY resets the running total separately per group, if you need per-customer or per-region totals instead of one total across the whole table.

SELECT customer_id, order_date, amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS UNBOUNDED PRECEDING
    ) AS running_total
FROM orders;

Same DENSE_RANK pattern as finding the second-highest salary above, just parameterize the filter instead of hardcoding it to 2. Wrap the ranked CTE in a stored procedure or pass N in as a variable, and the WHERE clause in the outer query filters on that variable instead of a literal.

DECLARE @n INT = 4;

WITH RankedSalaries AS (
    SELECT employee_id, salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT employee_id, salary
FROM RankedSalaries
WHERE salary_rank = @n;

OFFSET-FETCH is a reasonable alternative if you specifically want the Nth distinct row by position rather than by rank, but it doesn't handle tied salaries the same way DENSE_RANK does, so know which behavior the interviewer actually wants before picking one.

Default READ COMMITTED uses shared locks under the hood, meaning a reader can block a writer and a writer can block a reader while data is in flight. RCSI, a database-level setting, swaps that locking behavior for row versioning: readers get a transactionally consistent snapshot pulled from tempdb's version store instead of taking locks, so readers stop blocking writers and vice versa. The cost isn't free, it pushes more load onto tempdb, and reads are now consistent "as of the start of the statement" rather than reflecting whatever committed a millisecond ago mid-scan.

ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON;

SAVE TRANSACTION marks a point inside a larger transaction that you can roll back to without undoing the whole thing. It's useful in a multi-step batch where step 3 out of 5 might fail in a recoverable way, you want to unwind just that step and try an alternate path, but you don't want to lose the work steps 1 and 2 already did. In practice I don't reach for these often, most application code is better served by keeping transactions short and handling retry logic outside the transaction entirely, but they're the right tool for a genuinely multi-stage batch process.

BEGIN TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (101, 249.00);
SAVE TRANSACTION AfterOrderInsert;

INSERT INTO order_items (order_id, sku, qty) VALUES (SCOPE_IDENTITY(), 'SKU-1', 2);
IF @@ERROR <> 0
    ROLLBACK TRANSACTION AfterOrderInsert;

COMMIT TRANSACTION;

By default, a runtime error inside a T-SQL transaction doesn't automatically roll the whole transaction back, some errors just abort the current statement and let execution continue to the next one, which can leave a transaction half-applied if nobody checks @@ERROR carefully after every single statement. SET XACT_ABORT ON changes that behavior: any runtime error rolls back the entire transaction automatically and stops execution of the batch. People put it at the top of every procedure that opens a transaction because it turns an easy-to-miss bug (forgetting an error check after one INSERT out of five) into something that just can't happen.

The system_health Extended Events session captures deadlock graphs automatically, no setup required, and it's usually the first place to look. Trace flag 1222 is the older approach, dumping the deadlock graph as XML straight into the SQL Server error log. Either way, the graph shows both transactions' statements, exactly which resources (locks on which pages or objects) each side held and was waiting on, and which one got picked as the victim.

SELECT xed.value('(event/data/value)[1]', 'varchar(max)') AS deadlock_graph
FROM (
    SELECT XEventData = CAST(target_data AS XML)
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer'
) AS Data
CROSS APPLY XEventData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed);

Both live in tempdb under the hood, so the "table variables are in memory" claim floating around isn't accurate. The real differences are statistics and scope. A #temp table gets real column statistics, so the optimizer can make a reasonable cardinality estimate against it, and you can add your own indexes to it explicitly. A @table variable had no column statistics at all until SQL Server 2019's table variable deferred compilation, which delays plan compilation until the table variable's first actual execution so the optimizer can see real row counts instead of always assuming one row. My rule: anything beyond a few hundred rows, or anything that needs an index the optimizer will genuinely seek against, gets a #temp table. Small lookup sets scoped to one batch are fine as table variables.

Tempdb is shared by the entire instance, and it backs a lot more than the #temp tables developers create on purpose: the row-versioning store behind RCSI and SNAPSHOT isolation lives there, sort and hash operations that spill to disk during a big query land there, and internal work tables SQL Server builds for things like a hash join or a big GROUP BY use it too. Because every database on the instance shares one tempdb, and every session hitting any of those features competes for the same allocation pages, contention on the shared allocation structures (GAM, SGAM, PFS pages) used to be a common bottleneck under heavy concurrent load. The standard fix is configuring multiple tempdb data files (Microsoft's current guidance is one file per logical CPU core up to eight, then evaluate further based on observed contention), which spreads that allocation work across files instead of funneling everything through one.

False, and it's one of the most confidently wrong answers I hear in interview prep. Run TRUNCATE TABLE inside an explicit transaction and it absolutely can be rolled back, because the page deallocations are still logged enough to reverse, just not as a row-by-row log entry the way DELETE is.

BEGIN TRANSACTION;
TRUNCATE TABLE staging.temp_import;
/* verify something looks wrong before committing */
ROLLBACK TRANSACTION;

The "TRUNCATE can't be rolled back" reputation comes from running it in autocommit mode with no explicit transaction open, which is a completely different problem than what TRUNCATE itself is capable of. Getting this one right, and explaining why, tends to land better than reciting "TRUNCATE is DDL, DELETE is DML" from a flashcard.

Both are special in-memory tables SQL Server populates automatically for the duration of the trigger, mirroring the structure of the table the trigger fired on. For an INSERT, only inserted has rows. For a DELETE, only deleted has rows. For an UPDATE, both are populated at once, deleted holds the pre-update values and inserted holds the post-update values, which is exactly what you need to build an audit trigger that logs old value next to new value for every changed column.

CREATE TRIGGER trg_Employees_AuditSalary
ON employees
AFTER UPDATE
AS
BEGIN
    IF UPDATE(salary)
    INSERT INTO salary_audit (employee_id, old_salary, new_salary, changed_at)
    SELECT i.employee_id, d.salary, i.salary, SYSUTCDATETIME()
    FROM inserted i
    JOIN deleted d ON i.employee_id = d.employee_id
    WHERE i.salary <> d.salary;
END;

Reporting and analytics workloads are the usual case: a star schema with a wide, deliberately denormalized fact table avoids the multi-table joins a fully normalized OLTP schema would need for every single report query, and read-heavy dashboards care a lot more about query speed than about avoiding update anomalies. A cached, precomputed summary column (an order's total stored directly on the order row instead of recalculated from order_items every time) is a smaller-scale version of the same trade. The honest cost is that denormalized data can drift out of sync with its source if the update path isn't airtight, so I only reach for it once I've actually measured that the join cost is a real problem, not because normalized schemas are inherently slow.

A surrogate key is a system-generated value with no business meaning (an IDENTITY int or a GUID), while a natural key is a real-world attribute that happens to be unique (an email address, a national ID number, an SKU). I default to surrogate keys almost every time, mostly because business meaning tends to change (an email gets updated, a SKU gets renumbered by a vendor) and a key that has to change value is expensive to change everywhere it's referenced as a foreign key. The one place I don't reflexively reach for a surrogate key is a small, genuinely immutable code table, like a two-letter country code or a status enum, where the natural key is also the most readable thing in every query that joins to it.

SQL Server compiles and caches a plan the first time a stored procedure runs, built around whatever parameter values were passed in at that moment. If the first call filters to 3 rows, the cached plan gets optimized for a tiny result set, typically nested loops and index seeks. Call the same cached plan later with a parameter matching 3 million rows and SQL Server reuses that same tiny-result-set plan instead of recompiling, dragging a nested-loop plan across millions of rows it was never shaped for. Fixes include OPTION (RECOMPILE) to force a fresh plan every call (real CPU cost each time), OPTIMIZE FOR UNKNOWN to compile against average statistical distribution instead of the first call's specific value, or splitting the logic into separate procedures for known skewed cases.

A column is defined nvarchar, and the application (or an ORM) sends a plain varchar parameter, or the reverse. SQL Server has to convert one side before it can compare them, and if the conversion has to happen on the column side rather than the literal, the index on that column becomes unseekable, forcing a full scan even though the column is indexed and the query looks perfectly reasonable. The fix is matching parameter and application data types to the actual column types, or explicitly casting the constant instead of leaving the conversion to chance. This one is genuinely hard to spot by reading the query text alone. It only shows up once you actually look at the execution plan and see an unexpected CONVERT_IMPLICIT operator.

A full backup captures the entire database at a point in time and is always the base every restore chain starts from. A differential backup captures everything that changed since the last full backup (not since the last differential), so restoring a full plus one differential only ever needs those two files regardless of how many differentials happened in between. A transaction log backup captures the log records since the last log backup and is what enables restoring forward to any specific point in time, not just to whenever the last full or differential happened to run. The restore order is always full, then the most recent differential (if any), then every log backup taken after that differential, in order, ending with WITH RECOVERY on the final one to actually bring the database online.

RESTORE DATABASE Sales FROM DISK = 'D:\Backups\Sales_Full.bak' WITH NORECOVERY;
RESTORE DATABASE Sales FROM DISK = 'D:\Backups\Sales_Diff.bak' WITH NORECOVERY;
RESTORE LOG Sales FROM DISK = 'D:\Backups\Sales_Log1.trn' WITH NORECOVERY;
RESTORE LOG Sales FROM DISK = 'D:\Backups\Sales_Log2.trn' WITH RECOVERY;

An Availability Group replicates one or more databases to up to eight secondary replicas, keeping them in sync through the same log-shipping mechanism under the hood, and can fail over automatically to a secondary if the primary goes down. Database mirroring, the older feature it effectively replaced, only ever supported a single database per mirroring session and a single secondary, no readable secondaries, and Microsoft deprecated it years ago in favor of Availability Groups. An AG's readable secondary replicas are the practical difference people care about day to day: you can point reporting workloads at a secondary and take load off the primary entirely, which mirroring never offered. See Microsoft's Always On Availability Groups overview for the full architecture.

If the subquery in a NOT IN returns even a single NULL, the entire NOT IN comparison evaluates to UNKNOWN for every outer row, not just the row that would have matched the NULL, and SQL Server discards UNKNOWN the same way it discards FALSE. The practical result is a query that looks correct, runs without error, and quietly returns an empty result set the day someone's foreign key column allows NULL and one row actually has one. NOT EXISTS doesn't have this problem, because it's checking for the existence of a matching row rather than comparing values against a list that might contain NULL. My default now is NOT EXISTS whenever there's any chance the subquery's column is nullable, which in practice is most of the time unless I wrote the schema myself.

/* Dangerous if orders.customer_id allows NULL */
SELECT customer_id FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

/* Safe regardless of NULLs */
SELECT c.customer_id FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

A Key Lookup happens when the non-clustered index used for the seek doesn't contain every column the query needs. SQL Server seeks the non-clustered index to find matching rows, then goes back to the clustered index (or heap) once per matching row to fetch the missing columns. That per-row round trip is cheap for a handful of rows and brutal for a few hundred thousand, at which point the optimizer usually gives up on the seek entirely and scans instead. The fix is almost always to widen the non-clustered index with INCLUDE so the lookup disappears and the plan becomes a plain index seek. Sometimes the honest answer is that the lookup is fine, if the query only ever touches a small number of rows, chasing a covering index isn't worth the extra write overhead on every insert and update.

Outdated or missing statistics is the first guess, and it's right often enough to check it before anything else, either by looking at STATS_DATE() on the relevant index or running sys.dm_db_stats_properties. It could also be a multi-statement table-valued function feeding the query, since those get a flat, almost-always-wrong row estimate regardless of the actual data. Or it could be parameter sniffing, where the cached plan was compiled for a very different data distribution than the one running now. I don't have one universal fix that works every time here. Sometimes it's UPDATE STATISTICS, sometimes it's OPTION (RECOMPILE), and figuring out which one applies to a specific query is honestly a big part of the job.

sys.dm_db_missing_index_details, along with the related group and group stats DMVs, tracks columns the optimizer wanted an index on but couldn't find one for, going all the way back to the last service restart. Not very much, is my honest answer to the trust question. These DMVs don't account for indexes you already have that are close but not quite covering, don't know anything about write overhead on the table, and will happily suggest three overlapping indexes that do almost the same job if three similar queries ran. I use them as a starting point for investigation, never as a create-index script to run unmodified. The actual decision needs a look at the query workload as a whole, not one query's missing-index hint in isolation.

SELECT d.statement AS table_name, d.equality_columns,
    d.inequality_columns, d.included_columns,
    s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans) AS estimated_benefit
FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
ORDER BY estimated_benefit DESC;

Wrap DENSE_RANK() in a CTE, partitioned by department and ordered by salary descending, then filter the outer query for rank = 2. You can't filter a window function's result directly in the same SELECT's WHERE clause, since WHERE evaluates before window functions do, so it has to go in a CTE or subquery. DENSE_RANK matters over ROW_NUMBER here specifically because of ties: if two employees in a department share the highest salary, ROW_NUMBER just hands you another top earner as "rank 2," while DENSE_RANK correctly skips to the actual second distinct salary value.

WITH RankedSalaries AS (
    SELECT department_id, employee_id, salary,
        DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT department_id, employee_id, salary
FROM RankedSalaries
WHERE salary_rank = 2;

SNAPSHOT isolation is a separate, opt-in level (SET TRANSACTION ISOLATION LEVEL SNAPSHOT) that gives the entire transaction one consistent view from the moment it started, not per-statement the way RCSI works. That matters for a multi-statement reporting transaction that genuinely needs every read across several queries to reflect the exact same point in time. The trade-off is real: write-write conflicts under SNAPSHOT isolation throw error 3960 and abort the transaction outright, so your application needs retry logic it might not otherwise have needed. I'd reach for RCSI first almost every time I've had the choice. Full SNAPSHOT isolation earns its complexity less often than people assume walking into this question.

SQL Server escalates row and page locks up to a single table-level lock once a statement holds roughly 5,000 locks on one table or index in a single go, mostly to keep lock-manager memory from ballooning. Once that escalation happens, any other session needing a lock on that table blocks, even for rows nowhere near the ones the original statement actually touched. The usual fix is batching large UPDATE or DELETE operations into smaller chunks so no single statement ever gets close to that threshold. ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE) is available as a targeted override, but it's not a free fix, it trades broad blocking for higher sustained lock-manager memory use, and I'd only reach for it after batching the statement first didn't solve the actual problem. See Microsoft's transaction locking and row versioning guide for the full escalation rules.

Every trigger on a table fires for every qualifying statement, silently, with no line of code at the call site hinting that extra logic is about to run. Stack three AFTER triggers on the same table and a single UPDATE can cascade into a chain of side effects that's genuinely hard to reason about from reading the original statement alone, and debugging usually means stepping through each trigger by hand to find where something unexpected happened. Nested triggers (one trigger's DML firing another table's trigger) make this worse and can hit SQL Server's nesting-level limit or, if RECURSIVE_TRIGGERS is on, actually loop. My rule of thumb: one trigger per table per operation, keep the logic inside it boring and obvious, and if the logic needs to branch into several different concerns, that's usually a sign the work belongs in application code or a scheduled job instead.

A cursor processes one row per iteration, and each FETCH is its own round trip through the query engine, with per-row overhead (lock acquisition, log activity, sometimes tempdb work) multiplied by however many rows exist. A set-based UPDATE lets the optimizer plan the whole operation as one batch, taking full advantage of index seeks and sequential I/O, with one coordinated set of locks and log writes covering the entire operation instead of thousands of tiny separate ones. There are legitimate uses for cursors, admin scripts and a few genuine ETL edge cases come to mind, but defaulting to row-by-row processing for ordinary business logic is close to the single most common performance mistake I see in T-SQL written by people coming from a procedural-first background.

This only works at all if the database is in FULL (or BULK_LOGGED, with caveats) recovery model and log backups have actually been running, so it's worth stating that precondition before diving into syntax. Restore the last full backup WITH NORECOVERY, then the most recent differential if one exists, then each log backup in order, and on the log backup that covers the moment the bad DELETE happened, use STOPAT with a timestamp a moment before the DELETE instead of restoring the whole log file. The last statement needs WITH RECOVERY to actually bring the database online afterward, since every prior step leaves it in a restoring state on purpose.

RESTORE DATABASE Sales FROM DISK = 'D:\Backups\Sales_Full.bak' WITH NORECOVERY;
RESTORE LOG Sales FROM DISK = 'D:\Backups\Sales_Log_Incident.trn'
    WITH STOPAT = '2026-07-18T14:32:00', RECOVERY;

Synchronous commit waits for the secondary replica to confirm the transaction log record was hardened to disk before the primary reports COMMIT back to the application, guaranteeing zero data loss on failover at the cost of added commit latency, since every write now waits on a network round trip to the secondary. Asynchronous commit doesn't wait, the primary commits as soon as its own log write finishes, so latency looks like a standalone instance, but a failover can lose whatever transactions hadn't made it to the secondary yet. I've only seen synchronous commit make sense within a single datacenter or availability zone where the network round trip is a couple of milliseconds. Cross-region replicas, where the round trip is 30 to 80 milliseconds depending on distance, are asynchronous in practically every setup I've come across, because synchronous commit at that latency would make every write noticeably slower for zero acceptable business reason.