SQL Server Home
Documentation - Types of Indexes
7/22/2010 11:53:42 AM
User(s) can reproduce this bug
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.
SQL Server 2008 R2 - Developer Edition
Operating System Language
Steps to Reproduce
to post a comment.
Please enter a comment.
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.
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.
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.
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.
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.
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.
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.
to post a workaround.
Please enter a workaround.
© 2014 Microsoft