What is an index in SQL Server?
Quick Answer
An index is an on-disk structure (a B-tree) that lets SQL Server find rows without scanning the whole table. It stores the indexed column values in sorted order with pointers to the matching rows, so lookups, range queries, joins and ORDER BY on those columns become fast. The trade-off is extra storage and slower writes, because every insert/update/delete must also maintain the index.
An index is a sorted structure — usually a B-tree — that SQL Server uses to find rows quickly instead of scanning the whole table. It holds the indexed column values in order with pointers to the rows, so a filter, a join or an ORDER BY on those columns becomes a seek rather than a scan. It's exactly like the index at the back of a book. The cost is storage and write speed: every insert, update and delete has to maintain the index too, so you add them deliberately, based on the queries you actually run, not on every column.
Think of the index at the back of a book: instead of reading every page to find a topic, you look it up alphabetically and jump straight to the page. A SQL Server index is the same idea for table rows.
Structure. Most indexes are B-trees: a root page, intermediate levels, and leaf pages holding the sorted key values. The engine walks from the root down to the leaf in a handful of page reads — an index seek — instead of reading every page — a table/index scan.
Types you should name:
- Clustered — defines the physical order of the table; the leaf level is the data. One per table.
- Non-clustered — a separate structure whose leaf holds the key plus a row locator back to the table. Many per table.
- Covering index — a non-clustered index that
INCLUDEs enough columns to answer a query entirely from the index. - Filtered index — an index with a
WHEREclause, for a subset of rows. - Unique / composite — enforces uniqueness / indexes multiple columns (order matters).
Cost. Indexes consume space, must be kept in sync on every DML statement, and can fragment. Too many indexes slows writes and confuses the optimiser.
-- non-clustered index to support a common filter + sort
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderDate
ON dbo.Orders (CustomerId, OrderDate DESC);
-- covering index: the query never has to look up the base table
CREATE NONCLUSTERED INDEX IX_Orders_Status_Covering
ON dbo.Orders (Status)
INCLUDE (CustomerId, Total, OrderDate);- Turn full-table scans into seeks — often orders of magnitude faster reads
- Speed up joins, WHERE filters, ORDER BY and GROUP BY on the indexed columns
- A covering index can answer a query without touching the table at all
- Unique indexes enforce data integrity
- Support foreign keys so cascading operations and lookups stay fast
Practise the whole thread the way an interviewer walks it — each question builds on the last.