Switch partition fails with Clustered Columnstore Index

Question

hi ,

I am trying to switch partition between un-partitioned table table_src and partitioned table table_trg. Both the table tables have Clustered Columnstore Index on these. Does this work? 

ALTER TABLE [dbo].[table_src] SWITCH  TO [dbo].[table_trg] PARTITION 2; 

The error seems to mislead, the partition 2 on target table is empty...

I am using Microsoft SQL Server 2014 - 12.0.2000.8 (X64)

Msg 4982, Level 16, State 1, Line 65

ALTER TABLE SWITCH statement failed. Check constraints of source table 'table_src' allow values that are not allowed by range defined by partition 2 on target table 'table_trg'.

---

Suman

Answer 1

It would help to have a full repro, but there is nothing in the message that seems misleading to me. That is, the message is not suggesting that there is any data in partition 2. What the message is saying is that the metadata for table_src permits data that violates the range for partition 2.

That is, if partition 2 is for values >= 20 and < 50, then the table you shift in needs to have a trusted CHECK constraint on the partioning column with this range.

ALTER TABLE SWITCH is intended to be a metadata operation. That is, it does not read data to see whether it fits the partition, but there must be a constraint to enforce it.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

In order to SWITCH from a non-partitioned table to a partitioned one, the source table must have a check constraint that matches (or is a subset of) the target partition boundaries.  Alternatively, you can simply partition the source table using the same partition scheme as the target table, and then SWITCH partition 2.

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com