SQL table partition on same criteria as clustered index

Does it make sense to partition a table on the same criteria as the clustered index? For example, I have a table with a timestamp field. this timestamp is what the clustered index is based on and I want to partition the table by that timestamp into years.

Does it make sense to do that or should I change the clust开发者_StackOverflow中文版ered index to some other criteria (essentially like having two clustered indexes)?

There is no real problem with partitioning on the clustered index.

It is common for an incrementing clustered index to be used, such that new data only go into the last partition.

You cannot have a clustered index partitioned in one way and the table partitioned another way. This is simply because the clustered index is the table. If you create a table (a heap) on partition scheme A and then you build a clustered index on it on a partition scheme B then in the end you will have a table on partition scheme B. You simply cannot have both a table (a heap) and a clustered index. They are mutually exclusive.

A legitimate question is whether you can have non-clustered index on different partitions than the clustered index. Technically yes, you can, but this creates so called 'unaligned indexes', which are problematic from two points of view:

• they prevent partition switch operations, see Transferring Data Efficiently by Using Partition Switching
• they cause performance problems see Memory Limitations and Partitioned Indexes