SQL Server Administration – Index Types

Index Types in SQL Server

Indexes improve the performance of data retrieval operations. SQL Server supports several index types, each optimized for specific scenarios.

A clustered index determines the physical order of data rows in a table. A table can have only one clustered index.

CREATE CLUSTERED INDEX IX_Orders_OrderDate
ON dbo.Orders (OrderDate);

Feature	Description
Storage	Data rows stored in B‑tree order.
Uniqueness	Can be defined as UNIQUE; otherwise, SQL Server adds a unique identifier.
Use Cases	Range queries, sorting, primary key defaults.

A non‑clustered index is a separate structure that contains a copy of indexed columns and a pointer to the data rows.

CREATE NONCLUSTERED INDEX IX_Products_Price
ON dbo.Products (Price) INCLUDE (ProductName, CategoryID);

Feature	Description
Storage	Separate B‑tree holding key values and row locators.
Include Columns	Additional columns can be stored to cover queries.
Use Cases	Frequent lookups on non‑key columns, covering indexes.

Columnstore indexes store data column‑wise, providing high compression and fast analytics performance.

CREATE CLUSTERED COLUMNSTORE INDEX IX_Sales_Columnstore
ON dbo.Sales;

Feature	Description
Compression	Up to 75% reduction in storage size.
Performance	Optimized for scans and aggregation.
Limitations	Not suitable for heavy point lookups.

A filtered index is a non‑clustered index with a WHERE clause that indexes a subset of rows.

CREATE NONCLUSTERED INDEX IX_Orders_Active
ON dbo.Orders (CustomerID)
WHERE IsActive = 1;

Use filtered indexes to reduce index size and maintenance overhead for sparse data.

A unique index enforces the uniqueness of the indexed columns.

CREATE UNIQUE NONCLUSTERED INDEX IX_Employees_Email
ON dbo.Employees (Email);

Unique indexes automatically create a UNIQUE constraint if none exists.

XML indexes improve the performance of queries on XML data types.

CREATE PRIMARY XML INDEX IX_Documents_XML
ON dbo.Documents (XmlColumn);

Secondary XML indexes (PATH, VALUE, PROPERTY) can further optimize specific query patterns.