Clustered Indexes

There can be only one clustered index per table because, while the upper parts of the clustered index B-tree structure are organized like the nonclustered index B-tree structures, the bottom level of the clustered index B-tree consists of the actual 8-KB data pages associated with the table. There are performance implications:

• Retrieval of SQL data based on key search with a clustered index requires no bookmark lookup (and a likely nonsequential change of location on the hard disk) to get to the associated data page, because the leaf level of the clustered index is already the associated data page.
• The leaf level of the clustered index is sorted by the columns that comprise the clustered index. Because the leaf level of the clustered index contains the actual 8-KB data pages of the table, the row data of the entire table is physically arranged on the disk drive in the order determined by the clustered index. This provides a potential I/O performance advantage when fetching a significant number of rows from tables greater than 64-KB based on the value of the clustered index, because sequential disk I/O is being used unless page splitting is occurring on this table. For more information about page-splitting, see "FILLFACTOR and PAD_INDEX" later in this chapter. You should pick the clustered index on a table based on a column that is used to perform range scans to retrieve a large number of rows.