the.komplikator (7/27/2011)
Little remark... All non-unique non-clustered indexes are created with a sysem internal unique id, 8 bytes in size, so that sql could differentiate each row. These id "columns" aren't visible or accessible.
Errr, no...
Non-unique clustered indexes get a uniquifier, which is 4 bytes (an int) added to rows that have duplicate values for the clustering key.
Nonclustered indexes get either the clustering key (complete with uniquifier) if the base table has a clustered index, or the 8-byte RID (file_id, page_id, slot_index) if the base table is a heap.
If the nonclustered index is unique, that's present only in the leaf level, if the nonclustered index is not unique that is present in the leaf and non-leaf levels.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability