🧰 Core Tech Refresher

SQL Server Essentials

Indexes, isolation levels, execution plans.

Indexes — a clustered index physically orders the table (one per table, usually the PK); nonclustered indexes are separate lookup structures pointing back to the clustered key. Over-indexing slows writes; under-indexing slows reads — be ready to say you'd check the execution plan before guessing.

Isolation levels — Read Committed (SQL Server default) prevents dirty reads but allows non-repeatable reads and phantom reads. Read Committed Snapshot Isolation (RCSI) uses row versioning instead of locks for reads, common in modern OLTP setups to reduce blocking. Know Serializable exists as the strictest level (full protection, most blocking) for when you'd actually reach for it (financial reconciliation-type operations).

Execution plans — shows whether SQL Server used your index (Index Seek = good, Index/Table Scan on a large table = investigate), join strategy (Nested Loops vs Hash Match vs Merge), and estimated vs actual row counts (large divergence = stale statistics).

Flashcards (3)

Difference between a clustered and a nonclustered index?

tap to reveal answer

Clustered index physically orders the table's rows by its key (one per table); nonclustered indexes are separate structures with pointers back to the clustered key (or row) — a table can have many.

What does Read Committed Snapshot Isolation (RCSI) change about how reads work?

tap to reveal answer

Readers see a versioned snapshot of the row instead of blocking behind a writer's lock — trades some tempdb overhead for far less read/write blocking, common in high-concurrency OLTP systems.

In an execution plan, what's the difference between an Index Seek and an Index Scan, and which is generally the concern on a large table?

tap to reveal answer

A Seek navigates directly to matching rows via the index (efficient); a Scan reads the whole index/table (fine for small tables, a red flag for large ones — usually means a missing or unusable index for that predicate).