What column to use for Cluster index

  • I have a table with over 10 million rows, it has all the PaymentHistory. It has ID as identity column, and EMPID with duplicate emp numbers. All the query, [joins], [where clause] are using EMPID. Which column should be created for CLUSTERED index? pls help thans in advance:cool:

  • Generally, you'll get the most benefit of a (clustered) index if you add columns which are most used in your where clause.

    In your case EMPLID. But Clustered indexes have to be unique, which is not in your case.

    But, if you create a clustered index on a non-unique field, SQL will add a column to make this index unique.

    So you have to compare the extra diskspace against the performance benefits.

    You could also consider partitioning. A history table is a perfect candidate for that feature.

    Wilfred
    The best things in life are the simple things

  • Probably you can use database Engine tunning advisor and/or use Show Plan to find our more.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • A history table that isn't mostly queried by date?

    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
  • Wilfred van Dijk (10/3/2008)


    Generally, you'll get the most benefit of a (clustered) index if you add columns which are most used in your where clause.

    In your case EMPLID. But Clustered indexes have to be unique, which is not in your case.

    But, if you create a clustered index on a non-unique field, SQL will add a column to make this index unique.

    So you have to compare the extra diskspace against the performance benefits.

    You could also consider partitioning. A history table is a perfect candidate for that feature.

    Not necessarily - your clustered index should always* be monotinic. If as an extreme example, you're writing a Db for a web facing app using GUIDS even though you're going to be using the GUIDS for lookups and queries you DO NOT use them for your clustered index as the random nature of the GUID means that the new records can end up going anywhere in your table. This can lead to huge performance issues, page splits, pagelocks as everything is reordered in the table to place the row in the right physical position. You'd probably use insert datetime and cover the lookup using a covering index

    * Well, no rules hold fast all the time. Sure someone'll think of a situation where this wouldn't be the case I'm sure

  • Andrew Gothard (10/3/2008)


    * Well, no rules hold fast all the time. Sure someone'll think of a situation where this wouldn't be the case I'm sure

    Depends how fast the table changes. If inserts only happen occasionally (an employees table, or something like that), then the main reason for clustering on an ascending key (fragmentation) isn't that applicable.

    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

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

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