Documentation - Types of Indexes - by Wayne Sheffield

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


14
1
Sign in
to vote
ID 577460 Comments
Status Closed Workarounds
Type Bug Repros 6
Opened 7/22/2010 11:53:42 AM
Access Restriction Public

Description

http://msdn.microsoft.com/en-us/library/ms175049.aspx lists the different types of indexes for SQL 2008 R2.
http://msdn.microsoft.com/en-us/library/ms175049.aspx(sql.100) provides the same list for SQL 2008 R1.

Unique is not a type of index - it is either a clustered or non-clustered index that has an additional property to prevent duplicate values.

Filtered is not a type of index - it is a non-clustered index on a subset of the data.

Included columns is not a type of index - it is a non-clustered index that has been extended to be able to hold non-key columns.

These three are all properties or attributes of other index types, and are not a separate index type.
Sign in to post a comment.
Posted by magasvs on 7/24/2010 at 10:10 AM
A little bit confusing to see indexes classification in BOL: http://msdn.microsoft.com/en-us/library/ms175049.aspx and in sys.indexes table (http://msdn.microsoft.com/en-us/library/ms173760.aspx). Both places have indexes types, but they don't match.
Posted by Microsoft on 7/23/2010 at 8:52 AM
Changed the last sentence of unique to read: Uniqueness can be a property of both clustered and nonclustered indexes.
Considered included columns and filtered descriptions. I think they already have adequate descriptions.
Posted by Microsoft on 7/23/2010 at 8:45 AM
Actually, I just noticed that there were 11 customer up vote counts. That has to mean something. I'll fix it.
Posted by Microsoft on 7/23/2010 at 8:37 AM
Not fixing unless we get indication that it's confusing the newbies. Not a bad suggestion, but in my judgement it would needlessly complicate the topic.
Posted by Microsoft on 7/23/2010 at 8:32 AM
I agree with Eomot. The use of the word type is more casual than scientific. The intended audience of people who need a definition of clustered and non-clustered, will want to know that you can create a unique index. It clearly says that both clustered and nonclustered can be unique. Subcategorizing by structure vs. properties is more likely to confuse than help people who need this page.
Posted by TomThomson on 7/22/2010 at 6:25 PM
If "type" of index needs a tight formal definition let us have one. I don't believe it does, and those who wish to play sqls trivia games about definitions of terms that don't really need ant definition should be allowed to sink of swim without MS support or any sort.
Posted by Wayne Sheffield on 7/22/2010 at 12:47 PM
I forgot to mention that elsewhere in BOL (http://technet.microsoft.com/en-us/library/ms181197.aspx, http://technet.microsoft.com/en-us/library/ms173760.aspx) it is specifically mentioned that the types of index are clustered, non-clustered, XML and spatial when referring to the sys.indexes table are creating an index. These links are obviously not about full-text indexes, which are covered separately.