Post reply

Included Indexes

  • December 15, 2015 at 10:54 pm

    #310267

    Comments posted to this topic are about the item Included Indexes

  • December 16, 2015 at 1:44 am

    #1846254

    Typo alert!

    Option 4 "I can include deterministic computed columns. "

    Explanation of why this is wrong "The computed columns that are deterministic can be included."

    Oops :blush:

  • December 16, 2015 at 2:45 am

    #1846259

    Nice question.

    The last answer is also correct though (but it's not a restriction).

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

  • December 16, 2015 at 3:44 am

    #1846267

    I guess the fourth option is missing the word "not" 🙂 - or maybe it was intended to say what it is and the explanation is wrong (in which case the correct explanation would be "...this is not a restriction, it's something allowed").

    Nice question despite that.

    As usual when multiple answers are required, some people have ignored that - 2 required out of 4, but the percentages of selections add up to 195%, ie 5% short, which is more than can be accounted by rounding down since there are only 4 options.

    Tom

  • December 16, 2015 at 3:47 am

    #1846268

    As all above me highlighted so i would leave it as is it.

    beside that excellent question. Thanks for sharing Steve.

  • December 16, 2015 at 6:06 am

    #1846278

    I don't understand why non-clustered is required. While not a best practice, why couldn't you include the non-key columns in the definition of the clustered index?

  • December 16, 2015 at 6:18 am

    #1846280

    tom.w.brannon (12/16/2015)

    I don't understand why non-clustered is required. While not a best practice, why couldn't you include the non-key columns in the definition of the clustered index?

    I'm thinking that clustered indexes by definition include all the non key columns 🙂

  • December 16, 2015 at 6:20 am

    #1846281

    Toreador (12/16/2015)

    Typo alert!

    Option 4 "I can include deterministic computed columns. "

    Explanation of why this is wrong "The computed columns that are deterministic can be included."

    Oops :blush:

    Yeah I picked that one because I thought including deterministic computed columns would work! But no big deal for me, I don't get many questions right here :hehe:

  • December 16, 2015 at 6:27 am

    #1846285

    patrickmcginnis59 10839 (12/16/2015)

    tom.w.brannon (12/16/2015)

    I don't understand why non-clustered is required. While not a best practice, why couldn't you include the non-key columns in the definition of the clustered index?

    I'm thinking that clustered indexes by definition include all the non key columns 🙂

    Thanks!

  • December 16, 2015 at 8:10 am

    #1846309

    I was thinking that since a clustered index includes all non-key columns and you want to include some non-key columns, you wouldn't need it to be a nonclustered index...

    Be still, and know that I am God - Psalm 46:10

  • December 16, 2015 at 8:13 am

    #1846310

    Last answer updated. I was thinking it wasn't a restriction, but I can see why this created confusion. I'll award back points to this time.

  • December 16, 2015 at 8:36 am

    #1846324

    patrickmcginnis59 10839 (12/16/2015)

    tom.w.brannon (12/16/2015)

    I don't understand why non-clustered is required. While not a best practice, why couldn't you include the non-key columns in the definition of the clustered index?

    I'm thinking that clustered indexes by definition include all the non key columns 🙂

    Yeah, option 1 really is NOT correct. You can include non-key columns in a clustered index -- in fact, you have no choice but to include non-key columns.

    If the question had said that you want to include some but not all columns in the index, then the index could not be clustered.

  • December 16, 2015 at 8:54 am

    #1846339

    #1 is correct.

    If I have a table and do this:

    CREATE CLUSTERED INDEX ix_mybigtable ON dbo.mybigtable (id, largechar) INCLUDE (largechar2)

    I get this:

    Msg 10601, Level 16, State 1, Line 1

    Cannot specify included columns for a clustered index.

    The questions is on included columns. However I can see the phrasing being poor, so I've altered this to say INCLUDE specifically.

  • December 16, 2015 at 1:10 pm

    #1846412

    I thought it was an excellent question. Then again, I think indexing is always a good topic. By the time I got to it, it was worded well.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • December 17, 2015 at 11:08 am

    #1846628

    Thanks for the question.

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

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