Overview

Indexes improve the speed of data retrieval operations on a SQL Server table at the cost of additional writes and storage. SQL Server supports several index types, including clustered, non‑clustered, unique, filtered, and columnstore indexes.

Creating a Simple Non‑Clustered Index

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON dbo.Orders (CustomerID);

Creating a Unique Index

CREATE UNIQUE NONCLUSTERED INDEX IX_Products_ProductCode
ON dbo.Products (ProductCode);

Creating a Filtered Index

CREATE NONCLUSTERED INDEX IX_Orders_Status
ON dbo.Orders (Status)
WHERE Status = 'Pending';

Best Practices

• Index columns used in WHERE, JOIN, ORDER BY, and GROUP BY clauses.
• Avoid excessive indexes; each index adds overhead on DML operations.
• Consider filtered indexes for sparse data.
• Regularly monitor index usage with sys.dm_db_index_usage_stats.