Index Types

Question

Index Types

Viewing 7 posts - 31 through 36 (of 36 total)

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

WayneS SSC Guru Points: 95409 More actions · Answer 1

For everyone that thinks that the BOL reference in the QotD is wrong and that there are just 5 types of indexes: I created a connect item at https://connect.microsoft.com/SQLServer/feedback/details/577460/documentation-types-of-indexes to fix the documentation. Please go vote on it.

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

cengland0 SSCertifiable Points: 6102 More actions · Answer 2

WayneS (7/22/2010)
For everyone that thinks that the BOL reference in the QotD is wrong and that there are just 5 types of indexes: I created a connect item at https://connect.microsoft.com/SQLServer/feedback/details/577460/documentation-types-of-indexes to fix the documentation. Please go vote on it.

That's wonderful. So if it gets approved and the BOL is updated, do I get my point that I lost for this QOTD?

WayneS SSC Guru Points: 95409 More actions · Answer 3

cengland0 (7/22/2010)
WayneS (7/22/2010)
For everyone that thinks that the BOL reference in the QotD is wrong and that there are just 5 types of indexes: I created a connect item at https://connect.microsoft.com/SQLServer/feedback/details/577460/documentation-types-of-indexes to fix the documentation. Please go vote on it.
That's wonderful. So if it gets approved and the BOL is updated, do I get my point that I lost for this QOTD?

No... so far you've already gotten three for the posts in the discussion.:w00t:

Edit: But, if you go vote for it and it does get approved, you get the satisfaction of knowing you helped change something.

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

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 4

I've added my vote as well and will drop it on Twitter to scrounge for votes.

magasvs SSCertifiable Points: 7660 More actions · Answer 5

I have added my vote as well.

It would be confusing to get this question on interview, is it? I wouldn't know what is the right answer, probably would give two answers 🙂

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 6

magasvs (7/24/2010)
It would be confusing to get this question on interview, is it? I wouldn't know what is the right answer, probably would give two answers 🙂

I would consider it as an excellent occasion to show off my knowledge of all the various indexes SQL Server uses. I'd probably talk for at least five minutes, cover most (but not all - I'm bound to forget some) index types, never really answer the actual question but still show how much I know about the subject.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 7

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 answered 5 as well and I was quite surprised.

Thanks for the QotD anyway.

Always a great reason to learn something new. 🙂

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA