Indexes

Indexes

What it means

An index is a separate, ordered data structure SQL Server maintains alongside a table, letting it find matching rows quickly (like a book’s index) instead of scanning every row. A table’s PRIMARY KEY creates a clustered index automatically (the table’s rows are physically ordered by it — there can only be one per table). You can add nonclustered indexes on other columns you frequently filter or sort by.

Examples

-- Nonclustered index on a column you frequently filter by
CREATE INDEX IX_Orders_CustomerId ON Orders (CustomerId);

-- Composite index -- speeds up queries filtering on both columns together
CREATE INDEX IX_Orders_CustomerId_OrderDate ON Orders (CustomerId, OrderDate);

-- Unique index -- also enforces uniqueness, like a UNIQUE constraint
CREATE UNIQUE INDEX IX_Customers_Email ON Customers (Email);

-- See what indexes exist on a table
EXEC sp_helpindex 'Orders';

-- Check whether a query actually uses an index (via execution plan tools)
SELECT * FROM Orders WHERE CustomerId = 42;   -- fast with IX_Orders_CustomerId

Common mistake

Adding indexes to every column “just in case” — indexes speed up reads but slow down every INSERT/UPDATE/DELETE, since SQL Server must maintain each index in sync with every write. Over-indexing a write-heavy table can hurt overall performance more than it helps.

-- Adding indexes on rarely-queried columns adds write overhead for no read benefit
CREATE INDEX IX_Orders_InternalNotes ON Orders (InternalNotes);   -- probably never filtered on

-- Better: index only columns actually used in WHERE, JOIN, and ORDER BY clauses,
-- based on real query patterns -- not speculatively on everything

Quick practice

1. What’s the difference between a clustered and a nonclustered index?

   Answer

   A clustered index determines the physical storage order of the table's rows (only one per table, usually the primary key); a nonclustered index is a separate structure pointing back to the rows, and a table can have many.

2. Why isn’t it a good idea to add an index on every column?

   Answer

   Every index must be updated on every INSERT/UPDATE/DELETE, so excessive indexing slows down writes for read benefits that may never materialize if that column is rarely queried.

3. What kind of query pattern benefits most from adding an index on a column?

   Answer

   Columns frequently used in WHERE filters, JOIN conditions, or ORDER BY clauses, especially on large tables.