Does every table need a clustered index?

  • 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.

  • 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
  • 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]

  • 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

  • Thanks everyone, very informative.

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

    Thanks again, you all rock.

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

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