Index Types

  • 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

  • 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]

  • 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

  • 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

  • Would missing and unused make it 10?:)

    Hrvoje Piasevoli

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

  • 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

  • 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

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

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

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

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

  • 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

  • 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

  • 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

  • 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

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

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