8

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?

2
  • @JonSeigel, Primarily we will be hitting the table on a date/time range. We're partitioning by month, keeping 13 partitions at the moment. Commented Aug 16, 2012 at 16:20
  • I was wrong. We're using 12 partitions per year, up to 7 years = 84 partitions. Commented Aug 16, 2012 at 18:49

2 Answers 2

6

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.

2
  • 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. Commented Aug 16, 2012 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
    Commented Aug 16, 2012 at 19:30
-4

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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