clustered index

Question

clustered index

programmer76

SSC-Addicted

Points: 427
More actions
March 15, 2011 at 10:18 am

#235162

Gurus,
I am working at a client site and all tables in their database do not have a clustered index. Instead of clustered index all the tables have a unique nonclustered index.
So to explain in detail they have a table called company with only one field company_name varchar(100) and it has only one record inside it representing name of the company. company_name is the part of primary key for all the remaining tables inside the database. So for example contact table will have a unique nonclustered index on contact_id and company_name column.
I believe this design is wrong and every table should have a clustered index. I am planning to change all the table design to put clustered index in it.
I have three questions.
(1) I want the confirmation that i'm on the right track and I should definitely change the table designs to put clustered indexes on all the tables.
(2)Going ahead with the same example for the contact table I will create a clustered index on the contact_id column only.All the queries inside the database are always refering to company_name whenever there is a where clause associated with it.Tempting up on this fact I could have created clustered index on composite columns contact_id and company_name but I am not doing it because company_name is always one value and as far as i believe you are supposed to create index only on columns which have enough selectivity. Please confirm that My approach is right and I should be creating a clustered index this way.
(3) After creating the clustered index in order to reduce the overhead of maintaining the index I was planning to get rid of unique nonclustered index. I believe that we don't need unique nonclustered index because clustered index will be taking care of whaterver part it was doing and there will be more overhead to maintain the not needed nonclustered index.Am i doing this right?
Pleaset let me know.
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

First, don't confuse clustered index with primary key. Not related in any way. By default the PK is clustered, but that's a setting. They have nothing to do with one another.

Before messing with PKs, be sure that you understand what they are used for. Potentially there is a reason to have the company_name. Doesn't appear to be the case from your description, but ask questions here.

Second, clustered indexes are good. In general, the system works better by having clustered indexes on tables. However the clustering key ought to be narrow and appropriate. The Contact_ID is likely a good candidate. Keep in mind that this key is in EVERY nonclustered index, so you want it narrow. This doesn't have to be the PK, however.

Having a nonclustered index that is the PK, or even potentially includes the contact_id is not necessarily bad. You want to limit indexes, but a scan of the NC index might be much quicker than a scan of the clustered index, especially if the table is wide.

I tend to look for nonclustered indexes that are used often in queries, but also are not too narrow. Lots of one column nonclustered indexes are not necessarily better than a few that have 3, 4, or 5 columns and might cover queries.

Indexing is an evolution. not a set it and forget it. You want to periodically look at the queries and performance, and slowly build or remove indexes that are useful for your particular workload.

Edit: corrected the "don't" as noted below.

WayneS SSC Guru Points: 95460 More actions · Answer 2

Steve Jones - SSC Editor (3/15/2011)
Keep in mind that this key is in EVERY nonclustered index, so you don't want it narrow.

Actually (and I believe you meant to say this), you DO want it as narrow as possible. For exactly the reason that you alluded to - since all of the clustered index key columns are included in all non-clustered indexes, it will add to the size of the non-clustered indexes. Having a wider clustered index key can make them really big.

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

Doh!, yes, edited to to say what I meant.

Narrow clustered key, not necessarily narrow non-clustered key.

programmer76 SSC-Addicted Points: 427 More actions · Answer 4

So conclusion is to create a clustered index only on contact_id column and get rid of nonclustered index.right?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

No, the conclusion is that a narrow clustered index is good, and that other non-clustered indexes, even on the same column, might help. However you have to look at the queries that are being run.

Indexes don't mean anything without knowing what queries are being run.