SQL Server interview questions cover three layers: T-SQL you can write on a whiteboard, how the engine stores and finds data (indexes, statistics, execution plans), and how you keep a database fast and recoverable (isolation, locking, backups, high availability). The current release is SQL Server 2025 (17.x); Microsoft's build list shows cumulative update 11 released on November 12, 2026, and its what's-new page lists a native JSON type, a vector type, regular expression functions and optimized locking.
Below are 30 questions by level with concise answers, five scenarios and how developer and DBA interviews test this. For portable SQL see SQL interview questions; for operations-heavy roles see database administrator interview questions.
SQL Server interview questions: fundamentals (1–10)
- Clustered versus nonclustered index? A clustered index is the table itself, with rows stored in key order, so there is one per table. A nonclustered index is a separate B-tree of key columns plus a row locator (the clustered key, or a RID on a heap). A table can have up to 999 nonclustered indexes.
- What is a heap? A table without a clustered index. Rows are found through row identifiers, updates can leave forwarded records, and range scans are slower. Most OLTP tables should have a narrow, unique, ever-increasing clustered key.
- DELETE versus TRUNCATE?
DELETElogs each row, fires triggers and can take aWHERE.TRUNCATEdeallocates pages with minimal logging, resets identity and cannot have aWHEREor run against a table referenced by a foreign key. Both can be rolled back inside a transaction. - @@IDENTITY, SCOPE_IDENTITY() or IDENT_CURRENT?
SCOPE_IDENTITY()returns the last identity in your scope and session, which is almost always what you want.@@IDENTITYcan return a value generated by a trigger;IDENT_CURRENT('t')returns the last value for a table in any session. - WHERE versus HAVING?
WHEREfilters rows before grouping;HAVINGfilters groups after aggregation, such asHAVING COUNT(*) > 5. - ROW_NUMBER, RANK or DENSE_RANK? For values 100, 100, 90:
ROW_NUMBERgives 1, 2, 3;RANKgives 1, 1, 3;DENSE_RANKgives 1, 1, 2. Classic use:ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)to find each department's top earner. - What is a CTE, and what limits a recursive one? A named result set scoped to one statement, used for readability and recursion such as org charts. Recursion stops at 100 levels by default; change it with
OPTION (MAXRECURSION n), where 0 means no limit. - Temp table or table variable? Temp tables (
#t) live in tempdb, have statistics and can be indexed; table variables (@t) have no statistics. Table variable deferred compilation, added in SQL Server 2019, improves their row estimates, but large intermediate sets still usually belong in temp tables. - What are the recovery models? Simple (log truncates at checkpoint, no point-in-time restore), Full (log backups enable point-in-time restore) and Bulk-logged (minimal logging for bulk operations, with limits on point-in-time restore during them).
- What are the backup types? Full, differential (changes since the last full) and transaction log backups. A restore chain is the last full, the latest differential, then every log backup since; take a tail-log backup before restoring a damaged database.
Intermediate SQL Server questions (11–20)
- What is the default isolation level?
READ COMMITTEDusing locks. WithREAD_COMMITTED_SNAPSHOTon (RCSI), readers see the last committed row version from tempdb instead of waiting on writers.SNAPSHOTgives transaction-level consistency;SERIALIZABLEadds range locks. - Why is NOLOCK risky?
WITH (NOLOCK)meansREAD UNCOMMITTED: dirty reads, plus rows read twice or missed during page splits. If the goal is readers not blocking writers, RCSI is the correct fix. - What makes a predicate sargable? One the optimizer can turn into an index seek.
WHERE YEAR(OrderDate) = 2026orWHERE col + 1 = 5force scans; rewrite as a range such asOrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'. Implicit conversions between types also break seeks. - What is a key lookup and how do you remove it? After a nonclustered index seek, the engine fetches missing columns from the clustered index row by row. Remove it by making the index covering, adding columns with
INCLUDE, if the query matters enough to pay the write cost. - How do you read an execution plan? Use the actual plan. Look at the most expensive operators, estimated versus actual row counts (a large gap means a statistics or parameter problem), scans where you expected seeks, key lookups, sorts and hash spills, and warnings such as implicit conversions.
- What is parameter sniffing? The first execution's parameter values shape a cached plan that later values reuse, which hurts when data is skewed. Options:
OPTION (RECOMPILE),OPTIMIZE FOR, rewriting into separate procedures, or SQL Server 2022's Parameter Sensitive Plan optimization at compatibility level 160 or higher. - What is Query Store? A per-database record of queries, plans and runtime statistics over time. Per Microsoft's docs it is on by default (READ_WRITE) for new databases starting with SQL Server 2022, and it lets you find regressions and force a known-good plan.
- How does SQL Server handle deadlocks? The lock monitor detects a cycle and kills one session as the victim with error 1205, usually the one cheapest to roll back unless
DEADLOCK_PRIORITYsays otherwise. Fix with consistent access order, shorter transactions, better indexes, RCSI and retry logic; capture the graph from thesystem_healthExtended Events session. - Statistics: what are they and when do they go stale? Histograms of column value distribution that the optimizer uses for row estimates. They update automatically after enough modifications, but large or skewed tables can drift; update them after big loads, sometimes with
FULLSCAN. - Index rebuild or reorganize? Reorganize is online, lightweight and defragments leaf pages; rebuild recreates the index and its statistics. Many teams now act on measured page density and query impact rather than fixed fragmentation thresholds.
Experienced and senior SQL Server questions (21–30)
- Always On availability group or failover cluster instance? An availability group replicates databases to secondaries with synchronous or asynchronous commit, can have readable secondaries and needs no shared storage. A failover cluster instance protects the whole instance on shared storage, with one copy of the data. They can be combined.
- Synchronous versus asynchronous commit? Synchronous commit waits for the secondary to harden the log, so there is no data loss on failover but more latency; asynchronous does not wait, so it is fast but can lose recent transactions. Use synchronous within a data center and asynchronous across regions.
- What lives in tempdb, and why does it get contended? Temp tables, table variables, sorts and hash spills, version store for RCSI and snapshot isolation, and online index operations. Allocation contention is eased with multiple equally sized data files; SQL Server 2025 adds tempdb space resource governance.
- What is optimized locking? A SQL Server 2025 feature (also in Azure SQL) that cuts lock memory and blocking with transaction ID locking and lock after qualification. Per Microsoft's docs it requires accelerated database recovery, and lock after qualification also requires RCSI.
- What did SQL Server 2025 add for developers? A native
jsondata type, avectortype with functions such asVECTOR_DISTANCE, regular expression functions, and fuzzy string matching. Vector indexes were still marked preview on Microsoft's what's-new page when we checked. - Columnstore or rowstore? Columnstore stores data by column in compressed segments with batch-mode execution, ideal for scans and aggregations on large fact tables. Rowstore B-trees suit point lookups and small range reads. Nonclustered columnstore on an OLTP table supports real-time analytics.
- How do you handle errors in T-SQL? Wrap work in
BEGIN TRY ... BEGIN CATCH, checkXACT_STATE(), roll back and rethrow withTHROW.SET XACT_ABORT ONmakes most runtime errors roll back the whole transaction. - How do you page large result sets?
ORDER BY ... OFFSET n ROWS FETCH NEXT m ROWS ONLYis simple but slows on deep pages. Keyset pagination (WHERE Id > @lastId ORDER BY Id) stays fast because it seeks. - How do you find the most expensive queries? Query Store's top resource-consuming queries, or
sys.dm_exec_query_statsjoined tosys.dm_exec_sql_textandsys.dm_exec_query_plan, ordered by CPU, reads or duration. Check wait statistics to see what the server is waiting on overall. - How do you approach a migration to a newer version? Inventory features in use, test at the current compatibility level first, capture a Query Store baseline, then raise the compatibility level and compare plans, forcing old plans where something regresses. Rehearse the cutover and the rollback.
SQL Server scenario and troubleshooting questions
A report that took 2 seconds now takes 2 minutes. Check Query Store for a plan change. If the plan regressed, force the old plan while you fix the cause: stale statistics, parameter sniffing on skewed data, or a missing index after a schema change.
Users report blocking at 9 a.m. every day. Find the head blocker with sys.dm_exec_requests and sys.dm_tran_locks. Typical causes: a batch job in one large transaction, lock escalation on a hot table, or missing indexes that widen scans. Fixes: batch the job, add the index, consider RCSI.
The transaction log fills the disk. Check log_reuse_wait_desc in sys.databases. In Full recovery with no log backups, start them; other causes are a long-running open transaction, an unsynchronized availability group replica or replication.
A query is slow only from the application, not from SSMS. Different SET options (often ARITHABORT) give the app a different cached plan, frequently one sniffed for unrepresentative values. Compare the plans and fix the sniffing itself.
Deadlocks started after a release. Pull the deadlock graphs from system_health, find the two statements and the resources they lock in opposite order, then align access order or add an index so each statement touches fewer rows.
How SQL Server interviews test this
| Format | What it usually looks like | How to prepare |
|---|---|---|
| T-SQL exercise | Write joins, window functions, a running total or top-N per group, often live | Practice ROW_NUMBER, LAG and LEAD, and conditional aggregation |
| Performance discussion | Here is a slow query and its plan: what would you change? | Read actual plans; explain seeks, scans, lookups and estimates |
| DBA or operations | Backups and restore chains, availability groups, tempdb, patching | Walk through a restore and a failover out loud |
| Scenario | Blocking, deadlocks, a full log or a sudden regression | Use the five scenarios above as rehearsal prompts |
Developer roles lean on T-SQL and plans; DBA roles lean on recovery and availability. If the shop uses .NET, expect overlap with Entity Framework interview questions, and PostgreSQL interview questions covers the main open-source alternative.
Where CoPilot Interview fits
CoPilot Interview is an AI interview assistant. Its free AI mock interview builds SQL Server, T-SQL and DBA questions from your résumé and the job description, reads them aloud and scores each spoken answer out of 10, which is a fast way to find the answers above that you know but cannot yet explain cleanly under pressure. The browser demo has 145 practice questions in 10 tracks, scores typed answers out of 10 and needs no signup. The free plan gives 15 minutes a day of live help in sessions of up to 15 minutes, with AI mock interviews included. Paid plans are Standard at $24.99 a month and Pro at $39.99 a month, in the desktop app for Windows 10/11 and macOS 12 or later.
Use it to practice. Online assessments, proctored tests, take-home tasks with rules and AI-led rounds forbid outside help, so prepare for those instead of bringing a tool. For any live interview, follow your employer's rules.
Disclosure: CoPilot Interview is our product, so weigh what we say about it accordingly.
Frequently asked questions
What are the most asked SQL Server interview questions?
Clustered versus nonclustered indexes, isolation levels and NOLOCK, execution plans, parameter sniffing, deadlocks, DELETE versus TRUNCATE, window functions, recovery models and backups, and Always On availability groups.
What is the latest version of SQL Server?
SQL Server 2025 (17.x). Microsoft's build list showed cumulative update 11, released November 12, 2026, as the newest build when we checked on November 17, 2026.
Is NOLOCK ever acceptable?
Only where wrong or duplicated rows are tolerable, such as a rough dashboard. For readers that should not block writers, read committed snapshot isolation is the safer answer.
How do I prepare for a SQL Server DBA interview?
Practice a full, differential and log restore to a point in time, read actual execution plans, reproduce a deadlock and read its graph, and be ready to explain availability groups and tempdb.
Do SQL Server interviews include live coding?
Commonly, yes: candidates report T-SQL exercises such as top-N per group, running totals or deduplication, written in a shared editor or on a whiteboard.
Practice this interview before the real one
The AI mock interview builds questions from your resume and the job description, reads them aloud and scores each spoken answer out of 10. Included on the free plan.
Start a mock interview