tl;dr; If you are SWITCHing data into a table and the partitioning column is nullable you will need to add AND ColName IS NOT NULL to the constraint of the table that holds the data you are SWITCHing in.

Partitioning is a really handy, if rather complex tool. It can be used for a lot of odd tasks but one of its primary uses is to move data in and out of a table quickly. We do that using a SWITCH command.

I’ve recently been working on a project doing just that. It’s a monthly archive of 200+ tables. I’m looping through all of the eligible tables and dynamically setting up a table to swap the archive data into. Then I move those tables holding the archive data into an archive database and swap the data back into the archive tables. Unfortunately, I don’t control the creation of the tables so I’m having to cover a rather large range of possibilities. Including the possibility that the partitioning column will allow NULLs. Now I’m not one of those people who thinks NULLs are completely evil (Thomas LaRock (b/t) for example) but they sure do complicate things. And with something as complicated as partitioning adding more is a royal pain.

So to start with how does partitioning handle a NULL? If you look in the BOL for the CREATE PARTITION FUNCTION you’ll see the following:

Any rows whose partitioning column has null values are placed in the left-most partition unless NULL is specified as a boundary value and RIGHT is indicated. In this case, the left-most partition is an empty partition, and NULL values are placed in the following partition.

So basically NULLs are going to end up in the left most partition(#1) unless you specifically make a partition for NULL and are using a RIGHT partition. So let’s start with a quick example of where NULL values are going to end up in a partitioned table (a simple version).

First we set up our test environment

-- 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. All of the named values and just 
-- outside of the upper and lower boundries and 
-- a null value of course.
INSERT INTO PartWithNullsTab 
		VALUES (0,'abc'), (1, 'def'),
				(2, 'nop'), (3, 'qrs'),
				(4, 'tuv'), (NULL, 'wxy');
GO

Now take a look at the results

SELECT $Partition.PartWithNullsPF(Col1) AS Partition, *
FROM PartWithNullsTab
ORDER BY Partition, Col1;
GO

You can see that the NULL value ended up in partition one as expected. Like I said, this was a pretty simple example and I’m not displaying the exception but you can consider trying that out your homework.

Now for the SWITCH itself. Here is an example of the code I started with to switch the data back into the archive database.

-- Remove existing data
TRUNCATE TABLE PartWithNullsTab;
GO
-- Create swap table
CREATE TABLE PartWithNullsTab_Part (Col1 INT NULL, Col2 varchar(10) NULL,
	CONSTRAINT ck_PartWithNullsTab_Part CHECK (Col1>=3) );
GO
-- Load swap table with data
INSERT INTO PartWithNullsTab_Part VALUES (3,'ABC'), (4,'DEF');
GO
-- Swap in data
ALTER TABLE PartWithNullsTab_Part SWITCH
	TO PartWithNullsTab PARTITION 4;
GO

Msg 4972, Level 16, State 1, Line 48
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table ‘Test.dbo.PartWithNullsTab_Part’ allows values that are not allowed by check constraints or partition function on target table ‘Test.dbo.PartWithNullsTab’.

This worked great when the column wasn’t nullable but now I get an error. I spent hours and hours trying to figure this one out. Finally I found this answer on StackExchange by the great Paul White (b/t). In it he reminded me how NULL works. My constraint checks if Col1>=3 and only restricts a value if it returns a False. But if Col1 is NULL then Col1>=3 evaluates to NULL and NULL is not False allowing the value into the table. So basically our swap table could easily have a NULL in it but that isn’t allowed in that partition. End result, all we need to do is:

-- Remove old swap table
DROP TABLE PartWithNullsTab_Part;
GO
-- Create swap table
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
-- Swap in data
ALTER TABLE PartWithNullsTab_Part SWITCH
	TO PartWithNullsTab PARTITION 4;
GO

See the IS NOT NULL? That makes sure that there are no NULLs in the table being swapped in. The possible values for both sides are the same so the SWITCH worked!

So how about swapping the data in in the first place? When I first started writing this post that was what I started with.

-- 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
-- Swap out the data
ALTER TABLE PartWithNullsTab SWITCH PARTITION 4
	TO PartWithNullsTab_Part;
GO

And this time it worked fine! I have to tell you this was even more frustrating than when it didn’t work in the first place! And one more time I spent hours trying to figure out what was wrong and ended up reaching out for help. This time from Hugo Kornelis (t), although to be fair I was grumbling a bit about NULLs and he offered to help me out (don’t you love this community?). Anyway, he ended up pointing out that when you swap data into a table the point of the constraint is to make sure that the data moving in is valid. So in the case of swapping the data out it looked like this:

PartWithNullsTab(3,4) -> ParWithNullsTab_Part(3,4,NULL)

So obviously that worked. All values of (3,4) will go into (3,4,NULL).

But when I was trying to put the data back in:

ParWithNullsTab_Part(3,4,NULL) -> PartWithNullsTab(3,4)

And it makes complete sense that this one doesn’t work. The “value” NULL obviously won’t go in that other dataset.

So there you go. The possible values of the partition to be swapped have to be a subset (or the full set) of the possible values in the partition being swapped into. Clear as mud?

So what does it mean to say that a value is NULL? Basically it means that the value is unknown. As an example, consider a binary variable. Its value can either be 1 or 0; but it can also be unknown (i.e. NULL). Pretty simple right? Well… on the surface maybe, but when you start thinking through the implications, it gets more and more complicated. For example:

DECLARE @Bin Binary
DECLARE @Bin2 Binary

SET @Bin = 1
SET @Bin2 = NULL

Here are the possible comparisons

• (@Bin = 1) returns True
• (@Bin = 0) returns False
• (@Bin = NULL) returns NULL
• (@Bin2 = 1) returns NULL
• (@Bin2 = 0) returns NULL
• (@Bin2 = NULL) returns NULL

 
Any value when compared to a NULL is NULL. Why? Well it’s kind of like Schrödinger’s Cat. Until a value is placed in it you don’t know what the value is. In fact, I like to mentally replace NULL with “unknown”:

• (@Bin = unknown) returns unknown
• (@Bin2 = 1) returns unknown
• (@Bin2 = 0) returns unknown
• (@Bin2 = unknown) returns unknown

 
Obviously, if a value is unknown, the result of any comparison is unknown. In other words NULL = NULL returns NULL.

Next example:

CREATE TABLE #NullTest (MyVal binary)
INSERT INTO #NullTest VALUES (1),(1),(0),(0),(NULL) 

SELECT COUNT(1) FROM #NullTest
WHERE MyVal = 1 -- Returns 2

SELECT COUNT(1) FROM #NullTest
WHERE MyVal = 0 -- Returns 2

SELECT COUNT(1) FROM #NullTest
WHERE MyVal = NULL -- Returns 0

So we get two rows where MyVal = 1, two rows where MyVal = 0 and no rows where MyVal = NULL for a grand total of four. But wait, we inserted five rows!! So as we go farther down the rabbit hole we start to realize that aggregate queries can be heavily affected by NULLs. The implications go farther and farther down the rabbit hole as you think about it. So how do we work around this? Well there are a couple of options.

ISNULL(MyVal,0) will return a 0 if MyVal is a NULL and return the value of MyVal otherwise. This leads to queries that look like this:

SELECT COUNT(1) FROM #NullTest
WHERE ISNULL(MyVal,1) = 1

I don’t recommend this for several reasons but the fact that it isn’t SARGable is probably sufficient.

Next we have the option “IS NULL”. Which is used like this

SELECT COUNT(1) FROM #NullTest
WHERE MyVal = 1
  OR MyVal IS NULL

This is generally the method I would use although you do have to watch out with your OR operator. I see a lot of logical mistakes when people use OR carelessly. Here is a simple example:

CREATE TABLE #NullTest (MyVal binary, DateVal datetime)
INSERT INTO #NullTest VALUES 
			(1, '1/1/1900'),
			(1, '1/1/1901'),
			(0, NULL),
			(0, '1/1/2001'),
			(NULL, '1/1/2000') 

-- What you meant
SELECT *
FROM #NullTest
WHERE 
	(MyVal = 1
	  OR MyVal IS NULL)
 AND 
	(DateVal > '1/1/1950'
	  OR DateVal IS NULL)

-- What you entered
SELECT *
FROM #NullTest
WHERE MyVal = 1
  OR MyVal IS NULL
 AND DateVal > '1/1/1950'
  OR DateVal IS NULL

-- How the computer saw it.
SELECT *
FROM #NullTest
WHERE 
	MyVal = 1
  OR 
	(MyVal IS NULL
	AND DateVal > '1/1/1950')
  OR 
	DateVal IS NULL

I’ll let you run it yourself if you want to see the different results, but just looking at the code you should be able to tell how easy it is to mess yourself up.

There are other methods of course, and I’m sure more will show up over time. The important thing to remember is that if you are going to allow NULLs in your columns then you need to understand what a NULL is and plan for it accordingly. If you don’t Thomas LaRock is liable to come after you (not as bad as Grant Fritchey coming after you if you aren’t taking your backups but still.)