Post reply

Add column which is part of a clustered index to non clustered index

  • October 15, 2013 at 8:58 am

    #299192

    Would it make sense to include a column that is part of a clustered index in the definition of a non clustered index, given that the clustered index is added by default to a non clustered index ?

    For example

    I have a Table Orders which has the following clustered index

    CREATE CLUSTERED INDEX CIX_Orders_OrderNo ON dbo.Orders(OrderNo)

    Would it make sense do something like this

    CREATE NONCLUSTERED INDEX IX_OrderDateOrderNo ON dbo.Orders(OrderDate,OrderNo)

  • October 15, 2013 at 9:50 am

    #1658593

    It might. Depends on what exactly and why.

    My general rule - if a column is needed in a nonclustered index to support a query and it's part of the clustered index, explicitly specify that column is part of the nonclustered index.

    Main reason for that rule - lack of future surprises. If you don't explicitly specify it, you're risking nasty surprises when either that nonclustered index changes or someone changes the clustered index to another column.

    Now if the column doesn't need to be part of the nonclustered index to support queries, don't put it explicitly into them.

    Your example makes sense if you have queries of the form

    WHERE OrderDate = @SomeDate and OrderNo = @OrderNo

    or

    WHERE OrderDate = @SomeDate

    or

    WHERE OrderDate > @SomeDate

    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

  • October 15, 2013 at 4:41 pm

    #1658750

    Thanks GilaMonster, makes sense to avoid future surprises.

    Would be interesting to see the non clustered index column definition inclusive of the clustered index ... From my understanding the index would then look like

    OrderDate,OrderNo,OrderNo

    The Last OrderNo, coming from the addition of the clusteredIndex to the NC Index

    Anyway I can see this ?

  • October 15, 2013 at 9:05 pm

    #1658777

    Gerard Silveira (10/15/2013)

    Would be interesting to see the non clustered index column definition inclusive of the clustered index ... From my understanding the index would then look like

    OrderDate,OrderNo,OrderNo

    No. What possible reason would SQL have for adding the column a second time? It would be a monumental waste of space and incredibly inefficient. SQL's not that stupid.

    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 4 posts - 1 through 3 (of 3 total)

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