April 28, 2014 at 12:49 am
Hello, I am confused with the clustered key in nonclustered indexes. Lets say if I have a table with 10 columns: ID, Col1, Col1,..., Col9 . There is already a clustered index on column ID. When I create a nonclustered index for a query like SELECT ID, Col1, Col2 FROM MyTable where ID = xxx and Col1 = yyy should I include both ID and Col1 in the nonclustered index or just Col1 only?
Thanks.
April 28, 2014 at 5:43 am
If the index needs the clustering key to support the queries, explicitly add it. Don't rely on implicit behaviour.
So, an index for that query will be (Col1, ID) INCLUDE (Col2)
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
April 28, 2014 at 7:17 am
Gail, would you mind expanding on that? Would not the answer to this be 'it depends', and the only way to be sure is to see if the optimiser uses the clustered index or the NC to satisfy the query?
---------------------------------------------------------------------
April 28, 2014 at 8:07 am
george sibbald (4/28/2014)
Would not the answer to this be 'it depends'
No.
If you're creating an index and are trying to decide between explicitly putting the clustered index key in the index where necessary or letting SQL implicitly add it where it thinks it's necessary, be explicit.
If the index isn't used, then drop it, that's not what's in question here.
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
April 28, 2014 at 8:54 am
Hmmm, I read the question as
'as the clustered key is included in the leaf level of the non-clustered index do you need to add it to non-clustered indexes explicitly'
to which I would have to answer I don't know for sure without trying, are you saying you should add it? (but not as an included column)
---------------------------------------------------------------------
April 28, 2014 at 9:12 am
If you are creating an index to support a query and, to support that query the clustered index key is needed either as key or include column, then put it in the index where it is needed.
There shouldn't be a step of index creation which goes "Is that the clustered index key?". Treat columns which are part of the clustered index key no differently from any other column when deciding whether or not to add it to a nonclustered index.
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
April 28, 2014 at 10:44 am
Gail, is this for performance reasons or safety reasons (CI is changed or dropped), what about the redundancy and space considerations of the CI column(s) appearing twice?
---------------------------------------------------------------------
April 28, 2014 at 3:03 pm
Columns cannot be present twice in an index.
It's so that when someone changes a clustered index, your carefully crafted nonclustered index doesn't suddenly become useless. I don't like side effects, I don't like changes to one thing breaking something else and I don't like depending on implicit behaviour.
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
April 28, 2014 at 4:03 pm
Gail,
cheers
george
---------------------------------------------------------------------
April 28, 2014 at 5:20 pm
Thanks Gail and George.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply