There’s a lot of confusion around duplicate indexes and I think a lot of it has to do with how sp_helpindex (or even SSMS) display what’s in an index. What’s actually in the index – and how it’s structured – are not always what they seem. This was the original motivation behind my rewrite of sp_helpindex but even since then, I’ve still seen a lot of confusion. In today’s blog post I’m going to first explain EXACTLY which indexes are the same and which aren’t – as well as the faults in the tools.<\/p>\n
So, starting with index structures… (it all starts with internals <\/em>:) )<\/p>\n The clustered index IS the data. The KEY to the clustered index (what I often refer to as the clustering key<\/em>) defines the way that the data is ordered (not necessarily truly, physically ordered on disk – but ordered LOGICALLY). And, no, I’m not going to rehash every bit of internals here… just a few reminders.<\/em><\/p>\n A nonclustered index is duplicated data (similar to an index in the back of a book). This duplicated data can be used to help reference the actual data (exactly like an index in the back of a book) OR can be used to actually respond to requests (for example, if you’re solely looking for a count of people with a name that begins with ” then an index that has LastName in the index could be used to count them – without actually “looking up” the actual data). So, there are some very powerful uses to indexes. But, alas, this isn’t a post on using indexes or indexing strategies – this is ALL about internals (and understanding the structure of an index).<\/em> So, I’m going to cut to the chase!<\/p>\n A nonclustered index always<\/span> <\/strong>has:<\/p>\n *So, what is this lookup value<\/em><\/strong>?<\/p>\n The lookup value is what SQL Server uses to reference the actual data row. If a table has a clustered index then the lookup value is the clustering key (and EVERY column defined by it). If a table does not have a clustered index (it’s then a HEAP) then SQL Server uses something called a RID. A RID is an 8-byte structure consisting of 2:4:2 bytes which breakdown into 2 for the FileID, 4 for the PageID and 2 for the slot number. And while RID’s (and their history) are very interesting – it’s not entirely relevant here (how they specifically work and\/or their structures) but if\/when they’re in an index, I’ll list is as RID.<\/p>\n Let’s now put all of this together with an example (or two) starting with a relatively easy one.<\/p>\n The output shows:<\/p>\n And, while that looks <\/em>right, it’s horribly misleading. The index that’s on TestName also includes TestID in the index as well. And, not just in the leaf level but in the tree (for ordering purposes). So, that should really show Name, TestID. But, it’s a bit more confusing if you throw in this:<\/p>\n The output shows:<\/p>\n And, at this point, it doesn’t look like there’s any difference at all between the second and third indexes (well, except that the third index requires that the values be unique – shown in the description). But, as for the “index_keys” they look the same. However, they’re actually not the same (in all parts of the tree). So, this is why I tend to differentiate between the “leaf” and the non-leaf levels of an index (when I describe them). And, it only gets more complicated when you throw in included columns (2005+).<\/p>\n\n
\r\nUSE [JunkDB];\r\nGO\r\n\r\nCREATE TABLE Test\r\n(\r\n TestID int identity,\r\n [Name] char(16)\r\n);\r\nGO\r\n\r\nCREATE UNIQUE CLUSTERED INDEX TestCL ON Test (TestID);\r\nGO\r\n\r\nCREATE INDEX TestName ON Test([Name]);\r\nGO\r\n\r\nsp_helpindex Test\r\nGO\r\n<\/pre>\n
\r\nindex_name index_description index_keys\r\nTestCL clustered, unique located on PRIMARY TestID\r\nTestName nonclustered located on PRIMARY Name\r\n<\/pre>\n
\r\nCREATE UNIQUE INDEX TestNameUnique ON Test([Name]);\r\nGO\r\n\r\nEXECUTE sp_helpindex Test;\r\nGO\r\n<\/pre>\n
\r\nindex_name index_description index_keys\r\nTestCL clustered, unique located on PRIMARY TestID\r\nTestName nonclustered located on PRIMARY Name\r\nTestNameUnique nonclustered, unique located on PRIMARY Name\r\n<\/pre>\n