Post reply

Nonclustered Indexes

  • December 6, 2010 at 9:24 pm

    #388377

    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!

  • December 6, 2010 at 11:36 pm

    #1259388

    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 🙂

  • December 6, 2010 at 11:43 pm

    #1259391

    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

  • December 7, 2010 at 12:28 am

    #1259402

    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

  • December 7, 2010 at 12:29 am

    #1259403

    Straight forward question. Thanks

    Thanks

  • December 7, 2010 at 12:34 am

    #1259405

    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

  • December 7, 2010 at 1:18 am

    #1259413

    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

  • December 7, 2010 at 2:29 am

    #1259419

    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!

  • December 7, 2010 at 2:36 am

    #1259422

    Good question, thanks.

    Further info on size limits can be found here

    Nigel
    How to post questions

  • December 7, 2010 at 3:22 am

    #1259433

    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

  • December 7, 2010 at 3:41 am

    #1259445

    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

  • December 7, 2010 at 6:28 am

    #1259491

    Nice, straight-forward question. Thanks!

  • December 7, 2010 at 6:45 am

    #1259503

    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

  • December 7, 2010 at 8:36 am

    #1259578

    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.

    😎

  • December 7, 2010 at 9:24 am

    #1259608

    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 23 total)

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