SQLMentor // learn sql server

Indexes

Indexes are the difference between a 5-millisecond lookup and a 5-second table scan. SQL Server's index family covers row-store (B-tree) and column-store, with options for filtering, includes, and uniqueness.

Index Types

Type Storage Key idea
Clustered Table rows ARE the index leaves One per table — defines physical order
Nonclustered Separate B-tree pointing back to data Many per table; the workhorse
Unique Either kind, with uniqueness enforced Backs PRIMARY KEY / UNIQUE
Filtered Nonclustered with a WHERE predicate Smaller, targeted indexes
Columnstore Column-oriented, compressed Analytics workloads
Full-text Specialized inverted index Text search

Clustered Index

Every table should have a clustered index ("a heap is a bug"). It defines how rows are physically stored on disk.

-- Implicitly created by the PK constraint
CREATE TABLE hr.orders (
    order_id     INT IDENTITY PRIMARY KEY,   -- clustered by default
    customer_id  INT NOT NULL,
    order_date   DATE NOT NULL,
    total        DECIMAL(10,2) NOT NULL
);

-- Or define explicitly with a different key
CREATE TABLE hr.orders_partitioned (
    order_id    INT IDENTITY,
    order_date  DATE NOT NULL,
    customer_id INT,
    total       DECIMAL(10,2),
    CONSTRAINT PK_orders PRIMARY KEY NONCLUSTERED (order_id),
    INDEX CIX_orders_date CLUSTERED (order_date)
);

A good clustered key is narrow, unique, static, and ever-increasing (e.g., IDENTITY or bigint keys). Avoid clustered GUIDs — they cause heavy page splits.

Nonclustered Index

CREATE NONCLUSTERED INDEX IX_orders_customer
    ON hr.orders (customer_id);

-- Composite index — column order matters!
CREATE NONCLUSTERED INDEX IX_orders_customer_date
    ON hr.orders (customer_id, order_date DESC);

The leaf level stores the indexed columns + a pointer back to the row in the clustered index (the clustering key) or to the heap RID.

Composite Key Order Matters

A composite index (a, b, c) can be used to seek for predicates on:

  • a
  • a, b
  • a, b, c

It cannot seek for b alone or c alone. Lead with the most-selective, most-frequently-filtered column.

-- Useful for queries that filter by customer_id (with or without order_date)
CREATE INDEX IX_orders_cust_date ON hr.orders (customer_id, order_date);

-- This index does NOT help: WHERE order_date = '2024-01-01'
SELECT * FROM hr.orders WHERE order_date = '2024-01-01';   -- scan, not seek

Filtered Indexes

A nonclustered index with a WHERE clause — smaller and faster to maintain. Especially useful for sparse columns:

-- Only the small subset of un-shipped orders
CREATE NONCLUSTERED INDEX IX_orders_pending
    ON hr.orders (order_date)
    WHERE shipped_at IS NULL;

-- Active employees only
CREATE INDEX IX_employees_active
    ON hr.employees (department_id)
    WHERE termination_date IS NULL;

Queries must include a matching predicate to use the filtered index.

Covering Indexes (INCLUDE)

INCLUDE adds non-key columns to the leaf level so a query can be answered entirely from the index — no key-lookup back to the base table:

CREATE NONCLUSTERED INDEX IX_orders_customer_covering
    ON hr.orders (customer_id, order_date)
    INCLUDE (total, status);

-- Query is fully covered:
SELECT customer_id, order_date, total, status
FROM   hr.orders
WHERE  customer_id = 42
ORDER BY order_date DESC;

Covering indexes are the most common practical optimisation — turning a key-lookup-heavy plan into a clean seek.

Unique Indexes

-- Equivalent to a UNIQUE constraint (which actually creates a unique index)
CREATE UNIQUE INDEX UX_employees_email ON hr.employees (email);

-- Filtered + unique — allow many NULLs but require unique non-NULL values
CREATE UNIQUE INDEX UX_employees_ssn
    ON hr.employees (ssn)
    WHERE ssn IS NOT NULL;

Columnstore (Brief)

Columnstore indexes store data column-by-column with heavy compression. Designed for analytics — fast scans of millions of rows for SUM, COUNT, AVG. Two flavours:

-- Clustered columnstore (whole table is column-organised)
CREATE CLUSTERED COLUMNSTORE INDEX CCIX_orders_fact
    ON hr.orders_fact;

-- Nonclustered columnstore (analytic workload on top of OLTP table)
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCIX_orders
    ON hr.orders (customer_id, order_date, total);

Columnstore is huge for fact-table reporting and shouldn't be your first reach for narrow OLTP queries.

Index DDL

-- Drop
DROP INDEX IX_orders_customer ON hr.orders;

-- Disable (keep metadata, drop the leaves)
ALTER INDEX IX_orders_customer ON hr.orders DISABLE;

-- Rebuild (drop and recreate; takes a schema-modification lock unless ONLINE = ON)
ALTER INDEX IX_orders_customer ON hr.orders REBUILD WITH (ONLINE = ON);

-- Reorganize (online; defragments leaves only)
ALTER INDEX IX_orders_customer ON hr.orders REORGANIZE;

Inspecting Indexes

-- All indexes on a table
SELECT  i.name, i.type_desc, i.is_unique, i.is_primary_key,
        i.has_filter, i.filter_definition
FROM    sys.indexes i
WHERE   i.object_id = OBJECT_ID('hr.orders')
ORDER BY i.index_id;

-- Columns in each index, in order
SELECT  i.name AS index_name, c.name AS column_name,
        ic.is_included_column, ic.is_descending_key, ic.key_ordinal
FROM    sys.indexes i
JOIN    sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN    sys.columns       c  ON c.object_id  = ic.object_id AND c.column_id = ic.column_id
WHERE   i.object_id = OBJECT_ID('hr.orders')
ORDER BY i.index_id, ic.key_ordinal;

Best Practices

  • Every table should have a clustered index — narrow, unique, static, ever-increasing.
  • Lead composite keys with the most-filtered column; secondary columns are mostly for ordering and seek refinement.
  • Use INCLUDE to cover queries — usually the easiest big win.
  • Filtered indexes shine on sparse predicates (status, deleted flags, NULLs).
  • Don't over-index: every index slows DML and consumes space. Review unused indexes via sys.dm_db_index_usage_stats.

Summary

  • Clustered = ordered table; Nonclustered = pointer index pointing back to data.
  • Composite key column order determines seekability — lead with the most selective filter.
  • INCLUDE adds covering columns; filtered indexes shrink size with WHERE.
  • Columnstore is for analytics; row-store B-trees for OLTP.
  • Inspect with sys.indexes / sys.index_columns; rebuild or reorganise based on fragmentation.