Does every table need a clustered index?

Question

Does every table need a clustered index?

krypto69

SSChampion

Points: 13604
More actions
January 21, 2009 at 1:02 pm

#205712

I have optimized these two tables & queries and have almost no table/index scans. Odd thing is, I have yet to use a clustered index.
When I try adding a clustered index (I've tried many different columns)..I get no performance gains.
Is it possible that I'm better off having a set of non-clustereds, or am I doing something wrong by not finding a clustered? Is it possible that my non clustereds are hurting my (after the fact) choice of clustered?
~I know I haven't posted the table/queries, I certainly will if that will help. But thought this was a general enough question not to require them.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Unless you have a good reason not to, every table should have a clustered index.

Tests have shown that with a well-chosen clustering key, all operations are faster on the table (insert, update, delete, select). Badly chosen clustering keys will hinder all.

If all your queries are using covering nonclustered indexes, you won't see a perf gain with a cluster

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sankar Reddy SSCrazy Points: 2410 More actions · Answer 2

I added some notes about heaps and you may find that useful.

Back To Basics: Heaps

CI has to be choosen carefully as you can have only one per table. You don't want to put in a wide column as it uses too much index space. You want CI to be narrow.

Without further details, its hard to answer your question.

[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

krypto69 (1/21/2009)
Is it possible that my non clustereds are hurting my (after the fact) choice of clustered?

Generally speaking, no, non-clustered indexes aren't going to hurt your clustered index, but the clustered index could, if it's not a good choice, hurt the non-clustered indexes. This is because the clustered index stores the data of the tables and the non-clustered indexes have pointers to the clustered index. So those non-clustered indexes don't affect the cluster, but that pointer can make the non-clustered indexes bigger or less efficient.

That said, as Gail has already said, a well chosen clustered index makes everything faster. So, yes, unless testing proves an exception, you should have a clustered index on every table. Just pick it carefully.

"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

krypto69 SSChampion Points: 13604 More actions · Answer 4

Thanks everyone, very informative.

I meant to write back sooner, but have been busy with your suggestions.

Thanks again, you all rock.