Unable to SWITCH IN a partitioned table

Question

I have created two tables one is OrdersLatest and another is OrdersMarch. I want to switch data from the partitioned table to the staging table(OrdersMarch) on the same filegroup and vice versa.

It was working well for switching out but issue on switch in. My case SWITCH IN not working

It shows the below error which is indicating a constraint mismatch between the source and target table(OrdersMarch). I am not sure what should I change to solve this error.

I have checked in both the left and right partitioned, but the issue is the same. I set constraints on the staging table (OrdersMarch) on the partitioned column also but the error is the same. Anyone can solve this issue, where is the exact issue?

ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'TEST.dbo.OrdersMarch' allows values that are not allowed by check constraints or partition function on target table 'TEST.dbo.OrdersLatest'.

Partition Function

CREATE PARTITION FUNCTION OrdersLatestPartitionFunction (datetime)  
    AS RANGE RIGHT FOR VALUES (
        '20180101', 
        '20190101',
        '20200101'
    );
GO

Partition Scheme

CREATE PARTITION SCHEME OrdersLatestPartitionScheme
    AS PARTITION OrdersLatestPartitionFunction  
    TO (
        OrdersLatestFg1,
        OrdersLatestFg2,
        OrdersLatestFg3,
        OrdersLatestFg4
        );  
GO

Create Table

CREATE TABLE OrdersLatest (
    OrderDate date NOT NULL,
    OrderId int IDENTITY NOT NULL,
    OrderDesc varchar(255) NOT NULL,
    CONSTRAINT PKOrdersLatest PRIMARY KEY CLUSTERED(OrderDate, OrderId)
    )  
    ON OrdersLatestPartitionScheme(OrderDate);  
GO

Switch Target Table

CREATE TABLE OrdersMarch (
    OrderDate date NOT NULL,
    OrderId int IDENTITY NOT NULL,
    OrderDesc varchar(255) NOT NULL,
    CONSTRAINT PKOrdersMarch PRIMARY KEY CLUSTERED(OrderDate, OrderId)
    )
    ON OrdersLatestFg3;
GO

Data Insert

INSERT INTO OrdersLatest(OrderDate, OrderDesc) VALUES
    ('20190302', 'Cat food'),
    ('20190315', 'Water bowl'),
    ('20190318', 'Saddle for camel'),
    ('20190321', 'Dog biscuits'),
    ('20190328', 'Bigfoot shoes');
GO

Output After Insert

+-------------+-----------------+--------+
| Partition   | Filegroup       | Rows   |
|-------------+-----------------+--------|
| 1           | OrdersLatestFg1 | 0      |
| 2           | OrdersLatestFg2 | 0      |
| 3           | OrdersLatestFg3 | 5      |
| 4           | OrdersLatestFg4 | 0      |
+-------------+-----------------+--------+

Switch Out

ALTER TABLE OrdersLatest SWITCH PARTITION 3 TO OrdersMarch;

Output After Switch

+-------------+-----------------+--------+
| Partition   | Filegroup       | Rows   |
|-------------+-----------------+--------|
| 1           | OrdersLatestFg1 | 0      |
| 2           | OrdersLatestFg2 | 0      |
| 3           | OrdersLatestFg3 | 0      |
| 4           | OrdersLatestFg4 | 0      |
+-------------+-----------------+--------+

Switch In

ALTER TABLE OrdersMarch SWITCH TO OrdersLatest PARTITION 3; 

ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'TEST.dbo.OrdersMarch' allows values that are not allowed by check constraints or partition function on target table 'TEST.dbo.OrdersLatest'.

Answer 1

SQL Server requires source data to be within the boundaries of target partition in order to SWITCH data into a partitioned table. This can be guaranteed in advance with one of these methods as the error message suggests.

Method 1:

Create a check constraint on the non-partitioned source table to match (or be a subset of) the target partition boundaries. The check constraint for partition 3 of your RANGE RIGHT scenario is:

ALTER TABLE OrdersMarch
ADD CONSTRAINT CK_OrdersMarch_OrderDate
CHECK (OrderDate >= '20190101' AND OrderDate < '20200101');

This check constraint will allow you to switch the OrdersMarch table into OrdersLatest partition 3.

Note that a check constraint on the staging table is mandatory only when the staging table is switched into the main table partition; it is not required when you switch a partition into the staging table but, if one exists, the check constraint expression must be the same (or a superset) of the source partition boundaries. Be mindful of this if you use the same staging table for different partitions. A different check constraint expression is needed for each partition. I find method 2 easier since it avoids the need to (re)create check constraints on the staging table.

Method 2:

Partition the staging table with the same (or similar) partition function/scheme such that the source table partition boundaries are the same (or subset) of the target partition. This also guarantees the source and target partitions are storage aligned, another partitioning prerequisite for SWITCH.

CREATE TABLE OrdersMarch (
    OrderDate datetime NOT NULL,
    OrderId int IDENTITY NOT NULL,
    OrderDesc varchar(255) NOT NULL,
    CONSTRAINT PKOrdersMarch PRIMARY KEY CLUSTERED(OrderDate, OrderId)
) ON OrdersLatestPartitionScheme(OrderDate);

The data can then be switched into the target partition by specifying the source partition number too:

ALTER TABLE OrdersMarch SWITCH PARTITION 3 TO OrdersLatest PARTITION 3;

Note that I changed the OrderDate column data type from date to datetime in this example to match your partition function. The partitioning column data type must match that of the partition function so it seems there is a typo in your question DDL.