Partitioning experience

As you know since SQL Server 2005 MS introduced partitioning feature. I have been using it frequently with my clients but yesterday ,answering question on MSDN forum about partition I learned something new. Let say we have a partition table and staging table with the data and check constraint. See the below script.

create database db1
go
use db1
go


create partition function pf1(int) as range left for values (2,4)
create partition scheme ps1 as partition pf1 all to ([primary])

create table db1.dbo.t1 (id int ,descr varchar(2)) on ps1(id)
create table db1.dbo.t2 (id int,descr varchar(2))
alter table db1.dbo.t2 add check (id > 9)


create nonclustered index i1 on db1.dbo.t1(id)
create nonclustered index i2 on db1.dbo.t2(id)

insert into db1.dbo.t1 values (1,'n1')
insert into db1.dbo.t1 values(2,'n2')
insert into db1.dbo.t1 values(3,'N3')
insert into db1.dbo.t2 values (10,'n1')
insert into db1.dbo.t2 values(15,'n2')

---Add new range
alter partition function pf1() split range (9)

---Moving the data from t2 to t1 into a new added range
alter table db1.dbo.t2 switch to db1.dbo.t1 partition 4

Msg 4972, Level 16, State 1, Line 2
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'db1.dbo.t2' allows values that are not allowed by check constraints or partition function on target table 'db1.dbo.t1'.

I got the error, but everything should be ok. Both tables have the same datatypes,indexes,lengths.. What is going on here. I spend some time and found the below explanation in the BOL

/*
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.
*/
So altering CHECK constraint with id IS NOT NULL solved the problem..

alter table db1.dbo.t2 add check (id > 9 AND id is not null)