Columns that contain both clustered and unclustered

Question

Columns that contain both clustered and unclustered

Duran

SSChampion

Points: 13259
More actions
April 15, 2010 at 7:52 am

#217880

Hello,
I have a question regarding indexing, is it good practice to have a clustered and non-clustered index on the same column? Is there a reason why a query would use one index and not the other. What reasons exit that would require this arrangement and is it recommended?
Kind regards,
D

Viewing 15 posts - 1 through 15 (of 20 total)

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

I have never heard of anyone recommending to have both clustered and non clustered on the same column. This is the first time.

-Roy

WayneS SSC Guru Points: 95461 More actions · Answer 2

Keep in mind that a clustered index IS the table, physically sorted by the key(s) of the index. A non-clustered index is just the key(s) of the index, and a pointer to the row by either the clustered index key(s), or the heap rowid.

As such, a non-clustered index will usually take up less space on disk.

So, if your query can be completely satisified by the columns in a non-clustered index, then it will be faster to use that (you can hold more records per page), than the clustered index.

That being said, you need to carefully choose your clustered index... it is not necessary the PK. And you should not have duplicate indexes.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

Wayne, The OP is talking about having a clustered and a non clustered on the same column....

-Roy

WayneS SSC Guru Points: 95461 More actions · Answer 4

Yes, I knew that. Everything I wrote applies with that concept.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

Ah.. Now I know what you mean.. I reread it and figured it out. But these are not so common cases...Right?

-Roy

WayneS SSC Guru Points: 95461 More actions · Answer 6

Roy, I'm sorry I wasn't clearer the first time around.:(

Not entirely sure what you're meaning with the not so common cases. You really shouldn't have a non-clustered index on the same keys as the clustered index... that creates extra work with doing inserts/updates (to the key)/deletes... two indexes need to be maintained instead of just the one. So it would definitely be a "not so common case" to have a non-clustered index on the same keys as the clustered index.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 7

Well, let us look at a scenario where there is a clustered index on column A. The table has 30 columns. If you have a query that selects just 3 columns, would it not be good to have an index on the same column with the OPTION of INCLUDE. Especially if the record size is larger?

PS : Edited record to record size

-Roy

WayneS SSC Guru Points: 95461 More actions · Answer 8

The optimizer would utilize the smaller index, since it knows it would have less disk IO to get the necessary information... and we all know that disk IO is THE bottleneck in all queries.

IMO, this is an "it depends" type of question. How large is the table? How many inserts/updates/deletes on it? How many queries against it? If I had a large, but infrequently modified table, sure, I'd throw a smaller index on it. But if this table is having lots of records being added/deleted, or any of the columns in the indexes updated, then I'd think twice about it.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

Duran (4/15/2010)
I have a question regarding indexing, is it good practice to have a clustered and non-clustered index on the same column?

i am taking your question other way. just refer the link posted here. you will get some good information

Blogged by gail shaw

http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/

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

jabadwy SSC Enthusiast Points: 126 More actions · Answer 10

We have prob over 30 indexes in total on our db where DTA has recommended using clustered index on covering index or as include - i am concerned that this is incorrect

To give a bit of context all of our database application tables has a clustered index of type guid - this is not changeable. A sample from our donation tbl we have 3 indexes:

1) clustered index on column id (guid)

2) nc index on col id with include on cols value, date_received

3) nc index on date_received with include on col id

The above have been produced as result of capturing queries through profiler and then run through DTA, for example when a user say opens the form for a contact record, it displays the related donation values, date received etc.

Can anyone shed any light on why DTA has suggested this?

Thanks in advance

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 11

jabadwy (4/16/2010)
We have prob over 30 indexes in total on our db where DTA has recommended using clustered index on covering index or as include - i am concerned that this is incorrect
To give a bit of context all of our database application tables has a clustered index of type guid - this is not changeable. A sample from our donation tbl we have 3 indexes:
1) clustered index on column id (guid)
2) nc index on col id with include on cols value, date_received

No, if the ID is a cluster, adding another column with ID as the key and any columns included is a total waste of time

3) nc index on date_received with include on col id

No, since the ID is the key for the clustered index, it's automatically included in any nonclustered index you create

The above have been produced as result of capturing queries through profiler and then run through DTA, for example when a user say opens the form for a contact record, it displays the related donation values, date received etc.
Can anyone shed any light on why DTA has suggested this?
Thanks in advance

The DTA is a very weak tool. You honestly can't rely on it. Of the three indexes above, obviously the ID index is good (although indexing GUID's has a whole slew of problems associated with it) and the 3rd index, without the INCLUDE statement could be good. The second index is a complete waste of space and time.

The clustered index, as was mentioned before, is the table. The key values of the cluster are used to create the index, but all the columns of the table are included at the leaf level. So this means that if the clustered index is used in a seek operation, no other operation is required to get at the other columns. A non-clustered index stores it's own key values AND the key value for the clustered index (part of why GUID's are so expensive as clustered indexes).

You have to look at suggestions by the DTA very closely. It's not a good tool for serious performance tuning or large scale systems.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 12

Apart from DTA, did you see the execution plan and found anything which disturbs the performance like table spool/lookup etc ?

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

jabadwy SSC Enthusiast Points: 126 More actions · Answer 13

I was looking in forums and came across this post.

Here Adi has made a point about size of nc index in comparison to clustered....which makes some sense to me - would this theory not be applicable to second index?

Thanks

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 14

jabadwy (4/19/2010)
I was looking in forums and came across this post.
Here Adi has made a point about size of nc index in comparison to clustered....which makes some sense to me - would this theory not be applicable to second index?
Thanks

I'm not sure what you mean after reading this. Are you asking if a second index could be smaller? Yes, it could be, but, that doesn't mean that it will get used by optimizer without forcing the index, which can be a very bad choice. In extreme circumstances, this might be a valid approach, but the vast majority of the time, I would not recommend it. Especially if the row size in the cluster is not outrageously large or the clustering key is not too big. It really depends on what's going on with the queries in question. I would not trust anything that the DTA recommended.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning