Partitioning is a powerful tool in SQL Server that allows for efficient data movement in and out of tables. However, when dealing with nullable partitioning columns, additional considerations need to be taken into account.

By default, NULL values in a partitioning column are placed in the left-most partition, unless a specific partition for NULL values is defined and a RIGHT partition is used. This behavior can complicate the management of partitioned tables, especially when performing data switches.

Let’s take a look at a simple example to understand how NULL values are handled in a partitioned table:

-- Set up the partition function and scheme
CREATE PARTITION FUNCTION PartWithNullsPF (int)
AS RANGE RIGHT FOR VALUES (1,2,3);
GO

CREATE PARTITION SCHEME PartWithNullsPS
AS PARTITION PartWithNullsPF
ALL TO ([PRIMARY]);
GO

-- Create the table 
CREATE TABLE PartWithNullsTab (Col1 INT NULL, Col2 varchar(10) NULL)
ON PartWithNullsPS (Col1);
GO

-- Insert some test values across the full range of possibilities
INSERT INTO PartWithNullsTab 
VALUES (0,'abc'), (1, 'def'),
(2, 'nop'), (3, 'qrs'),
(4, 'tuv'), (NULL, 'wxy');
GO

-- Query the partitioned table
SELECT $Partition.PartWithNullsPF(Col1) AS Partition, *
FROM PartWithNullsTab
ORDER BY Partition, Col1;
GO

In this example, the NULL value is placed in the left-most partition (#1), as expected.

Now, let’s consider the scenario of performing a data switch with a nullable partitioning column. When attempting to switch data into a table with a nullable partitioning column, you may encounter an error if the source data contains NULL values that are not allowed in the target partition.

To resolve this issue, you need to ensure that the constraint on the target table includes the condition “ColName IS NOT NULL”. This ensures that NULL values are not allowed in the table being switched in.

Here’s an example of how to modify the constraint for the swap table:

-- Remove old swap table
DROP TABLE PartWithNullsTab_Part;
GO

-- Create swap table with updated constraint
CREATE TABLE PartWithNullsTab_Part (Col1 INT NULL, Col2 varchar(10) NULL,
CONSTRAINT ck_PartWithNullsTab_Part CHECK (Col1>=3 AND Col1 IS NOT NULL));
GO

-- Load swap table with data
INSERT INTO PartWithNullsTab_Part VALUES (3,'ABC'), (4,'DEF');
GO

-- Perform the data switch
ALTER TABLE PartWithNullsTab_Part SWITCH
TO PartWithNullsTab PARTITION 4;
GO

By adding the “Col1 IS NOT NULL” condition to the constraint, we ensure that no NULL values are present in the swap table, allowing the data switch to be successful.

It’s important to note that the same consideration applies when initially loading data into a partitioned table. The constraint on the swap table should not include the “IS NOT NULL” condition, as the data being swapped out may contain NULL values.

Here’s an example of how to perform the initial data load:

-- Reload the data 
INSERT INTO PartWithNullsTab 
VALUES (0,'abc'), (1, 'def'),
(2, 'nop'), (3, 'qrs'),
(4, 'tuv'), (NULL, 'wxy');
GO

-- Drop and re-create the swap table without the IS NOT NULL condition
-- Remove old swap table
DROP TABLE PartWithNullsTab_Part;
GO

CREATE TABLE PartWithNullsTab_Part (Col1 INT NULL, Col2 varchar(10) NULL,
CONSTRAINT ck_PartWithNullsTab_Part CHECK (Col1>=3));
GO

-- Perform the data switch
ALTER TABLE PartWithNullsTab SWITCH PARTITION 4
TO PartWithNullsTab_Part;
GO

By following these guidelines, you can effectively manage nullable partitioning columns and ensure successful data switches in SQL Server.

Remember, understanding the behavior of NULL values in partitioning and properly configuring constraints is crucial for maintaining data integrity and optimizing performance in your SQL Server environment.

Happy partitioning!