Top 60 MS SQL Server Interview Questions (2026)

MS SQL Server is Microsoft's relational database management system. It stores data in tables with defined relationships and lets you query and modify that data through T-SQL, Microsoft's SQL dialect.

It's more than a query engine: the product bundles services for reporting, integration, and analytics, plus tools like SQL Server Management Studio. Editions run from the free Express and Developer builds up to Enterprise.

T-SQL (Transact-SQL) is Microsoft's extension of standard SQL. It keeps the standard SELECT, INSERT, UPDATE, and DELETE, then adds procedural features: variables, IF and WHILE control flow, error handling, and stored procedures.

So standard SQL is the shared query language, and T-SQL is what you actually write in SQL Server. Functions like ISNULL, TOP, and GETDATE are T-SQL specifics you won't find spelled the same way elsewhere.

A primary key uniquely identifies each row and cannot contain NULL; a table has exactly one. A unique key also enforces uniqueness but allows a single NULL value, and a table can have several.

By default SQL Server backs a primary key with a clustered index and a unique constraint with a nonclustered index, though you can change that. Both prevent duplicate values in the columns they cover.

A foreign key is a column, or set of columns, that references the primary key of another table. It enforces referential integrity: you can't insert a value that has no matching parent row, and you can't orphan children by deleting a referenced parent.

It's how relationships between tables get enforced by the database instead of relying on application code to keep data consistent.

INNER JOIN returns only rows matching in both tables. LEFT JOIN keeps all left-table rows plus matches. RIGHT JOIN keeps all right-table rows plus matches. FULL OUTER JOIN keeps unmatched rows from both sides. CROSS JOIN produces every combination.

The unmatched side fills with NULLs in outer joins, which is exactly how you find rows that have no match: a LEFT JOIN with a WHERE that checks the right side IS NULL.

WHERE filters individual rows before grouping happens. HAVING filters groups after GROUP BY has aggregated them, so aggregate conditions like COUNT(*) > 5 belong in HAVING, not WHERE.

You can use both in one query: WHERE narrows the raw rows, GROUP BY buckets them, and HAVING keeps only the buckets you want.

DELETE removes rows one at a time, accepts a WHERE clause, logs each deletion, and can be rolled back. TRUNCATE removes all rows by deallocating pages, is minimally logged, resets the identity seed, and is much faster but can't be filtered. DROP removes the entire table, structure included.

So DELETE is a data operation you can target, TRUNCATE is a fast full wipe, and DROP is a schema operation.

CHAR is fixed length and pads with spaces, so CHAR(10) always uses ten characters. VARCHAR is variable length and stores only what you put in, up to the declared max. NVARCHAR is like VARCHAR but stores Unicode, using two bytes per character to hold any language.

Reach for VARCHAR when values vary in length, CHAR only for genuinely fixed-width codes, and NVARCHAR whenever the text can contain non-Latin characters.

An index is a data structure that speeds up row lookups, much like a book's index points you to a page. Instead of scanning every row, SQL Server walks the index to find matching rows fast. Most SQL Server indexes are B-tree structures.

The trade-off: indexes speed up reads but slow down writes, because every insert, update, and delete has to maintain the index too. They also use disk space.

A clustered index sorts and stores the table's rows in the index order, so the table IS the index. A table can have only one, since rows can be physically ordered just one way. A nonclustered index is a separate structure that points back to the rows, and a table can have many.

The primary key gets a clustered index by default. Nonclustered indexes carry the columns you search on plus a pointer to the actual row.

Normalization is organizing tables to reduce redundancy and prevent update anomalies. You split data into related tables so each fact lives in one place, then link them with keys. The common forms are 1NF (atomic values), 2NF (no partial dependency on part of a composite key), and 3NF (no dependency on non-key columns).

The payoff is consistency: change a customer's address once instead of in every order row. The cost is more joins at read time.

DDL (Data Definition Language) defines structure: CREATE, ALTER, DROP, TRUNCATE. DML (Data Manipulation Language) changes data: SELECT, INSERT, UPDATE, DELETE. DCL (Data Control Language) manages permissions: GRANT and REVOKE.

There's also TCL (Transaction Control Language): COMMIT, ROLLBACK, and SAVE TRANSACTION, which control transaction boundaries.

NULL means unknown or missing, not zero or empty string. Any comparison with NULL using = or <> returns UNKNOWN, so those rows never match a WHERE filter. To test for NULL you must use IS NULL or IS NOT NULL.

ISNULL(col, replacement) substitutes a value when a column is NULL, and COALESCE returns the first non-NULL from a list.

Aggregate functions collapse many rows into one value: COUNT, SUM, AVG, MIN, and MAX. GROUP BY splits rows into buckets by one or more columns, then applies the aggregate to each bucket.

Every column in the SELECT that isn't inside an aggregate must appear in the GROUP BY, otherwise SQL Server raises an error.

DISTINCT removes duplicate rows from a result set, keeping one copy of each unique combination of the selected columns. It applies to the whole row the SELECT returns, not a single column.

It costs a sort or hash to find duplicates, so on large results it isn't free. Often a GROUP BY or a better join condition removes duplicates more cheaply.

UNION combines the results of two queries and removes duplicate rows, which means it does a sort or hash to dedupe. UNION ALL combines them and keeps every row, duplicates included, so it's faster.

Use UNION ALL by default and only reach for UNION when you actually need duplicates gone. Both require the same number of columns with compatible types.

TOP limits how many rows a query returns. TOP (10) returns the first ten rows, and with an ORDER BY it returns the ten highest or lowest by that order. TOP (5) PERCENT returns a percentage of the result.

Without ORDER BY, which rows you get is arbitrary, so pair TOP with ORDER BY whenever the ranking matters. WITH TIES includes rows that tie with the last one.

BETWEEN tests a range and is inclusive of both bounds. IN tests membership in a list of values. LIKE matches string patterns with wildcards: % for any run of characters and _ for a single character.

BETWEEN '2026-01-01' AND '2026-01-31' catches the whole range; IN (1, 2, 3) is shorthand for three OR conditions; LIKE 'A%' finds anything starting with A.

A view is a saved query that behaves like a virtual table. It stores no data itself; when you select from it, SQL Server runs the underlying query. Views simplify complex joins, hide columns, and give a stable interface even as base tables change.

You can read from most views normally. Updating through a view works only when the view maps cleanly to one base table without aggregation.

An IDENTITY column auto-generates increasing numbers as rows are inserted, so you don't supply the value yourself. IDENTITY(1,1) starts at 1 and increments by 1, which is the usual surrogate primary key pattern.

SCOPE_IDENTITY() returns the last identity value your statement generated. TRUNCATE resets the seed; DELETE does not, so gaps can appear after deletions.

GETDATE() returns the current date and time. DATEADD(unit, n, date) shifts a date. DATEDIFF(unit, start, end) returns the difference between two dates. DATEPART extracts a piece like year or month, and FORMAT or CONVERT reshape output.

Store dates in DATE, DATETIME2, or DATETIMEOFFSET types rather than strings, so comparisons and math work correctly.

SQL injection is when user input is concatenated into a query so an attacker can inject their own SQL, reading or destroying data. Building queries by gluing strings together is what opens the hole.

The fix is parameterized queries or stored procedures with parameters, so input is treated as data, never as executable SQL. Never build a query by concatenating raw user input.

ACID is Atomicity, Consistency, Isolation, Durability. Atomicity means a transaction is all-or-nothing. Consistency means it moves the database from one valid state to another. Isolation means concurrent transactions don't see each other's half-done work. Durability means once committed, changes survive a crash.

SQL Server guarantees these through the transaction log and locking. Wrapping related writes in BEGIN TRANSACTION and COMMIT is how you get atomicity across multiple statements.

SQL Server offers READ UNCOMMITTED (allows dirty reads), READ COMMITTED (the default, no dirty reads), REPEATABLE READ (no non-repeatable reads), SERIALIZABLE (no phantom reads, strictest), and SNAPSHOT (row versioning so readers don't block writers).

Higher isolation means fewer anomalies but more blocking. SNAPSHOT and READ COMMITTED SNAPSHOT use versioning to give consistency without readers holding locks, which is why many teams enable them.

A stored procedure is a named batch of T-SQL that can modify data, return multiple result sets, use output parameters, and handle transactions. A function returns a single value or a table and must be side-effect free, so it can't change data and can be used inside a query.

The practical split: use a function when you need a value inside a SELECT or WHERE, and a procedure when you're performing an action like inserting or orchestrating logic.

A CTE is a named temporary result set defined with WITH that exists only for the query that follows it. It makes complex queries readable by naming intermediate steps instead of nesting subqueries.

A recursive CTE references itself, which is how you walk hierarchies like an org chart or a category tree without a loop.

Window functions compute a value across a set of rows related to the current row, without collapsing them the way GROUP BY does. The OVER clause defines the window with PARTITION BY and ORDER BY.

ROW_NUMBER assigns a unique sequential number. RANK gives tied rows the same rank but skips the next numbers. DENSE_RANK gives ties the same rank with no gaps. All three keep every row in the output.

A non-correlated subquery runs once, independent of the outer query, and its result feeds the outer query. A correlated subquery references a column from the outer query, so it runs once per outer row, which can be slow.

Correlated subqueries are often rewritable as joins or window functions that the optimizer handles better. Recognizing when to rewrite one is what this question checks.

A temp table (#temp) lives in tempdb, supports indexes and statistics, and can be altered after creation, which makes it better for large intermediate sets. A table variable (@table) is scoped to the batch, holds no column statistics, and suits small result sets.

The statistics difference matters: the optimizer often assumes a table variable holds one row, which can produce bad plans on larger data. For big sets, reach for a temp table.

Logically the engine processes FROM and JOIN first, then WHERE, then GROUP BY, then HAVING, then SELECT, then DISTINCT, then ORDER BY, and finally TOP or OFFSET. Not the written order.

This order explains why you can't use a SELECT alias in WHERE (WHERE runs before SELECT) but can in ORDER BY (which runs after SELECT), and why HAVING sees aggregates that WHERE can't.

A covering index contains every column a query needs, either in the key or in INCLUDE columns, so SQL Server answers the query from the index alone without touching the table. That avoids the extra key lookup into the clustered index.

You put the columns you filter and join on in the key, and the columns you only return in INCLUDE. It's one of the highest-impact tuning moves for a hot read query.

MERGE performs an upsert: it compares a source to a target and, in one statement, inserts rows that are new, updates rows that match, and optionally deletes rows that no longer exist in the source. It's the classic tool for syncing a staging table into a target.

It reduces several separate statements to one, though on high-concurrency workloads some teams prefer explicit INSERT and UPDATE for predictability.

You wrap risky statements in BEGIN TRY and BEGIN CATCH. If an error fires in the TRY block, control jumps to CATCH, where functions like ERROR_MESSAGE and ERROR_NUMBER describe what went wrong. You typically ROLLBACK there and optionally re-raise with THROW.

Combined with transactions, TRY CATCH is how you make a multi-statement operation atomic: commit at the end of TRY, roll back in CATCH.

A deadlock happens when two transactions each hold a lock the other needs, so neither can proceed. SQL Server detects this, picks one as the victim, and rolls it back with error 1205 so the other can continue.

You reduce deadlocks by accessing tables in a consistent order across transactions, keeping transactions short, using the right indexes to shrink locking, and retrying the victim in application code.

PIVOT rotates rows into columns, turning distinct values from one column into headers with aggregated values beneath, which is how you build a cross-tab report. UNPIVOT does the reverse, folding columns back into rows.

PIVOT always aggregates, so you pick an aggregate like SUM. When the value set is dynamic, teams often build the PIVOT with dynamic SQL.

CASE returns a value based on conditions, like an inline if-else inside a query. The searched form evaluates WHEN conditions in order and returns the first match's THEN value, falling back to ELSE. It works in SELECT, WHERE, ORDER BY, and GROUP BY.

A common use is conditional aggregation: SUM a CASE expression to count rows meeting a condition without a separate query.

ISNULL(a, b) takes exactly two arguments and returns b when a is NULL. COALESCE takes any number of arguments and returns the first non-NULL. COALESCE is standard SQL and more flexible; ISNULL is T-SQL specific.

A subtle difference: ISNULL returns the data type of the first argument, while COALESCE follows data-type precedence rules, which can produce different result types.

A trigger is code that runs automatically in response to a data event. A DML trigger fires on INSERT, UPDATE, or DELETE, and inside it the inserted and deleted virtual tables hold the affected rows. Triggers enforce rules, audit changes, or cascade updates.

Use them sparingly: hidden logic in triggers is hard to debug, and heavy triggers slow down every write. Many teams prefer explicit application logic or constraints instead.

A scalar function returns a single value and is called wherever an expression fits. A table-valued function returns a table you can query and join, either inline (a single SELECT) or multi-statement (built up in a table variable).

Inline table-valued functions perform well because the optimizer folds them into the calling query. Scalar functions called per row can hurt performance badly, which is a common tuning finding.

Group by the columns that define a duplicate, then keep only groups with more than one row using HAVING COUNT(*) > 1. That surfaces which values are duplicated and how many times.

To delete duplicates while keeping one copy, ROW_NUMBER partitioned by those columns is the clean approach: number the copies and delete every row where the number is greater than one.

The clean way is a window function: DENSE_RANK by salary descending, then keep the rows where the rank equals two. DENSE_RANK handles ties correctly so two people tied at the top don't hide the real second place.

An older approach uses TOP with a subquery or OFFSET FETCH, but the window-function version scales to Nth highest by just changing the number.

OFFSET FETCH is the modern way: ORDER BY a stable column, OFFSET past the rows you've seen, then FETCH NEXT the page size. It requires an ORDER BY because paging without a defined order is meaningless.

For deep pages, keyset pagination (a WHERE filter on the last seen key) beats OFFSET, which grows slower the further you page because it still counts through skipped rows.

LEN returns the number of characters in a string and ignores trailing spaces. DATALENGTH returns the number of bytes the value uses. For NVARCHAR, DATALENGTH is roughly twice LEN because Unicode uses two bytes per character.

So LEN answers 'how many characters' and DATALENGTH answers 'how much storage', which matters when you're checking whether data fits a column or estimating row size.

Get the actual execution plan, then read right to left and top to bottom, following the thickest arrows because arrow width shows row counts. Look for expensive operators: a clustered index scan where you expected a seek, a key lookup on a hot query, hash or sort spills to tempdb, and a big gap between estimated and actual rows.

That estimate-versus-actual gap is the tell for stale statistics or a bad parameter estimate. The fix is usually a better index, updated statistics, or rewriting the query so the optimizer can use an index.

On first execution of a stored procedure, SQL Server sniffs the passed parameter values and builds a plan optimized for them, then caches that plan. Usually this helps. It hurts when data is skewed: a plan built for a parameter that matches ten rows gets reused for one that matches a million, or the reverse.

Fixes include OPTION (RECOMPILE) for the statement, OPTIMIZE FOR a representative value, local variables to force an average estimate, or splitting into separate procedures. Each trades plan reuse against getting the right plan per call.

A good clustered key is narrow, unique, static, and ever-increasing. Narrow because every nonclustered index carries the clustered key as its row pointer. Unique so the engine doesn't add a uniquifier. Static so rows don't move. Ever-increasing (like an identity) so inserts append rather than fragmenting pages.

That's why a monotonic integer identity often beats a wide natural key or a random GUID. A random GUID as the clustered key causes page splits and fragmentation across the whole table.

Statistics are histograms describing the distribution of values in a column, which the optimizer uses to estimate how many rows an operation will touch. Good estimates produce good plans; stale or missing statistics produce bad ones.

SQL Server auto-creates and auto-updates statistics, but on large tables the update threshold can lag, leaving estimates behind reality. UPDATE STATISTICS or a maintenance job keeps them fresh, and the cardinality estimator version can change plan behavior across upgrades.

Locking is normal: SQL Server takes locks to protect data during reads and writes. Blocking is when one transaction's lock forces another to wait. Short blocking is expected; sustained blocking is a problem, usually from long transactions, missing indexes causing broad locks, or an escalated lock.

Diagnose with the sys.dm_exec_requests and sys.dm_os_waiting_tasks views to see the blocking chain, then shorten transactions, add indexes to narrow locks, or move readers to snapshot isolation so they stop blocking writers.

When a single statement acquires too many row or page locks (the threshold is around 5,000 locks on one object), SQL Server escalates them to a single table lock to save memory. That one coarse lock is cheaper to track but blocks far more concurrency.

It surfaces as sudden blocking during large updates. Mitigations include batching big modifications into smaller chunks, better indexing so fewer rows are touched, or in specific cases disabling escalation on a table.

An indexed view (a materialized view in SQL Server terms) has a unique clustered index built on it, so its results are physically stored and kept in sync automatically as base tables change. It speeds up expensive aggregations and joins that run often.

The cost is write overhead: every change to a base table updates the indexed view too, and there are strict requirements (schema binding, deterministic expressions). Worth it for read-heavy aggregate queries, not for volatile tables.

Partitioning splits one large table into physical partitions by a key, commonly a date range, while it still looks like a single table to queries. The main wins are manageability and fast data movement: you can switch an entire partition in or out instantly instead of deleting millions of rows.

It also enables partition elimination, where a query touching one date range only scans relevant partitions. Partitioning is a management and maintenance tool more than a pure query-speed feature; people expecting it to fix all slow queries are often disappointed.

Every data change is written to the transaction log before the data file, which is what makes durability and rollback possible. The log is a sequential record of changes. In the FULL recovery model, log records stay until a log backup runs, which is how point-in-time restore works.

The log grows out of control when it can't truncate: no log backups in FULL model, a long-running open transaction, or replication or availability groups holding log records. The fix is regular log backups, not shrinking, and finding the transaction or feature pinning the log.

SIMPLE reclaims log space automatically and supports no point-in-time restore; you can only restore to the last full or differential backup. FULL logs everything and, with regular log backups, supports point-in-time restore. BULK_LOGGED is a variant of FULL that minimally logs bulk operations to reduce log growth, at the cost of point-in-time restore during those operations.

The choice follows the recovery objective: SIMPLE for data you can rebuild, FULL for anything where losing recent transactions is unacceptable.

Fragmentation is when the logical order of index pages no longer matches their physical order, or pages sit partly empty, which happens as rows are inserted, updated, and deleted. Heavy fragmentation makes range scans read more pages than needed.

Manage it by measuring with sys.dm_db_index_physical_stats, then reorganizing (light, online) below roughly 30 percent and rebuilding (heavier) above it. On modern SSD-backed systems, fragmentation matters less than it once did, so blanket nightly rebuilds are often wasted work.

A columnstore index stores data by column instead of by row, compresses it heavily, and processes it in batches. That makes analytic queries scanning millions of rows and aggregating a few columns dramatically faster than a rowstore index.

Use columnstore for data warehouse and reporting workloads. For transactional workloads with lots of single-row lookups and updates, rowstore stays better, though nonclustered columnstore indexes let one table serve both patterns.

An availability group keeps one or more secondary replicas in sync with a primary by shipping transaction log records. If the primary fails, the group fails over to a synchronous secondary, promoting it to primary with no committed data lost. Readable secondaries can also offload reporting.

Synchronous replicas guarantee zero data loss but add commit latency; asynchronous replicas add none but risk losing the last transactions on failover. Choosing per replica is the real design decision.

Historically a scalar function in a SELECT ran once per row, row by row, and the optimizer couldn't see inside it, so it produced serial plans and bad estimates. On large result sets this quietly wrecked performance.

SQL Server 2019 added scalar UDF inlining, which folds many scalar functions into the calling query so the optimizer can plan them properly. It's not automatic for every function, so knowing the old cost and the newer inlining feature is the complete answer.

Pessimistic concurrency uses locks: a transaction locks rows it reads or writes so others wait, which prevents conflicts but reduces concurrency. Optimistic concurrency, via SNAPSHOT isolation and row versioning, lets readers see a consistent version without blocking writers, then detects conflicts at commit time.

SNAPSHOT and READ COMMITTED SNAPSHOT trade tempdb space for the version store against much lower blocking. The design question is whether your workload has more read-write contention (favor optimistic) or needs strict serialization (favor pessimistic).

Clarify first: what must be captured (which tables, before and after values, who and when), retention, and read patterns. Then pick a mechanism. Temporal tables are the built-in choice: SQL Server keeps a history table automatically with system-versioned validity periods, no triggers to maintain. Triggers give more control but add write overhead and hidden logic. Change Data Capture suits feeding downstream systems.

The closing step is the operational edges: audit writes shouldn't block business transactions, so keep them cheap or asynchronous; partition or archive history so it doesn't bloat; and separate audit permissions so the data can't be quietly altered.

Compare what changed. A query that was fine yesterday usually means a plan change, a data volume jump, stale statistics, or a new blocking pattern, not a bug in the SQL. Pull the current plan and compare it to a known-good one from Query Store, which keeps plan history exactly for this.

From there the ladder is familiar: if the plan regressed, force the good plan or fix the statistics; if data grew, revisit indexes; if it's blocking, find the blocker. The discipline is compare-then-fix, not guess-then-change.