Partitions and Clustered Columnstore Indexes

Hi @ekw9 can you please share screenshots of the table settings for the relevant tables? Which version of SQL Server are you using?

Hi Christian, 

Thank you for your reply! We are using SQL Server 2019 Enterprise, although we will be upgrading to SQL Server 2022 (and upgrading TimeXtender to the most recent legacy version as well) very shortly. 

Screenshots follow below. Allow me to correct myself - the error messages do not specify that SQL Server does not allow CCIs and partitions to be used together, but it does stop them from being used together regardless. 

Below, please find the following images: table settings, the error when you first create a partition and then create a CCI, and the error when you create a CCI but then attempt to create a partition. The tables that we are working with are query tables, but the issue affects normal tables as well. 

Hi Emily

I got this from the developers.

The reason it is not allowed is because of the way TimeXtender do partitioning. We create a computed column called "DW_PartitionKey" when partition is added to a table.

If you after deploying the table with partitioning try to apply the clustered column stored index to the table in eg. SSMS, SQL Server will error with:

The statement failed because column 'DW_PartitionKey' on table 'YourTableName' is a computed column. Columnstore index cannot include a computed column implicitly or explicitly.

 

Hi Thomas,

Thank you and the development staff for your response. 

To clarify, you are saying that we cannot use a CCI and partitioning at the same time - a common, important combination of SQL Server features for handling large data sets - because of a quirk in how that feature was implemented in TimeXtender. Is that accurate? 

If that is the case, is there any estimate on when this issue might be fixed? 

Hi Emily

You are right about it being about how the partition is made. If I remember there is another thing where it does not split out the partitions across filegroups, which is another reason for doing this.

Essentially this features main use is for doing OLAP incremental load.

I would suggest you to make a idea for a change of this, so it continues to benefit executions, queries and so.

Hi @ekw9 

I have converted this to an idea

Thank you Christian!

I believe this is an important feature for TimeXtender to support. In a world where data is getting bigger and bigger every year, a product like TimeXtender should allow developers to use every tool available to make the data easier to query and transform.