Post reply

Index Creation and included columns

  • March 7, 2014 at 9:44 am

    #290733

    If I have the following table and a clustered index on OrderNo

    CREATE TABLE orders

    (OrderNo int,

    OrderDate datetime,

    selectedUser varchar(10)

    )

    When I create a non clustered index on SelectedUser, would the following indexes both be covering indexes

    CREATE INDEX ix_orders_selectedUser ON orders(selectedUser) INCLUDE (OrderDate,Orderno)

    CREATE INDEX ix_orders_selectedUser ON orders(selectedUser) INCLUDE (OrderDate)

    Since the Clustered index is always added as part of the Non clustered index ?

  • March 7, 2014 at 12:05 pm

    #1695213

    Yes. The difference is that the clustered index is not an included column, but is part of the index key so is not just at the leaf level like an included column.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • March 9, 2014 at 4:22 pm

    #1695397

    Thanks for your reply Jack , so that would mean that SQL Server would have to actually do a row lookup to return the value for the Orderno, and would not be able to use the value present within the index structure ?

  • March 10, 2014 at 8:50 am

    #1695570

    Gerard Silveira (3/9/2014)

    Thanks for your reply Jack , so that would mean that SQL Server would have to actually do a row lookup to return the value for the Orderno, and would not be able to use the value present within the index structure ?

    No. If you have a clustered index on orderNo, any index you create will include the clustering key as part of the key. So if you create an index on selectedUser the key values for the index will be:

    selectedUser + orderNo

    and the leaf level would be:

    selectedUser + orderNo + [included columns]

    So a query that just returns selectedUser and orderNo may not have to navigate all the way down to the leaf level to return the data.

    Both of the indexes you propose are covering indexes for any query that includes the 3 columns listed in the table, if there is a clustered index on orderNo. The only benefit to actually listing orderNo in the included list is that, if you ever change the clustered index, you still have a covering index for any queries that require those 3 columns.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • March 10, 2014 at 8:59 am

    #1695575

    Jack Corbett (3/10/2014)

    The only benefit to actually listing orderNo in the included list is that, if you ever change the clustered index, you still have a covering index for any queries that require those 3 columns.

    Which to my mind is a pretty good reason to explicitly reference the column, especially as there's no downside in doing so.

    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

  • March 10, 2014 at 5:48 pm

    #1695790

    GilaMonster (3/10/2014)

    Jack Corbett (3/10/2014)

    The only benefit to actually listing orderNo in the included list is that, if you ever change the clustered index, you still have a covering index for any queries that require those 3 columns.

    Which to my mind is a pretty good reason to explicitly reference the column, especially as there's no downside in doing so.

    +1000. I'm apparently the only one at work that also believes this.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 10, 2014 at 9:29 pm

    #1695802

    Thanks Jack and Gail

Viewing 7 posts - 1 through 6 (of 6 total)

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