Microsoft SQL Server 2012 Internals

Chapter 9. Special indexes

Bob Beauchemin

SQL Server indexes, as discussed in Chapter 7 are B-tree indexes. The leaf node level of a B-tree always contains columns and rows, where the actual “data” for the index resides. The key range information that’s used for index key traversal is stored above the leaf level in the intermediate and root nodes. Looking at just the leaf nodes, you can divide the data columns into key columns and non-key columns. In a clustered index, the clustering key is the key column(s), and the rest of the data columns in the table are non-key columns. In a nonclustered index definition, the key columns appear after the table in the CREATE INDEX statement. Every nonclustered index also contains a copy of the clustering key, ...