Why Indexes Exist
Without an index, SQL Server must perform a table scan, reading every row in a heap or clustered index to find the ones that match a WHERE clause, which is fine for a few hundred rows but ruinous for a table with millions. An index is a separate, sorted B-tree structure built on one or more columns that lets the engine binary-search down to the matching rows in a handful of page reads instead of scanning the whole table, at the cost of extra storage and slower writes since every INSERT, UPDATE, or DELETE must also maintain the index.
Cricket analogy: A table scan is like reading every ball-by-ball commentary of an entire IPL season to find every six Jos Buttler hit, while an index is the pre-built 'sixes by batter' stat page the broadcaster's database already sorted for instant lookup.
Clustered vs Nonclustered Indexes
A clustered index determines the physical storage order of the table's data rows, so a table can have at most one clustered index (commonly built on the primary key), and if none is defined the table is a heap with no guaranteed row order. A nonclustered index is a separate structure that stores the indexed column(s) plus a row locator back to the base table — either the clustering key if one exists, or a physical row ID for a heap — and a table can have many nonclustered indexes, each useful for a different query pattern.
Cricket analogy: A clustered index is like a scorebook physically bound in chronological match order — there can only be one true physical ordering — while nonclustered indexes are like separate cross-reference cards sorted by batter name or venue, each pointing back to the chronological entry.
-- Clustered index on the primary key (often created automatically)
CREATE CLUSTERED INDEX IX_Orders_OrderId
ON dbo.Orders (OrderId);
-- Nonclustered index to speed up lookups by customer and status,
-- with an included column to make it a covering index
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_Status
ON dbo.Orders (CustomerId, OrderStatus)
INCLUDE (OrderDate, TotalAmount);
-- Query the index can fully satisfy without touching the base table
SELECT OrderDate, TotalAmount
FROM dbo.Orders
WHERE CustomerId = 4521 AND OrderStatus = 'Shipped';Covering Indexes and Included Columns
A covering index contains every column a query needs — both in the key and via the INCLUDE clause — so SQL Server can satisfy the query entirely from the nonclustered index without a key lookup back to the clustered index or heap. INCLUDE is preferable to adding extra columns to the index key itself when those columns are only ever selected, not filtered or sorted on, because included columns are stored only at the leaf level, keeping the upper B-tree levels narrower and the index cheaper to maintain.
Cricket analogy: A covering index is like a broadcaster's single graphic overlay that already shows a batter's strike rate, average, and boundary count together, so the director never needs to cut back to a separate stats database mid-over.
A predicate is SARGable (Search ARGument-able) when SQL Server can use an index seek to evaluate it directly, e.g. WHERE OrderDate >= '2026-01-01'. Wrapping the indexed column in a function, like WHERE YEAR(OrderDate) = 2026, usually forces a scan because the optimizer can no longer map the expression to a range on the index key.
Execution Plans and Statistics
The query optimizer chooses between an index seek (navigating the B-tree directly to matching rows) and an index or table scan (reading every row) based on cardinality estimates derived from statistics — histograms SQL Server maintains on indexed and frequently filtered columns describing the distribution of values. When statistics are stale, for example after a large bulk load without an UPDATE STATISTICS or the auto-update threshold not being hit yet, the optimizer's row-count estimates can be badly wrong, leading it to pick a scan when a seek would be far cheaper, or to allocate too little memory for a sort or hash join and spill to tempdb.
Cricket analogy: A stale statistics histogram is like a team's pre-match strategy built on a batter's stats from two seasons ago — the analyst's plan looks sound on paper but is wrong because the underlying data has since shifted.
Adding indexes is not free: every additional nonclustered index adds overhead to every INSERT, UPDATE, and DELETE against the table, and index fragmentation from ongoing writes can degrade seek performance over time. Periodically check fragmentation with sys.dm_db_index_physical_stats and rebuild or reorganize heavily fragmented indexes, and drop indexes that execution plan history shows are never used.
- An index is a sorted B-tree structure that lets SQL Server seek to matching rows instead of scanning every row in a table.
- A table has at most one clustered index, which defines physical row order; it can have many nonclustered indexes.
- A nonclustered index stores a row locator back to the base table — the clustering key, or a physical RID for a heap.
- INCLUDE adds columns to a nonclustered index's leaf level only, creating a covering index without widening the key.
- SARGable predicates let the optimizer seek; wrapping an indexed column in a function usually forces a scan.
- The optimizer's seek-vs-scan decision relies on statistics; stale statistics can produce badly wrong cardinality estimates.
- Every index speeds up reads but slows down writes and consumes storage, so indexing is always a trade-off, not a free win.
Practice what you learned
1. How many clustered indexes can a single SQL Server table have?
2. What does the INCLUDE clause do in a nonclustered index definition?
3. Why does WHERE YEAR(OrderDate) = 2026 typically prevent an index seek on OrderDate?
4. What is a likely consequence of stale statistics on a heavily modified table?
5. What does a nonclustered index on a heap table use as its row locator?
Was this page helpful?
You May Also Like
Transactions and Locking
Understand how SQL Server groups statements into atomic transactions, uses locks to enforce isolation, and how deadlocks arise and get resolved.
Constraints and Keys
Learn how PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT, and NOT NULL constraints enforce data integrity directly at the database layer.
Views and Schemas
Learn how SQL Server views package reusable queries and how schemas organize objects into logical namespaces with independent permissions.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics