SQL Server Clustered Index

By: Armando Prato

Overview

We will likely want indexes to help queries. One of the most important indexing decisions is to determine which column(s) will make up the clustered index.

Explanation

You may or may not have read it before but I'll re-hash it here. You get only 1 clustered index per table since the clustered index determines the logical ordering of the data in the table and it makes up the actual table itself. Not having a clustered index should be conscious decision that should be considered carefully.

How can a clustered index help and why do you want one? If you're grouping, sorting, or range searching large sets of data, clustering on the column(s) involved in your query can considerably speed up the query and will consume fewer resources (i.e. CPU, memory). The data that makes up a clustered index is stored as a B tree structure with the data itself linked in doubly linked list fashion in clustered key order making it ideal for sorting, grouping and range queries.

Consider this example where we SELECT a range of rows from AdventureWorks table Production.TransactionHistory using the clustered, unique primary key on TransactionID:

set statistics io on
go
select * 
from Production.TransactionHistory
where TransactionID between 211000 and 211200
go

Examining the I/O counts we see that 6 pages of data needed to be read to satisfy the request

Now let's drop and re-create the primary key as a non-clustered one.

alter table Production.TransactionHistory     
drop constraint PK_TransactionHistory_TransactionID
go
alter table Production.TransactionHistory 
add constraint PK_TransactionHistory_TransactionID primary key nonclustered (TransactionID) 
go

Re-running the same query and re-examining the I/O counts we see that 205 pages of data are now needed to satisfy the same request

SQL Server automatically makes the primary key a clustered index (if not defined otherwise). The primary key does not have to be the clustered index of the table if other columns make more sense to support critical queries.

The choice of the clustered index is a balancing act between query performance vs. table health. Highly volatile tables (i.e. tables that experience a lot of inserts), can experience severe logical fragmentation. If your clustered index is on not on monotonically increasing data (i.e. an identity, sequentially increasing datetime, sequential uniqueidentifier) and the table is highly volatile, you will experience rapid fragmentation of the table which can result in extra I/O to retrieve the data to satisfy a query request.

If no columns make good candidates for a clustered index, I usually make an available monotonically increasing value the clustered index.

Additional Information

• Clustered Tables vs. Heap Tables
• SQL Server Tables Without A Clustered Index
• SQL Server Fragmentation Storage basics and Access Methods Part 1 of 9