There is a Dedicated SQL Pool in which the existing tables are using clustered columnstore index.
You are planning for partition splitting to improve the performance following your partition strategy.
You run the ALTER function on the first two tables, but they were not successful.
Which of the options tells about the possible reasons for this failure? (Multiple choice)
Click on the arrows to vote for the correct answer
A. B. C. D.Correct Answer: A and B.
There are certain limitations while using ALTER PARTITION STATEMENT for partition splitting of the table that already has data and using clustered columnstore indexing.
You will see the following ERROR:
“ALTER PARTITION statement failed because the partition is not empty.
Only empty partitions can be split in when a columnstore index exists on the table.
Consider disabling the columnstore index before issuing the ALTER PARTITION statement; then rebuilding the columnstore index after ALTER PARTITION is complete.”
The partition split will need the specific partition to be empty.
It is not mandatory that the whole table should be empty.
Also, in the case of clustered columnstore, the CTAS may not work if you have data in the partition.
Options A and B are correct: Clustered columnstore containing data cannot be partition split.
Also, if a Partition contains data, the data should be split first.
But this works only if your table is not using clustered columnstore indexing.
Options C and Dare incorrect: Even if CTAS statement is used, it will be failing since the scenario tells that the tables are columnstore indexed.
But they are not a mandatory rule that the entire table must be empty for partition splitting.
In Dedicated SQL Pool, clustered columnstore index is a column-based storage format that improves the compression and query performance of data warehouse tables. The index groups data into column segments and compresses them. Partitioning is a data organization technique that divides a table into smaller, more manageable segments to improve query performance.
When partitioning a table that contains a clustered columnstore index, there are some limitations to keep in mind. Here are the options provided in the question:
A. Clustered columnstore containing data cannot be partition splitted.
This option is not entirely accurate. It is possible to split a partition that contains a clustered columnstore index with data. However, there are some restrictions on how this can be done. For example, the index cannot be rebuilt using the ALTER INDEX statement. Instead, a CREATE TABLE AS SELECT (CTAS) statement can be used to create a new table with the desired partitioning scheme.
B. Partition which is getting splitted should be empty while using ALTER statement.
This option is incorrect. It is not necessary for the partition being split to be empty before running the ALTER statement. However, any changes made to the partition may cause data movement and affect query performance.
C. Clustered columnstore index with data can be splitted using CTAS statement only.
This option is partially correct. As mentioned earlier, it is possible to split a partition that contains a clustered columnstore index using a CTAS statement. This statement creates a new table with the desired partitioning scheme and data is copied over from the original table. However, it is also possible to use the ALTER PARTITION FUNCTION statement to split a partition.
D. The entire table must be empty before running partition splitting in case of clustered columnstore index.
This option is incorrect. It is not necessary for the entire table to be empty before running partition splitting. However, as mentioned earlier, any changes made to the partition may cause data movement and affect query performance.
In summary, the correct answer to the question is C. When partitioning a table that contains a clustered columnstore index with data, a CTAS statement can be used to split the partition. However, it is also possible to use the ALTER PARTITION FUNCTION statement. It is not necessary for the partition being split to be empty, and it is not necessary for the entire table to be empty.