Index Types

Question

Post reply

Index Types

WayneS

SSC Guru

Points: 95460
More actions
July 21, 2010 at 8:45 pm

#223521

Comments posted to this topic are about the item Index Types
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Viewing 15 posts - 1 through 15 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Very nice question ... thanks Wayne

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 2

Thanks for the question. I'd not come across filtered indexes before, so it's only 7:30am and I've already learnt something new today. Can't ask for much more than that!

Duncan

michael.kaufmann SSCrazy Points: 2816 More actions · Answer 3

Wayne,

first of all thank you for compiling a QotD.

However, I do not quite agree with the solution.

'Unique' is just taking the definition of a clusterd or non-clustered index a step further.

'with included columns' and 'filtered' again are non-clustered indexes (with advanced features).

So I don't see them as individual types, but sub-types at best.

Hence it comes down to 5 types (why I got it wrong):

- clustered

- non-clustered

- full-text

- spatial

- XML

I assume, though, that this is another wording issue ;-); nevertheless an excellent lesson having to recap what index types there are.

Thanks,

Michael

hrvoje.piasevoli Ten Centuries Points: 1377 More actions · Answer 4

Would missing and unused make it 10?:)

Hrvoje Piasevoli

cengland0 SSCertifiable Points: 6102 More actions · Answer 5

michael.kaufmann (7/22/2010)
Wayne,
first of all thank you for compiling a QotD.
However, I do not quite agree with the solution.
'Unique' is just taking the definition of a clusterd or non-clustered index a step further.
'with included columns' and 'filtered' again are non-clustered indexes (with advanced features).
So I don't see them as individual types, but sub-types at best.
Hence it comes down to 5 types (why I got it wrong):
- clustered
- non-clustered
- full-text
- spatial
- XML
I assume, though, that this is another wording issue ;-); nevertheless an excellent lesson having to recap what index types there are.
Thanks,
Michael

I selected 5 as well and got it wrong. This question came up on another QOTD and there were disagreements on that one too.

The "Unique" index one is still a clustered or non-clustered type so it shouldn't be counted twice.

The "Index with included columns" is a non-clustered type.

The "Filtered" is a non-clustered type.

Taking the 8 listed, minus the 3 duplicates leaves 5.

If you want to argue this further, what about adding indexed views? What about Fragmented indexes (Not good but they do exist on tables updated frequently).

WayneS SSC Guru Points: 95460 More actions · Answer 6

hrvoje.piasevoli (7/22/2010)
Would missing and unused make it 10?:)

Thanks for the early morning chuckle! Yes, you are right about this. 😀

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 7

I would agree that Unique, filtered and included indexes are a sub-type of the clustered (for Unique) or non-clustered index types. However, according to the latest Microsoft documentation, they are a type of index. I'm not going to base a QotD off of what I believe it should be without any documentation to back me up.

You might want to take a look at SQL 2008 (R1) BOL for index types at http://msdn.microsoft.com/en-us/library/ms175049.aspx%28sql.100%29 - here they list 9 (they included Indexed Views). Since an Indexed View is a type of a view, physically manifested by having a clustered index and optionally having non-clustered indexes, I believe that it is correct that it was removed from this list.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

george sibbald SSC Guru Points: 104210 More actions · Answer 8

just to get my point back I would have to agree some of the index types MS list are really properties of an index, but hey-ho, I guess you can say a unique index is 'different' to a non-unique one.

---------------------------------------------------------------------

cengland0 SSCertifiable Points: 6102 More actions · Answer 9

WayneS (7/22/2010)
I would agree that Unique, filtered and included indexes are a sub-type of the clustered (for Unique) or non-clustered index types. However, according to the latest Microsoft documentation, they are a type of index. I'm not going to base a QotD off of what I believe it should be without any documentation to back me up.
You might want to take a look at SQL 2008 (R1) BOL for index types at http://msdn.microsoft.com/en-us/library/ms175049.aspx%28sql.100%29 - here they list 9 (they included Indexed Views). Since an Indexed View is a type of a view, physically manifested by having a clustered index and optionally having non-clustered indexes, I believe that it is correct that it was removed from this list.

I don't subscribe to the logic that if it's on BOL, it must be true. If you want to include sub index types, you could include Ascending and Descending. The list could go on and on.

OCTom SSChampion Points: 11785 More actions · Answer 10

I knew it had to be more than two and thought it was less than 10. So, I stabbed at it with eight and got it right. 😛 It made me review indexes and that's a good thing.

sjimmo SSChampion Points: 11139 More actions · Answer 11

I must add my own .02 to this thread. I to come up with the correct answer being wrong. Using the reference given, I see that some of the indexes identified as correct answers are truely modifiers of the basic index types. How can these be acceptable index types?

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

sjimmo SSChampion Points: 11139 More actions · Answer 12

Also, to use Paul Randall's method from a couple of weeks ago - this is for my point I lost

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

magasvs SSCertifiable Points: 7660 More actions · Answer 13

I new I will get incorrect answer 🙂

I based my answer on sys.indexes "type" column:

Type of index:

0 = Heap

1 = Clustered

2 = Nonclustered

3 = XML

4 = Spatial

gchornenkyy Say Hey Kid Points: 687 More actions · Answer 14

michael.kaufmann (7/22/2010)
Wayne,
first of all thank you for compiling a QotD.
However, I do not quite agree with the solution.
'Unique' is just taking the definition of a clusterd or non-clustered index a step further.
'with included columns' and 'filtered' again are non-clustered indexes (with advanced features).
So I don't see them as individual types, but sub-types at best.
Hence it comes down to 5 types (why I got it wrong):
- clustered
- non-clustered
- full-text
- spatial
- XML
I assume, though, that this is another wording issue ;-); nevertheless an excellent lesson having to recap what index types there are.
Thanks,
Michael

And XML index may be

- Primary

- Secondary

And secondary XML index can be

- for Path

- for Value

- for Property

As for me the question is not formalized properly