Nonclustered Indexes

  • rfr.ferrari

    SSCertifiable

    Points: 6879

    Comments posted to this topic are about the item Nonclustered Indexes


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • ziangij

    SSCertifiable

    Points: 7006

    Is the same number (16) applicable for a clustered index in SQL Server 2008 ?

    Checked the link, could not find if the above number is different in case of clustered index. Please comment. thanks 🙂

  • tommyh

    SSCertifiable

    Points: 6252

    ziangij (12/6/2010)


    Is the same number (16) applicable for a clustered index in SQL Server 2008 ?

    Checked the link, could not find if the above number is different in case of clustered index. Please comment. thanks 🙂

    Sort of yes. See http://msdn.microsoft.com/en-us/library/ms190197%28v=SQL.100%29.aspx

    /T

  • Hardy21

    SSCrazy Eights

    Points: 9708

    ziangij (12/6/2010)


    Is the same number (16) applicable for a clustered index in SQL Server 2008 ?

    Checked the link, could not find if the above number is different in case of clustered index. Please comment. thanks 🙂

    Yes it is.

    If you check "CREATE INDEX (Transact-SQL)" in BOL, it mentions that: " Up to 16 columns can be combined into a single composite index key. All the columns in a composite index key must be in the same table or view. The maximum allowable size of the combined index values is 900 bytes."

    Thanks

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Straight forward question. Thanks

    Thanks

  • tommyh

    SSCertifiable

    Points: 6252

    Hardy21 (12/7/2010)


    ziangij (12/6/2010)


    Is the same number (16) applicable for a clustered index in SQL Server 2008 ?

    Checked the link, could not find if the above number is different in case of clustered index. Please comment. thanks 🙂

    Yes it is.

    If you check "CREATE INDEX (Transact-SQL)" in BOL, it mentions that: " Up to 16 columns can be combined into a single composite index key. All the columns in a composite index key must be in the same table or view. The maximum allowable size of the combined index values is 900 bytes."

    Not entierly accurate. The clustered index have an exception to the 16 columns limit. It can be 15 in some cases. From the link in my previous post

    "Clustered index is limited to 15 columns if the table also contains a primary XML index or any spatial indexes."

    /T

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Nice and easy. Thanks!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • paul.knibbs

    SSCoach

    Points: 15270

    Why does the question specify SQL 2008 when the limit for this, AFAIK, was the same in earlier versions? Thought it might be a tricksy question about filtered indexes till I saw the answers!

  • nigel.

    SSChampion

    Points: 11593

    Good question, thanks.

    Further info on size limits can be found here

  • w.durkin@online.de

    Hall of Fame

    Points: 3943

    Slightly misleading wording. It should read "how many columns can be used in a nonclustered index key". The important part is to make clear that you are talking about the key columns of the index. Using the word "included" can be misinterpreted, especially since SQL 2005 where that is a keyword in indexing.

    Regards,

    WilliamD

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    WilliamD- (12/7/2010)


    Slightly misleading wording. It should read "how many columns can be used in a nonclustered index key". The important part is to make clear that you are talking about the key columns of the index. Using the word "included" can be misinterpreted, especially since SQL 2005 where that is a keyword in indexing.

    You are right, but the answers clearly stated that the question was about key columns. Furthermore, even if you did take in account included columns, no answer could have been correct.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Mattrick

    Ten Centuries

    Points: 1176

    Nice, straight-forward question. Thanks!

  • TomThomson

    SSC Guru

    Points: 104772

    Nice question, and easy to get the "right" answer given the choices available. A small niggle is that the genuinely right answer ("It depends: 16 key columns if the table has no xml indexes, 15 key columns if there are any xml indexes") wasn't an option, but that shouldn't have lead to anyone picking any of the thoroughly wrong options.

    (Incidentally, I regard the introduction of XML into SQL in the way it has been done as a horrible mistake, and allowing it to interfere with cluster key column count limit in this was is really stupid.)

    Tom

  • SanDroid

    SSChampion

    Points: 10068

    WilliamD- (12/7/2010)


    Slightly misleading wording. It should read "how many columns can be used in a nonclustered index key".

    Not certain what you mean, the correct answer was clearly "16 key columns".

    It was practicly cut and pasted from the BOL.

    Nice Question. Simple, but apparently people are learning something from it.

    😎

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Another phrasing problem with the question - the maximum size of an index is not 900 bytes. The maximum size of the key columns is 900 bytes. The index row can technically be up to 8000 bytes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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