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

Question

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

Gerard Silveira

Ten Centuries

Points: 1106
More actions
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)

Viewing 4 posts - 1 through 3 (of 3 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Gerard Silveira Ten Centuries Points: 1106 More actions · Answer 2

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 ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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