---

The 15 things to be sure of

If you remember nothing else, remember these.

1	One clustered index per table (it IS the table); many non-clustered (key + pointer back).
2	Composite index: only useful from a leading prefix — an index on (City, Age) can't seek on Age alone.
3	INCLUDE columns you only return; key columns you filter, join or sort on.
4	Wrapping a column in a function kills the seek — WHERE YEAR(d)=2026 is non-SARGable.
5	A view is updatable only if each statement maps to one base table; otherwise use an INSTEAD OF trigger.
6	SCOPE_IDENTITY(), never @@IDENTITY (a trigger steals it). Better still: OUTPUT INSERTED.Id.
7	Delete duplicates: ROW_NUMBER() OVER (PARTITION BY …) then DELETE WHERE rn > 1.
8	ROW_NUMBER never ties · RANK ties and skips · DENSE_RANK ties and doesn't skip.
9	Never concatenate user input into SQL. Parameterise — stored procedures are not automatically safe.
10	ACID · default isolation is Read Committed · dirty → non-repeatable → phantom, fixed by RC → RR → SERIALIZABLE.
11	NOLOCK = dirty reads, duplicated and skipped rows. The real fix is an index or READ_COMMITTED_SNAPSHOT.
12	Blocking resolves itself; a deadlock never can, so SQL Server kills a victim (error 1205) — retry it.
13	Deadlocks: fix the lock order first, then transaction length, then indexes.
14	Parameter sniffing: one cached plan for skewed data. OPTION (RECOMPILE) is the pragmatic fix.
15	Slow query? Actual plan + STATISTICS IO first. Then index, SARGability, statistics, fewer rows.

Words that signal seniority

SARGable · covering index · key lookup · cardinality estimate · parameter sniffing · lock escalation · row versioning · RCSI · write-ahead logging · set-based · logical reads