Clustered Index Column In A Non-Clustered Index

Question

Clustered Index Column In A Non-Clustered Index

ImYourDBA

SSCertifiable

Points: 5654
More actions
May 4, 2010 at 2:26 pm

#143038

Is there any benefit to including the clustered column(s) in a non-clustered index with more fields?
For example:
Keeping it simple, I have a table Stats that has two columns, Stats and Value.
If I build a unique clustered index on Stats, is there any benefit to having a non-clustered index on Stats and Value if the queries that are run against it always pull both columns together?
I'm thinking yes, but want to be sure before I go using that theory and do it on other tables.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

Viewing 14 posts - 1 through 13 (of 13 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

SQLJocky (5/4/2010)
Is there any benefit to including the clustered column(s) in a non-clustered index with more fields?

On a table with a clustered index the clustered index key is already contained within the non-clustered index, so the short answer would be no.

For example:
Keeping it simple, I have a table Stats that has two columns, Stats and Value.
If I build a unique clustered index on Stats, is there any benefit to having a non-clustered index on Stats and Value if the queries that are run against it always pull both columns together?
I'm thinking yes, but want to be sure before I go using that theory and do it on other tables.

before doing anything like this test it out, compare execution plans and see which is best.

Your example is probably too simple, in this case the clustered index on its own would be good enough as it would likely be the index chosed by the optimiser anyway. A non clustered index containing both columns in the table would just be duplicating the table.

In a more real life situation including both columns you wanted to retrieve in the index and therefore making it a covering index will give good results.

---------------------------------------------------------------------

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 2

Well, I tried in our dev environment and it appears that the query used the non-clustered covering index over the unique clustered, however, both indexes provided the exact same statistics. So, optimizer chose the non-clustered but didn't do so because of any benefit gain.

Just thought I would share.

Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

george sibbald SSC Guru Points: 104210 More actions · Answer 3

thanks for sharing.

but of course you now have an index that offers no performance gain on selects but will need to be maintained on updates/inserts and double(?) the space used by the table. 🙂

---------------------------------------------------------------------

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 4

george sibbald (5/4/2010)
thanks for sharing.
but of course you now have an index that offers no performance gain on selects but will need to be maintained on updates/inserts and double(?) the space used by the table. 🙂

Sorry, I should have clarified. "Therefore I have not created the non-clustered index on the prod server".

hehe 😉

Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 5

SQLJocky (5/4/2010)
If I build a unique clustered index on Stats, is there any benefit to having a non-clustered index on Stats and Value if the queries that are run against it always pull both columns together?
I'm thinking yes, but want to be sure before I go using that theory and do it on other tables.

Yes there can be benefits to creating a nonclustered index on some or all of the same columns as a clustered one.

Firstly the nonclustered index will usually be smaller than the clustered one and therefore queries that don't need to do bookmark lookups will generally be faster against a nonclustered index than a clustered one. When a query is covered by the index the optimizer will actually choose the nonclustered index in preference to the clustered one in order to minimise the number of reads.

It's true that a nonclustered index always contains the cluster key columns but those columns will not be duplicated in the index if you include them in the nonclustered index key as well. It is only by including the columns in the index key itself that they can be used for index seeks.

george sibbald SSC Guru Points: 104210 More actions · Answer 6

I think we agree david that the important point is that the non-clustered index be covering.

---------------------------------------------------------------------

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

David Portas (5/4/2010)

SQLJocky (5/4/2010)
It's true that a nonclustered index always contains the cluster key columns

something new to me . Are you sure for it ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

george sibbald (5/4/2010)
I think we agree david that the important point is that the non-clustered index be covering.

thats true.above of all optimizer always choose that index which cost it less resources usage

see below example inspite of having clustered index, optimizer go for index seek (nonclustered)

create table #t ( id int identity , num nvarchar(300))

insert into #t(num)

select 'hdddjf' union

select 'hf' union

select 'nfyjf' union

select 'dddd' union

select 'ggggg' union

select 'rrrrr' union

select 'bbbbb'

create clustered index ix on #t(id)

select * from #t where id = 3 and num = 'ggggg'

---it will go for clustered index seek

create nonclustered index cix on #t(id, num)

Select * from #t where id = 3 and num = 'ggggg'

---NOw it will go for index seek(nonclustered)

drop table #t

selection of indexes also depend on amoount of data

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

george sibbald SSC Guru Points: 104210 More actions · Answer 9

Bhuvnesh (5/5/2010)

David Portas (5/4/2010)
SQLJocky (5/4/2010)
It's true that a nonclustered index always contains the cluster key columns
something new to me . Are you sure for it ?

http://msdn.microsoft.com/en-us/library/ms177484.aspx

or see BOL

---------------------------------------------------------------------

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 10

David Portas (5/4/2010)

SQLJocky (5/4/2010)
It's true that a nonclustered index always contains the cluster key columns

Here you are talking about "when the clustered index is not a unique index" . right ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 11

Bhuvnesh (5/5/2010)

David Portas (5/4/2010)
SQLJocky (5/4/2010)
It's true that a nonclustered index always contains the cluster key columns
Here you are talking about "when the clustered index is not a unique index" . right ?

No. A nonclustered index always contains the cluster key columns in the leaf pages of the nonclustered index. If the clustered index is not unique then the nonclustered index will also contain the additional "uniquifier" bytes so that the clustered index key can be used to locate each row. This is documented in Books Online and elsewhere.

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 12

David Portas (5/5/2010)

A nonclustered index always contains the cluster key columns in the leaf pages of the nonclustered index.

Correction: it could be in the leaf level or at a higher level. But the clustered index columns are always included.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 13

MY BAD, i need a strong coffee.:-)

I treated (misread ) "non clus " index as "clus" and viceversa :-D. thats why i asked this foolish question

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)