Partition table's switch condition 2:partition the implicit check constraint and nullability

Partition column allows Null,null to be the minimum value, which exists in Partition Partition number=1.

Any data with a NULL in the partition column would reside in the leftmost partition. NULL is considered smaller than the minimum value of the data type ' s values.

The Partition Function defines the value range for Partition column in each partition, which, like every Partition data, has a check constraint that is used to qualify the Partition The range of values for the column.

Partition number=1,partition column allows null to exist;

Other partition,partition column is not allowed to exist in null.

In a check constraint, the Unknown,check constraint is considered true if it appears. For example constraint ck__tablename_id check (id>1 and id<10), if id=null, then the expression id>1 and id<10 returns unknown (or Null), The result of a CHECK constraint logical operation is true, that is, the check constraint is not violated.

1, create instance data

--Create Parition functionCREATEPARTITIONFUNCTIONPf_int_left (int) asRANGE Left  for VALUES(Ten, -);--create PARTITION SchemeCREATEPARTITION SCHEME ps_int_left asPARTITION Pf_int_left to([Primary],[Primary],[Primary]);--Create partitioned TableCreate Tabledbo.dt_partition (IDint, Codeint) onps_int_left (ID)--Create Staging TableCreate Tabledbo.dt_switchstaging (IDint, Codeint) on [Primary]--insert data into dbo.dt_partitionInsert  intodbo.dt_partition (Id,code)Values( the,2),() 

2, switch the data from partition 2 to Dbo.dt_switchstaging

-- Swith partition 2 to Staging table Alter Table  2 to Dbo.dt_switchstaging

Swith the data in staging table into partition 2

-- switch Staging table to partition 2 Alter Table Dbo.dt_switchstagingswitch  to 2

MSG 4982, Level A, State 1, line 2
ALTER TABLE SWITCH statement failed. Check constraints of source table ' DB_Study.dbo.dt_SwitchStaging ' allow values that is not allowed by range defined by PA Rtition 2 on target table ' DB_Study.dbo.dt_partition '.

To increase the CHECK constraint for staging table, the value range for partition 2 is id>10 and id<=20.

-- Add Check Constraint Alter Table dbo.dt_switchstaging  with Check Add constraint Check > Ten  and ID<=)

Execution of the Swith code, still error, error message and the same as above, the error reason is to ignore the partition 2 partition column cannot be empty constraints.

-- Add Check Constraint Alter Table dbo.dt_switchstaging  with Check Add constraint Check > Ten  and ID<=and is notnull)

3,partition number=1 allow parition column to be null

--Empty Staging Tabletruncate Tabledbo.dt_switchstagingGo--drop ConstraintAlter Tabledbo.dt_switchstagingDrop constraintck__dt_switchstaging_idGoAlter TableDbo.dt_partitionswitch Partition1 todbo.dt_switchstagingGo--Add Check ConstraintAlter Tabledbo.dt_switchstaging with CheckAdd constraintck__dt_switchstaging_idCheck(ID<=Ten )Go--Insert NULLInsert  intodbo.dt_switchstaging (Id,code)Values(NULL,1)Go--switch Staging table to partition 1Alter TableDbo.dt_switchstagingswitch toDbo.dt_partition partition1Go

Execution succeeds, the check constraint created allows the ID to be null, and partition column is also allowed to be null.

Partition table's switch condition 2:partition the implicit check constraint and nullability