Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I have a clustered index on 'A', is it better to use this clustered index even if I have

selection of 'A=constant'? If not, why?

Thank you!

share|improve this question

migrated from stackoverflow.com Feb 2 '12 at 14:22

This question came from our site for professional and enthusiast programmers.

4  
Do you mean "non-clustered" index? And what RDBMS too please? –  gbn Feb 1 '12 at 16:04

2 Answers 2

up vote 2 down vote accepted

I just want to put in a word of warning: please very carefully pick your clustered index!

It's the most replicated data structure in your SQL Server database (assuming it's SQL Server you're talking about). The clustering key will be part of each and every non-clustered index on your table, too - certainly in the leaf level, possibly also in the index navigation structure.

You should use extreme care when picking a clustering key - it should be:

  • narrow (4 bytes ideal)

  • unique (it's the "row pointer" after all - if you don't make it unique, SQL Server will - for you - in the background - costs your a couple of bytes for each entry - times the number of rows and the number of nonclustered indices you have - can be very costly!)

  • static (never change - if possible)

  • ideally ever-increasing so you won't end up with horrible index fragmentation (a GUID is the total opposite of a good clustering key - for that particular reason)

  • it should be non-nullable and ideally also fixed with - a varchar(250) makes a very poor clustering key

Anything else should really be second and third level of importance behind these points ....

See some of Kimberly Tripp's (The Queen of Indexing) blog posts on the topic - anything she has written in her blog is absolutely invaluable - read it, digest it - live by it!

share|improve this answer
    
sqlskills.com/blogs/kimberly –  sam yi Feb 1 '12 at 17:24

Yes.

Actually, I'm not sure how you would not use the index in this case. The clustered index contains the actual row data. That's why there can only be one clustered index. All non-clustered indexes contain pointers to the actual data.

So using the clustered index saves you doing a lookup from the index to the data.

share|improve this answer
2  
Unless "covering" stackoverflow.com/a/1308012/27535 –  gbn Feb 1 '12 at 16:14
1  
IF the user is really talking about SQL Server ..... (I'm assuming so, too - just not really confirmed yet) –  marc_s Feb 1 '12 at 16:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.