Index Architecture and Behavior - SQL Server Query Performance Tuning - page 132

When to Use a Clustered Index
In certain situations, using a clustered index is helpful. I discuss these situations in the sections that follow.
Accessing the Data Directly
With all the data stored on the leaf pages of a clustered index, any time you access the cluster, the data is immediately
available. One use for a clustered index is to support the most commonly used access path to the data. Any access of
the clustered index does not require any additional reads to retrieve the data, which means seeks or scans against the
clustered index do not require any additional reads to retrieve that data. This is another likely reason that Microsoft
has made the primary key a clustered index by default. Since the primary key is frequently the most likely means of
accessing data in a table, it serves well as a clustered index.
Just remember that the primary key being the clustered index is a default behavior but not necessarily the most
common access path to the data. This could be through foreign key constraints, alternate keys in the table, or other
columns. Plan and design the cluster with storage and access in mind, and you should be fine.
The clustered index works well as the primary path to the data only if you're accessing a considerable portion of
the data within a table. If, on the other hand, you're accessing small subsets of the data, you might be better off with
a nonclustered covering index. Also, you have to take into account the number and types of columns that define the
access path to the data. Since the key of a clustered index becomes the pointer for nonclustered indexes, excessively
wide clustered keys can seriously impact performance and storage for nonclustered indexes.
Retrieving Presorted Data
Clustered indexes are particularly efficient when the data retrieval needs to be sorted (a covering nonclustered index
is also useful for this). If you create a clustered index on the column or columns that you may need to sort by, then the
rows will be physically stored in that order, eliminating the overhead of sorting the data after it is retrieved.
Let's see this in action. Create a test table as follows:
IF (SELECT OBJECT_ID('od')
) IS NOT NULL
DROP TABLE dbo.od ;
GO
SELECT pod.*
INTO dbo.od
FROM Purchasing.PurchaseOrderDetail AS pod;
The new table od is created with data only. It doesn't have any indexes. You can verify the indexes on the table by
executing the following, which returns nothing:
EXEC sp_helpindex 'dbo.od';
To understand the use of a clustered index, fetch a large range of rows ordered on a certain column.
SELECT od.*
FROM dbo.od
WHERE od.ProductID BETWEEN 500 AND 510
ORDER BY od.ProductID;