02 August,2012 by Jack Vamvas
Question : What is an Index Key? Is the definition different for SQL Server Clustered and NonClustered Indexes ? Is there a maximum size of Index keys?
Answer: The indexed value is the index key. For example, in a NonClustered Index comprised of last names in a telephone directory, the last names values are the index keys. Rethinking SQL Server Indexes is central to using them effectively
An index may have more than one field as a key, such as last name and middle name.
An index may have an Include column – but these values are not part of the index key. See details below of maximum bytes and maximum columns
Two types of SQL Server Indexes exist: 1) Clustered Index 2) NonClustered Index
Indexes use a binary tree (B-tree) format. Three levels exist: a) Root b) Intermediate c) Leaf.
Root is the top node . Leaf node is the bottom level. Any levels in-between Root and Leaf are called Intermediate. In the Clustered Index , the Root and Intermediate levels contain the index pages holding index rows , with the index rows containing the key value and pointer to another intermediate page or a leaf level row. In the NonClustered Index , the leaf level contains index pages as opposed to data pages.
In summary : Clustered Indexes Leaf level contains the data. Index Key Values are used to locate the data. NonClustered Indexes leaf level contains the address (pointer) to the actual data. Pointers are used to locate the actual data
In SQL Server 2008:
Maximum NonClustered Indexes per table = 999
Maximum Clustered Index per table = 1
Maximum columns per index key = 16
Maximum bytes per index key = 900
Use Include nonkey columns in nonclustered indexes to overcome maximum limitations for NonClustered Indexes.