Tuesday, September 10, 2019
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. 😀