I have a partitioned table that contains approx. 16 billion rows. The table has a clustered columnstore index and one partition aligned non-clustered index. The partitions are all on the same filegroup. The table is partitioned by day. I plan to run a weekly process that truncates partitions containing data older than 90 days. The process also removes the partition (using a partition function merge range stmt). During a given weekly run, there may be approx. 500 million rows truncated across multiple partitions. Should I be rebuilding the indexes or updating stats after truncating that much data?

On SQL Server 2017

CodePudding user response：

No need to rebuild indexes after the purge. A partition truncate and merge removes both data and storage from the table. The remaining partitions are unaffected by the purge operation and thus don't need rebuilding afterwards.

You probably don't need to bother update statistics for the clustered columnstore index unless you've created column statistics. The column segment value metadata, used for rowgroup elimination, is also removed by the purge.

Updating statistics on the non-clustered b-tree index after the purge is a good idea since there is only one stats blob per index. Stats will otherwise stale after the purge.