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).