We have a table that is clustered on identity/datetime2. It is partitioned on the same datetime2. Are there any reasons to cluster on datetime2/identity instead? I understand the reasoning behind clustering generally, but with partitioning included, do things change?

  • 3
    Workload dependent. Are you hitting the table with date/time range-based queries? What is the current granularity of the partitioning (by day, by month, by year, etc.)? – Jon Seigel Aug 16 '12 at 1:51
  • @JonSeigel, Primarily we will be hitting the table on a date/time range. We're partitioning by month, keeping 13 partitions at the moment. – GaTechThomas Aug 16 '12 at 16:20
  • I was wrong. We're using 12 partitions per year, up to 7 years = 84 partitions. – GaTechThomas Aug 16 '12 at 18:49

Partitioning a table only divides it into "chunks" based on the partition function. The clustered index will give order to the data within each partition.

If you're planning to run queries that involve parts of a partition (i.e., show me sales between Jan 5th and Jan 12th), then it can be advantageous to those queries to have the date as the leading column of the clustering key. This type of structure will result in clustered index seeks, instead of partition scans. (Assuming there are no other suitable indexes on the table.)

If the queries only touch entire partitions, it doesn't matter as much, as partition elimination is enough to isolate the needed data. That said, ordering by date first could eliminate the need for an expensive sort operation, depending on what you're doing.

But this also depends on which columns you need from the table. If you only need to do something like aggregate total sales amounts over a date range, it may be sufficient to use a covering nonclustered index with the date as the leading (or only) column of the key, instead of rebuilding the entire table just for that.

If you do change the clustered index, this will affect singleton lookups (probably by the identity column which I assume is the primary key) as they will now involve a nonclustered index seek + key lookup. If this type of activity isn't a major part of the workload, this will be fine, but you have to be really careful that not too many rows are selected by these queries, or the optimizer will revert to a partition scan on the assumption that it's cheaper. Again, depending on which columns you need, it may be advantageous to create a covering nonclustered index that includes only the columns you need.

| improve this answer | |
  • Would having datetime first in the clustered index potentially cause reduced performance for the times in which the datetime values are identical? My understanding is that it is best to have a monotonically increasing clustered index, and situation in which dates are identical would potentially cause records to go into the database "out of order", so to speak. – GaTechThomas Aug 16 '12 at 18:55
  • @GaTechThomas: If the identity column is included in the clustered index key as the second column (which I would recommend doing), that solves the problem. – Jon Seigel Aug 16 '12 at 19:30
  • Very good point. – GaTechThomas Aug 16 '12 at 19:37

From a partitioning perspective there's no need to change the clustering key to datetime2/identity. It would be totally dependent on your queries.

| improve this answer | |
  • Why is everybody burying this answer? – datagod Aug 16 '12 at 18:39
  • 2
    I have to assume that it was from the yes/no answer with no detail provided before it was edited. – GaTechThomas Aug 17 '12 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.