Index Creation and included columns

  • Gerard Silveira

    Ten Centuries

    Points: 1106

    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 ?

  • Jack Corbett

    SSC Guru

    Points: 184359

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • Gerard Silveira

    Ten Centuries

    Points: 1106

    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 ?

  • Jack Corbett

    SSC Guru

    Points: 184359

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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
  • Jeff Moden

    SSC Guru

    Points: 994238

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Gerard Silveira

    Ten Centuries

    Points: 1106

    Thanks Jack and Gail

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

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