non clustered index strategy?

Question

Post reply

non clustered index strategy?

Jeff W

SSCertifiable

Points: 5207
More actions
April 21, 2004 at 12:22 pm

#85186
I am in the process of choosing indexes on a table. I had a thought about non-clustered indexes on a table with a clustered index that I am not sure of the answer too. Maybe some guru out there knows the answer to this??
Say we have an Employee table as...........
Employee
- ID
- Name
- Dept
- Address
With the following index........
- PK_EMPLOYEE on (ID) Clustered
Now say there is a query that runs frequently with ID and Dept in the WHERE clause. It seems that a composite index on ID and Dept would be appropriate. Right? This is where it got fuzzy for me. The leaf node in a non-clustered index (on a table that is clustered) contains the key value for the index and the key value in the clustered index. Instead of having a pointer to the data page there is a reference to the clustered index.
So here's my question.....Would it be equally as effective or even more effective to use a single column index on Dept than creating the composite index? The leaf node should still contain a reference to the key value in the clustered index, in this case ID. Does the query optimizer take into account that the key value for the clustered index exists in the non-clustered indexes leaf node?
Any thoughts, opinions, and random musings are welcome!!
Jeff

"Keep Your Stick On the Ice" ..Red Green

Viewing 8 posts - 1 through 7 (of 7 total)

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

Johan Bijnens SSC Guru Points: 135368 More actions · Answer 1

afaik (my guideline)

if the keyvalue(-combination) of your clustered ix is unique, it is designed to be the same.

if not, sqlserver will add a uniquifier to the clustering-index-key(s).

But in this case, you'll need a pk_index, which should be on the most narrow unique item (i.e. [id])

Keep your clustered index key(set) as short as possible, because it will be added to all other indexes of that table.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Angela But Hall of Fame Points: 3848 More actions · Answer 2

Hi Jeff,

Had a simalar situation here and looked at both combinations. After reviewing the execution plans, it appears that although there is a clustered index on the PK, it would not use the combination of this PK with the index on (in your example) Dept.

If you had a clustered index on you PK, and created another index containing ID and Dept, it would use this index when querying based on these two criteria.

This being the case you would need to create your PK and a covering index for ID & Dept to get the results you expect in a resonable amount of time and in the most efficient manner.

I hope this makes sense, it sounds a bit confusing now I've read what I just wrote. You may want to test it out for you own peace of mind.

Cheers,

Angela

twolfinger SSC Enthusiast Points: 106 More actions · Answer 3

Jeff,

Keep in mind while Angela's response is correct for a smaller table, it may not be correct as your table grows. Keep in mind that the non-cluster will do a table scan as opposed to a clustered index which would scans more efficiently. If your table is large, the clustered index on ID and non-clustered index on Dept. may be more efficient as it may take less time for SQL Server to search for the Clustered index value and then find the pointer reference than it would to table scan a large table to find the composite non-clustered pointer. As with all indexing, it is a constant evaluation. You will need to run performance monitoring on both solutions and then re-assess them as your table / db grows. Hope this helps.

mssql_rules SSCrazy Points: 2783 More actions · Answer 4

I have no answer to the central question. But, in this case would n't it be better to make the clustered index on the ID and DeptID? The question of narrow clustered indexes comes into picture when you have non-clustered indexes. Here even if your clustered index is wider, that is still eliminating the non-c index altogether and since the clustered index is part of the table itself does not have a large impact.

I understand that does not address the core issue of whether or not you can leverage your clustered index in the non-clus index. But several articles refer to index intersection, is not this the same thing conceptually?

Michael R Schmidt Mr or Mrs. 500 Points: 532 More actions · Answer 5

Ok, I must ask, why would you ever query on ID and Dept if ID is unique? If I am selecting a record and I use a where with a clustered unique index I will get to teh record as quickly as possible, if I try to use more data, I would hope the query optimizer would just filter out the "noise", find the right record and then check it for the rest of the qualifiers. But back to the original question, what wuld be the reason for selecting data with more qualifiers than is absolutely necessary?

Michael R. Schmidt
Developer

Johan Bijnens SSC Guru Points: 135368 More actions · Answer 6

rule n° 1 on every rdbms : tell your engine what you know