March 15, 2011 at 10:18 am
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!
March 15, 2011 at 10:44 am
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 15, 2011 at 10:51 am
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
March 15, 2011 at 1:30 pm
Doh!, yes, edited to to say what I meant.
Narrow clustered key, not necessarily narrow non-clustered key.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 15, 2011 at 1:42 pm
So conclusion is to create a clustered index only on contact_id column and get rid of nonclustered index.right?
March 15, 2011 at 2:12 pm
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply