SQL Server Indexes and Transaction Isolation Levels Cheat Sheet

Note7 min readTechInterviewNotes Editorial TeamUpdated 17 Oct 2026FreeSave

SQL Server clustered, nonclustered, covering and filtered indexes, SARGable predicates, and the isolation levels with their anomalies, plus RCSI vs SNAPSHOT, on one revision page.

Learning outcome

By the end of this cheat sheet you should be able to explain clustered and nonclustered indexes, covering and filtered indexes, and why a query does or doesn't use an index. You should also be able to name the SQL Server isolation levels, the anomalies each one prevents, and when to use row versioning (RCSI and SNAPSHOT) instead of locks. The facts here are for SQL Server and Azure SQL Database. Other engines differ in the details.

Intuition

A rowstore index organizes key values in a B+ tree. A suitable index can enable a seek: locating matching keys without scanning every entry. The optimizer may still choose a scan; indexes can speed reads but cost writes, memory and storage.
An isolation level decides what a transaction may see of other transactions' uncommitted or newly committed work. Stronger isolation means fewer anomalies, at the cost of more blocking or more version-store work.

Quick reference: index types

Index	What it is	Key facts
Clustered	The table's rows stored in key order (B+ tree whose leaf level is the data)	At most one per table. The primary key creates a clustered index by default if none exists. Narrow, unique, static and ever-increasing keys work best
Heap	A table with no clustered index	Rows located by RID; a growing row can be forwarded when it no longer fits on its original page
Nonclustered	Separate B+ tree of the key columns; the leaf holds the clustered key (or RID) as the row locator	Up to 999 per table. A lookup back to the base table is a key lookup (or RID lookup)
Covering	A nonclustered index containing every column the query needs	Add non-key columns with `INCLUDE (...)` to avoid key lookups
Unique	Enforces uniqueness	A `UNIQUE` constraint or primary key creates one
Filtered	Nonclustered index with a `WHERE` clause	Smaller and cheaper to maintain. The optimizer can only use it when the query predicate matches the filter (watch out for parameterized queries)
Columnstore	Column-wise, compressed storage with batch mode	Analytics and large scans/aggregations, not point lookups

Size limits worth knowing: max 32 key columns. Max key size 900 bytes for a clustered index and 1,700 bytes for a nonclustered index (SQL Server 2016+ and Azure SQL).

SQL

-- Seek on CustomerId, range on OrderDate, no key lookup for Total/Status
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Date
    ON dbo.Orders (CustomerId, OrderDate DESC)
    INCLUDE (Total, Status);

-- Small index for the hot subset only
CREATE NONCLUSTERED INDEX IX_Orders_Open
    ON dbo.Orders (CreatedAt)
    WHERE Status = 'Open';

When an index is (not) used

Pattern	Seek possible?	Why
`WHERE CustomerId = @id` on index `(CustomerId, OrderDate)`	Yes	Equality on the leading column
`WHERE OrderDate > @d` on index `(CustomerId, OrderDate)`	Usually no (scan)	Not the leading column. Composite indexes are used left to right
`WHERE YEAR(OrderDate) = 2026`	No	A function on the column makes the predicate non-SARGable. Rewrite as `OrderDate >= '20260101' AND OrderDate < '20270101'`
`WHERE Name LIKE 'Ab%'`	Yes	Prefix range
`WHERE Name LIKE '%ab'`	No	Leading wildcard
`WHERE VarcharCol = N'x'` (nvarchar parameter)	Often no	Implicit conversion on the column side
Low-selectivity predicate returning a large share of rows	Optimizer may prefer a scan	Many key lookups cost more than one scan

Rule of thumb for composite keys: equality columns first, then the range or sort column, then INCLUDE the remaining selected columns.

Write cost: writes maintain the indexes whose stored entries or filtered membership change; updates to included columns also count. Over-indexing slows down OLTP writes and increases lock and log volume.

Quick reference: isolation levels

Level	Dirty read	Non-repeatable read	Phantom	How SQL Server implements it
READ UNCOMMITTED (`NOLOCK`)	Possible	Possible	Possible	No shared locks for reads. Can also skip or double-read rows during page splits
READ COMMITTED (default)	Prevented	Possible	Possible	Shared locks held only while reading, or statement-level row versions when `READ_COMMITTED_SNAPSHOT` is ON
REPEATABLE READ	Prevented	Prevented	Possible	Shared locks held until the transaction ends
SERIALIZABLE	Prevented	Prevented	Prevented	Key-range locks held until the transaction ends. Most blocking; deadlock-prone
SNAPSHOT	Prevented	Prevented	Prevented	Transaction-level row versions in tempdb, or the persistent version store when ADR is enabled. Writers can raise update-conflict error 3960 when another transaction changed the target row after the snapshot transaction began. Needs `ALLOW_SNAPSHOT_ISOLATION ON`

Definitions:

Dirty read: reading another transaction's uncommitted change.
Non-repeatable read: the same row returns different values within one transaction.
Phantom: re-running a range query returns new or missing rows.

RCSI vs SNAPSHOT

	READ COMMITTED SNAPSHOT (RCSI)	SNAPSHOT
Enable	`ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON`	`ALTER DATABASE ... SET ALLOW_SNAPSHOT_ISOLATION ON` + `SET TRANSACTION ISOLATION LEVEL SNAPSHOT`
Consistency point	Each statement	Whole transaction
App change needed	No; it changes the default READ COMMITTED behaviour	Yes; each session opts in
Readers block writers?	No	No
Update conflicts	No application-visible snapshot update conflicts; optimized locking detects and retries its conflicts internally	Yes, error 3960. The app must retry
Default	OFF in SQL Server; ON in Azure SQL Database	OFF in SQL Server; ON (ALLOW_SNAPSHOT_ISOLATION) in Azure SQL Database — sessions still must `SET TRANSACTION ISOLATION LEVEL SNAPSHOT`

Cost: row versions live in tempdb (or in the persistent version store when Accelerated Database Recovery is on), and each row can carry up to 14 bytes of versioning information, added the first time it is modified or inserted while versioning is enabled.

Failure modes

Using NOLOCK "for speed": you trade blocking for wrong results (dirty, missing or duplicated rows).
Read-then-write without the right lock (check stock → decrement): lost updates under READ COMMITTED. Use an atomic UPDATE ... WHERE Stock >= @qty, UPDLOCK/HOLDLOCK hints, or optimistic concurrency with rowversion.
Assuming SERIALIZABLE is the "safe default": it causes blocking and deadlocks under load. Keep transactions short instead.
A filtered index that is never used because the query is parameterized and the plan can't prove the filter matches.
Long-running transactions under RCSI or SNAPSHOT can retain old versions and grow the version store (tempdb, or PVS when ADR is enabled).
In .NET, TransactionScope defaults to Serializable. Set TransactionOptions.IsolationLevel explicitly.

Interview drill

Clustered vs nonclustered: what does the leaf level contain in each?
What is a key lookup, and how do you remove it?
Why is WHERE YEAR(OrderDate) = 2026 slow, and how do you rewrite it?
Which anomalies does REPEATABLE READ still allow?
RCSI vs SNAPSHOT: what is the consistency point of each, and which one can raise update conflicts?
How do you prevent a lost update on a stock counter?

Revision checklist

Can I draw the B+ tree idea and explain seek vs scan?
Can I design a composite index for a given WHERE + ORDER BY?
Can I fill in the isolation-level anomaly table from memory?
Do I know that READ COMMITTED is the default, and that RCSI is ON by default in Azure SQL Database?
Do I know the TransactionScope default isolation level trap?

Examples note

The T-SQL above is illustrative and shows standard syntax. Check index choices against the actual execution plan (SET STATISTICS IO ON, actual plan, Query Store) on your own data, because the optimizer's choices depend on statistics.