CODEWITHSUNDEEP
sql-serverpartitioningsql-server-2016clustered-indexcolumnstore
crazyavengers
crazyavengers

Reputation: 81

Alter partition function and scheme when a columnstore table is in same database

I have a table which is having weekly partitioned with partition function and scheme defined. The most important thing is this table is having clustered columnstore index with same weekly partition scheme.

So now I have to add few more ranges in partition function and scheme. Which is failing with error saying “cannot alter partition function which is having non empty partition ......... “ where in the data file is of only 4KB with no data loaded.

From one of the post of 2014 Ssms, I came to know that we need to disable clustered index and alter the partition scheme and enable again.

Please help in solving this issue. I’m using 2016 sql and enterprise edition. Thanks in advance.

Upvotes: 0

Views: 1363

Answers (1)

Anton
Anton

Reputation: 2882

For columnstore index you need to empty the partition that is going to be split. That can be done by:

  • moving the data to other partition (by updating its partition key)
  • altering Partition Schema (with NEXT USED clause) and Partition function (with SPLIT RANGE clause)
  • moving the data back to correct partition.

Above can be done in one transaction.

For the future, (assuming the data is partitioned by date periods) it's recommended to have a few empty partitions, so a maintenance task/job can automatically split the partitions (and create a few new partitions for future periods) without any issues.

Alternatively you can use ALTER TABLE with SWITCH PARTITION clause, but that approach is less efficient. SWITCH PARTITION is mostly used to quickly delete the old partitions.

Upvotes: 2

Related Questions