Today I was to execute a ALTER TABLE… SWITCH TO PARTITION… operation to SWITCH an unpartitioned table into a partitioned table. I had taken care of the normal documented prerequisites:

• Same schema for both tables


• CHECK CONSTRAINT on the unpartitioned table to ensure values which match the destination partition function range

Despite these, I I ran into the following error:

Msg 4982, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints of source table ‘foodb.dbo.unparttable’ allow values that are not allowed by range defined by partition 5 on target table ‘foodb.dbo.parttable’.

After some troubleshooting, it was apparent that the issue was due to the following note mentioned in the BOL topic ‘Transferring Data Efficiently by Using Partition Switching’: 

We recommend that you specify NOT NULL on the partitioning column of partitioned tables, and also nonpartitioned tables that are sources or targets for ALTER TABLE…SWITCH operations. By doing this, you make sure that any CHECK constraints on partitioning columns do not have to check for null values. Null values are ordinarily placed in the leftmost partition of a partitioned table. When you are switching any partition other than the leftmost partition and when the ANSI_NULLS database option is set to ON, the absence of a NOT NULL constraint on the source and target tables might interfere with any CHECK constraints that are also defined on the partitioning column.

Little wonder – in my case, I did not have a NOT NULL for the partitioning column. After correcting that, the ALTER TABLE SWITCH TO PARTITION worked just fine!