T-SQL Tuesday 118; My Fantasy SQL Server Feature

This month the T-SQL Tuesday is hosted by Kevin Chant (b | t).
The topic: My Fantasy SQL Server Feature.

Here's my wish: meta-data only merges for populated CCI partitions when the partition scheme fully aligns.

Imagine a table - CCI only with no nonclustered indexes, no PK - into which millions of rows are inserted every day.  The partition column is a datetime corresponding to the day the row was inserted.

It may make good sense to partition daily.  But as the data ages, there's likely to be a time when that partition size may as well be monthly. And after 2 years, might as well have yearly partitions.

Rather than physically moving data into newly defined larger partitions, I'd rather a meta-data operation to merge the 28-31 daily partitions for an old month into a monthly partition.  And a metadata only operation to merge 12 older monthly partitions into an yearly partition.

The older the data gets, the more time there's been to prune the rowgroups.  A meta-data only operation to combine the daily rowgroup partitions into a monthly partition reduces the number of partitions *and* keeps partition elimination on the datetime partitioning column at an equivalent level to what partition elimination was before.

Whew.  My shortest blog post in quite a while, I think. 😀