skip to content

SQL Server

SQL Server is the Microsoft stack's relational engine, with its own T-SQL dialect, clustered index model, row-versioning isolation and Always On availability.

on this pageshow

explore

questions

page 1 of 2

In SQL Server, what do full, differential and transaction log backups each contain, and in what order do you restore them?

level: juniorimportance: must knowfreq 70%

answer

  1. three backup types, one chain
  2. differential counts from the last full
  3. log backups need FULL or BULK_LOGGED
  4. NORECOVERY on every step but the last

basics

~20 s

A full backup copies the whole database plus enough log to recover it; a differential, extents changed since the last full; a log backup, log since the previous one. Restore the full, the newest differential, then later log backups in order.

solid answer

~50 s

`BACKUP DATABASE` makes a **full** backup: every data extent plus the part of the transaction log needed to bring that copy to a consistent state as of the moment it finished. `BACKUP DATABASE ... WITH DIFFERENTIAL` copies only the extents marked in the differential bitmap since the last conventional full backup, its *differential base*, so each differential grows until the next full. `BACKUP LOG` copies the log records written since the previous log backup and is possible only under the FULL or BULK_LOGGED recovery model. To restore, apply the full backup `WITH NORECOVERY`, then only the most recent differential `WITH NORECOVERY`, then every later log backup in sequence, and finish `WITH RECOVERY`, which rolls back uncommitted work and opens the database. A `COPY_ONLY` full backup leaves the differential base alone, so it is safe for an ad hoc copy.

code

sql · 8 lines
sql
-- Sunday: full backup, the differential base
BACKUP DATABASE Sales TO DISK = N'D:\bak\Sales_full.bak' WITH CHECKSUM;

-- Each night: differential since that full backup
BACKUP DATABASE Sales TO DISK = N'D:\bak\Sales_diff.bak' WITH DIFFERENTIAL, CHECKSUM;

-- Every 15 minutes: log backup (FULL recovery model)
BACKUP LOG Sales TO DISK = N'D:\bak\Sales_log_1015.trn' WITH CHECKSUM;

go deeper

for a junior

Recall the three statements, BACKUP DATABASE, BACKUP DATABASE WITH DIFFERENTIAL and BACKUP LOG, and that a restore goes full, newest differential, then log backups in order.

for a middle

Explain the differential base and bitmap, why only the newest differential is restored, and why the log chain survives a new full backup.

for a senior

Show that you read msdb backup history to prove the chain is complete, use CHECKSUM and COPY_ONLY on purpose, and rehearse restores rather than trusting job success.

for a principal

Treat the full, differential and log cadence as a restore-time and storage budget agreed with the business, and make someone accountable for testing restores.

## The three backup types SQL Server has three backup types that matter for an ordinary user database, and each is a different statement or option: | Backup | Statement | Contains | Recovery models | |---|---|---|---| | **Full** | `BACKUP DATABASE` | Every data extent plus enough transaction log to make the copy consistent | All three | | **Differential** | `BACKUP DATABASE ... WITH DIFFERENTIAL` | Extents changed since the differential base | All three | | **Transaction log** | `BACKUP LOG` | Log records since the previous log backup | FULL and BULK_LOGGED only | A **full backup** is taken while the database stays online: inserts, updates and deletes keep running. Because pages are read over a period of time, the backup also captures the log written while it ran, and a restore uses that log to bring the copy to the state of the database at the moment the backup *finished*, minus any uncommitted transactions. ## How a differential knows what changed An **extent** is a group of eight contiguous 8 KB pages. SQL Server keeps a **differential bitmap** with one bit per extent; any change to an extent sets its bit. A differential backup reads only the extents whose bit is set, which is why it is usually much smaller and faster than a full backup. - The full backup the bitmap counts from is the **differential base**. Only the next conventional full backup clears the bitmap. - Every differential therefore contains everything the previous differential did, plus newer changes. Differentials are **cumulative**, not incremental. - A full backup taken `WITH COPY_ONLY` does not touch the bitmap and cannot serve as a base, so an ad hoc copy for a developer does not silently change what Tuesday's differential means. - Because differentials grow over time, a regular full backup (weekly is common) keeps them, and the restore time, under control. ## The log chain Under the FULL and BULK_LOGGED recovery models, each log backup starts where the previous one ended. That unbroken sequence is the **log chain**, and it is independent of data backups: a log backup taken after a new full backup still continues the chain that the older full backup started. Two consequences follow: - A full or differential backup does not truncate the log; only a log backup frees log space in FULL recovery. - If the newest full backup turns out to be unreadable, you can restore the previous one and replay every log backup since, provided none is missing. The SIMPLE recovery model has no log backups at all, so a database in SIMPLE can only be restored to the end of a full or differential backup. ## The restore sequence A restore that uses more than one backup must keep the database in the RESTORING state until the last step. `RESTORE` defaults to `WITH RECOVERY`, which rolls back uncommitted transactions and opens the database, after which no further backups can be applied. So the sequence is: 1. Restore the most recent full backup `WITH NORECOVERY`. 2. Restore only the most recent differential based on that full backup, also `WITH NORECOVERY`. Older differentials are skipped because the newest already contains their changes. 3. Restore every log backup taken after that differential, in order, `WITH NORECOVERY`. 4. Recover the database with `RESTORE DATABASE ... WITH RECOVERY`, or put `WITH RECOVERY` on the final log restore. Restoring over a live database adds one step before all of this, a backup of the tail of the log, so that the commits since the last log backup are not lost. ## Reading backup history SQL Server records every backup in `msdb`. The `msdb.dbo.backupset` table holds a `type` column (`D` for a database backup, `I` for a differential, `L` for a log backup), `is_copy_only`, and `first_lsn` and `last_lsn`, which let you confirm that each log backup begins where the previous one ended. That history is the first thing to read before planning a restore. ## Common mistakes - Treating differentials as incremental and restoring all of them in turn. - Forgetting `NORECOVERY` on the first restore, which recovers the database early and forces you to start over. - Expecting a full backup to keep the log small under FULL recovery. - Taking an ad hoc full backup without `COPY_ONLY` in the middle of the week, so later differentials are based on a file that may not be kept with the rest.

  • In SQL Server, does taking a new full backup break the log chain for restores that start from an older full backup?
    No. The log chain is independent of data backups: log backups taken after a newer full backup still continue the chain the older one began. You can restore an older full backup and then every later log backup, which is how you recover when the newest full backup is unreadable. A missing log backup or a switch to the SIMPLE recovery model, by contrast, does break it.
  • Why does each SQL Server differential backup get bigger through the week?
    A differential copies every extent whose bit is set in the differential bitmap, and only the next conventional full backup clears that bitmap. Changed extents keep accumulating, so Friday's differential holds everything Monday's did plus later changes. A regular full backup resets the base and keeps differentials, and the restore time, small.

A full backup is a photograph of the whole house; a differential is a fresh photo of every room that has changed since that photograph; the log backups are a diary of each change in order. To rebuild, you hang the original photograph, swap in the latest set of room photos, then replay the diary from there.

  • A differential holds only the changes since the previous differential.
  • A full backup truncates the transaction log under the FULL recovery model.
  • You can take log backups of a database in the SIMPLE recovery model.
  • Every differential since the full must be restored in order, like incrementals.
  • Restore the full backup WITH RECOVERY first, then apply log backups afterwards.
open as a page

In SQL Server, what is the difference between a clustered and a nonclustered index, and how many of each can a table have?

level: juniorimportance: must knowfreq 80%

basics

~20 s

A SQL Server clustered index is the table itself: its leaf pages hold whole rows in key order, so a table has at most one. Nonclustered indexes are separate B+ trees pointing back to those rows, up to 999 per table.

open as a page

In SQL Server, how does a stored procedure hand values back through OUTPUT parameters and a RETURN code, and what must the caller write?

level: juniorimportance: must knowfreq 62%

basics

~20 s

A SQL Server procedure marks a parameter OUTPUT, and the caller must also pass a variable marked OUTPUT or the value is silently lost. RETURN sends back only an integer status, read with EXEC @rc = procedure.

open as a page

In SQL Server, how do TOP (n) and OFFSET ... FETCH differ, and what does each require of ORDER BY?

level: juniorimportance: must knowfreq 70%

basics

~20 s

TOP (n) is T-SQL's own limiter: ORDER BY is optional, but without it the rows chosen are undefined, and only TOP offers WITH TIES and PERCENT. OFFSET ... FETCH is part of ORDER BY, so it needs one, and it is the paging tool.

open as a page

What is the difference between a SQL Server failover cluster instance and an Always On availability group, and what does each one fail over?

level: middleimportance: must knowfreq 68%

basics

~20 s

A failover cluster instance is one SQL Server instance on shared storage that restarts on another node: whole-instance protection, one copy of data. An availability group keeps separate copies of chosen databases on several instances and fails over just those.

open as a page

Why does a SQL Server database's transaction log keep growing under the FULL recovery model, and how do SIMPLE and BULK_LOGGED change that?

level: middleimportance: must knowfreq 72%

basics

~20 s

Under FULL, inactive log is reused only after a log backup, so without scheduled BACKUP LOG the log grows until the disk fills. SIMPLE truncates at checkpoints but allows no log backups; BULK_LOGGED minimally logs bulk work yet still needs log backups.

open as a page

In SQL Server, how do you capture an estimated versus an actual plan, and get runtime plan data for a production query?

level: middleimportance: must knowfreq 62%

basics

~10 s

SET SHOWPLAN_XML ON compiles without executing and returns the estimated plan; SET STATISTICS XML ON executes and adds runtime rows and warnings. Cached plans hold estimates only; runtime data comes from sys.dm_exec_query_statistics_xml or sys.dm_exec_query_plan_stats.

open as a page

In SQL Server, why does an execution plan show a Key Lookup beside an Index Seek, and how does an INCLUDE column remove it?

level: middleimportance: must knowfreq 72%

basics

~20 s

A Key Lookup means the nonclustered index lacks columns the query needs, so SQL Server fetches each matching row from the clustered index. Adding those columns with INCLUDE stores them in the index leaf, which removes the lookup.

open as a page

In SQL Server, how do ALTER INDEX REBUILD and ALTER INDEX REORGANIZE differ, and which would you run on a busy table?

level: middleimportance: must knowfreq 68%

basics

~20 s

REORGANIZE reorders and compacts only the leaf level, is always online and keeps its progress if stopped. REBUILD re-creates the whole index and refreshes its statistics, but blocks the table unless run with ONLINE = ON, an Enterprise feature.

open as a page

In SQL Server, what is the difference between running dynamic SQL with EXEC (@sql) and with sp_executesql, and why does it matter?

level: middleimportance: must knowfreq 72%

basics

~20 s

EXEC (@sql) runs a string, so values must be concatenated in as literals. sp_executesql takes a Unicode statement plus typed parameters, so the text stays constant: its plan is reused and values cannot alter the statement's structure.

open as a page

In SQL Server, how do scalar, inline table-valued and multi-statement table-valued functions differ in definition and in how the optimizer treats them?

level: middleimportance: must knowfreq 64%

basics

~20 s

A scalar function returns one value per call. An inline table-valued function is a single SELECT that SQL Server expands into the calling query like a view. A multi-statement function fills a table variable the optimizer guesses at 100 rows.

open as a page

A SQL Server report blocks checkout writes and the team proposes WITH (NOLOCK) everywhere; what can those reads return, and what should you do instead?

level: middleimportance: must knowfreq 70%

basics

~20 s

NOLOCK equals READ UNCOMMITTED for that table: a report can read changes later rolled back, miss rows during page splits, read moved rows twice, and fail with error 601. READ_COMMITTED_SNAPSHOT removes the blocking without dirty reads.

open as a page

In SQL Server, how do the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options differ in what readers see and how writers fail?

level: middleimportance: must knowfreq 72%

basics

~20 s

READ_COMMITTED_SNAPSHOT makes the default READ COMMITTED level read row versions as of each statement's start, transparently. ALLOW_SNAPSHOT_ISOLATION only permits SET TRANSACTION ISOLATION LEVEL SNAPSHOT, which reads as of transaction start and aborts with Msg 3960 on an update conflict.

open as a page

In SQL Server, how do ISNULL and COALESCE differ in result type, evaluation and nullability?

level: middleimportance: must knowfreq 68%

basics

~20 s

ISNULL takes two arguments and returns the first one's type, truncating a longer replacement; COALESCE takes many and returns the highest-precedence type. COALESCE is rewritten as CASE, so inputs can be evaluated twice, and its result counts as nullable.

open as a page

In SQL Server, how does a table variable differ from a #temp table in statistics, scope, rollback and plan quality?

level: middleimportance: must knowfreq 64%

basics

~20 s

Both live in tempdb. A table variable has no statistics, is scoped to its batch, ignores ROLLBACK and gets no parallel plan for modifications; a #temp table has statistics and full indexing and can last the whole session, at the cost of recompiles.

open as a page

In SQL Server, a stored procedure is fast for one customer and slow for another; how do you confirm parameter sniffing and choose a fix?

level: seniorimportance: must knowfreq 74%

basics

~20 s

Compare the value the cached plan was compiled for with the slow customer's value and its actual rows. Then fix per statement: OPTION (RECOMPILE), OPTIMIZE FOR, a forced Query Store plan or hint, or PSP optimization from SQL Server 2022.

open as a page

In SQL Server, how do you use sys.dm_os_wait_stats to diagnose a slowdown, and what do PAGEIOLATCH_SH, CXPACKET and LCK_M_X waits point to?

level: seniorimportance: must knowfreq 60%

basics

~10 s

sys.dm_os_wait_stats totals completed waits per wait type since startup, so diff two snapshots and drop idle waits. PAGEIOLATCH_SH means reading pages from disk, CXPACKET parallel-exchange waiting, LCK_M_X waiting for an exclusive lock.

open as a page

An orders table in SQL Server is clustered on a uniqueidentifier key defaulted to NEWID() and fragments within hours of each rebuild; how do you diagnose and fix it?

level: seniorimportance: must knowfreq 62%

basics

~20 s

NEWID() values are random, so SQL Server inserts land mid-index and split full pages, leaving half-empty, out-of-order pages. Confirm it with sys.dm_db_index_physical_stats, then cluster on a bigint IDENTITY and keep the GUID as a nonclustered primary key.

open as a page

In SQL Server, how should a TRY...CATCH block roll back a failed transaction, and why pair THROW with XACT_ABORT and XACT_STATE?

level: seniorimportance: must knowfreq 58%

basics

~20 s

Run SET XACT_ABORT ON, open the transaction inside BEGIN TRY, and in BEGIN CATCH roll back when XACT_STATE() <> 0, then THROW; to re-raise the original error. XACT_ABORT stops half-committed work, and XACT_STATE detects a doomed transaction.

open as a page

In SQL Server, when does AUTO_UPDATE_STATISTICS actually refresh a statistics object, and why can a large table's estimates still go stale?

level: middleimportance: should knowfreq 52%

basics

~20 s

SQL Server refreshes a statistic only when a query using it compiles after modifications pass a threshold: 500 + 20% of rows below compatibility level 130, and the smaller of that and SQRT(1000 * rows) from 130.

open as a page

In SQL Server, how do you keep a nullable column unique among its non-NULL values, and what limits the filtered index that does it?

level: middleimportance: should knowfreq 45%

basics

~20 s

Create a filtered unique index, CREATE UNIQUE NONCLUSTERED INDEX ... WHERE col IS NOT NULL. Its filter allows only simple comparisons joined by AND, it can't be clustered or use IGNORE_DUP_KEY, and writing sessions need the required SET options.

open as a page

In SQL Server, how do CREATE PROCEDURE ... WITH RECOMPILE, OPTION (RECOMPILE) on one statement, and sp_recompile differ in what they recompile and when?

level: middleimportance: should knowfreq 55%

basics

~20 s

WITH RECOMPILE on a procedure stops SQL Server caching any plan for it, so every call recompiles the whole procedure. OPTION (RECOMPILE) recompiles one statement each run. sp_recompile only marks a module for recompilation on its next execution.

open as a page

In SQL Server, why do DATEDIFF and EOMONTH-based month filters give wrong answers, and how do you write them correctly?

level: middleimportance: should knowfreq 50%

basics

~20 s

DATEDIFF counts datepart boundaries crossed, so one second across New Year is a year. EOMONTH returns a date, so <= EOMONTH(...) drops the last day's later times. Filter months as half-open ranges: >= first day and < DATEADD(month, 1, first day).

open as a page

In a SQL Server availability group, why do commits slow down and HADR_SYNC_COMMIT top the waits after adding a synchronous-commit secondary in a second datacenter, and what can you do?

level: seniorimportance: should knowfreq 52%

basics

~20 s

Synchronous commit returns only after the secondary hardens the log, so each commit pays the inter-site round trip and a remote log flush. Make the far replica asynchronous with a nearby synchronous partner, or speed up the link and log storage.

open as a page

The data-file disk of a SQL Server database in FULL recovery has failed but its log disk survived; how do you restore it without losing committed transactions?

level: seniorimportance: should knowfreq 50%

basics

~20 s

First back up the log tail with BACKUP LOG ... WITH NO_TRUNCATE. Then restore the last full, newest differential and later log backups WITH NORECOVERY, apply the tail and recover. Skipping the tail loses every commit since the last log backup.

open as a page

In SQL Server, sessions creating temp tables pile up on PAGELATCH_UP waits in tempdb; what is contending and how do you fix it?

level: seniorimportance: should knowfreq 42%

basics

~10 s

Sessions queue on latches for tempdb's allocation pages (PFS, GAM, SGAM) because there are too few data files. Add equal-size files, one per logical processor up to eight, then more in fours.

open as a page

A SQL Server table with a clustered columnstore index receives a steady trickle of single-row INSERTs and its scans slow down; what is happening, and how do you fix it?

level: seniorimportance: should knowfreq 28%

basics

~20 s

Small inserts go to the delta store, uncompressed B-tree rowgroups that every scan must read alongside the compressed columnstore. Load in batches of at least 102,400 rows, or compress them now with ALTER INDEX ... REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON).

open as a page

A SQL Server search procedure concatenates optional filter values and a sort column into a string run with EXEC; how do you rewrite it safely without wrecking plan reuse?

level: seniorimportance: should knowfreq 52%

basics

~20 s

Build only the predicates that were supplied, each referencing a parameter, and run the string with sp_executesql passing every value as a typed parameter. Pick the sort column from an allowlist and wrap it in QUOTENAME.

open as a page

After a SQL Server database moves to compatibility level 150, one report calling a scalar UDF per row speeds up and another stays slow; how do you explain and check that?

level: seniorimportance: should knowfreq 48%

basics

~20 s

Compatibility level 150 turns on scalar UDF inlining, which rewrites eligible functions into the calling query. A function using GETDATE() or table variables, or a call from ORDER BY, is not inlined and still runs once per row, serially.

open as a page

In SQL Server, where do you find the deadlock graph behind an error 1205 that already happened, and how do you read it to find the cause?

level: seniorimportance: should knowfreq 58%

basics

~20 s

The default system_health Extended Events session records every deadlock as an xml_deadlock_report event. Its graph names the victim, each process's statement and, in resource-list, who owns and who waits for each lock, so you can trace the cycle.

open as a page

showing 1–30 of 36