A index is duplicated if its tree structure and usage are the same as another one on the same table.
sp-helpindex can't tell you if indexes are really the same on tree structure and usage.
(
Clustered index is the data.
Non-clustered index is duplicated data. These duplicated data helps SQL Server efficiently find the real data.
)
Non-Clustered index Structure:
. Key
. A leaf level entry(actual data stored in index+lookup values+included columns)
Here lookup values are either clustered index key if table has clustered index or RID if table is a heap. It's used to look for actual data row.
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.
For non-unique non-clustered index, clustered keys are stored in both its tree and leaf nodes.
For unique non-clustered index, clustered key are stored only in its leaf nodes.
(A nonunique nonclustered needs to have the lookup value pushed up into the tree (for navigation). A unique nonclustered index does not.)
Sequence of included columns does not matter on usage of index so that difference on the sequence will be ignored.
Credit to Kimberly. Understanding Duplicate Indexes
No comments:
Post a Comment