An index helps the SQL Server query optimizer find rows efficiently, but every index carries maintenance costs. Good index design begins with real workloads rather than indexing every column.
Clustered versus nonclustered
A clustered index establishes the table’s logical row-order structure and there can be only one per table. Nonclustered indexes provide separate lookup paths and may use INCLUDE columns to cover queries. SQL Server can also maintain heaps without a clustered index.
Composite key order
For an index on (CustomerID, OrderDate), equality predicates on CustomerID often benefit from the leftmost key. The textual order of predicates in the WHERE clause does not have to match the index column order.
Example
CREATE INDEX IX_Orders_Customer_Date
ON dbo.Orders (CustomerID, OrderDate DESC)
INCLUDE (TotalAmount);Choosing indexes
Prefer keys used by selective predicates, joins, and useful orderings. Evaluate filtered indexes, columnstore indexes, and primary/unique constraints for different workloads. Small tables may perform adequately with scans.
Check the trade-off
Compare reads and plans before and after index creation, then measure INSERT/UPDATE overhead, maintenance, disk space, and fragmentation/statistics behaviour. Avoid blindly forcing an index in every query.
AI, sources and reuse: This is an AI-generated rewrite informed by the original Open Education post (#69266), with additional explanations and examples. The new article text may be copied, adapted, and republished with or without a reference to this site. Third-party resources and trademarks keep their own rights. AI can make mistakes. Verify version support, code correctness, and security practices against Microsoft documentation, and test with nonproduction data first.
