A lot has been said already on how to do partition switching in SQL Server. I would suggest reading this blog post by Cathrine Wilhelmson, which is my go-to whenever I forget the syntax for partition switching.
Partition switching needs to be smarter. Or at least, it needs to not give me an error that is a false negative.
See the example below. SQL Sever version is SQL Server 2017 CU15.
DROP TABLE IF EXISTS PartitionSwitchTarget;
DROP TABLE IF EXISTS PartitionSwitchSource;
GO
IF EXISTS ( SELECT 1
FROM sys.partition_schemes
WHERE name = N'PartitionSwitchScheme' )
BEGIN
DROP PARTITION SCHEME PartitionSwitchScheme;
END;
GO
IF EXISTS ( SELECT 1
FROM sys.partition_functions
WHERE name = N'PartitionSwitchFunction' )
BEGIN
DROP PARTITION FUNCTION PartitionSwitchFunction;
END;
GO
CREATE PARTITION FUNCTION PartitionSwitchFunction( bigint )
AS RANGE RIGHT FOR VALUES( 1,
100,
1000 );
GO
CREATE PARTITION SCHEME PartitionSwitchScheme
AS PARTITION PartitionSwitchFunction
ALL TO ( [PRIMARY] );
GO
CREATE TABLE PartitionSwitchSource
(
Id bigint
) ON PartitionSwitchScheme (Id);
GO
CREATE TABLE PartitionSwitchTarget
(
Id bigint
);
GO
ALTER TABLE dbo.PartitionSwitchTarget
ADD CONSTRAINT MyCheckConstraint CHECK( Id >= 1
AND Id <= 99
AND Id IS NOT NULL );
GO
ALTER TABLE dbo.PartitionSwitchSource
SWITCH PARTITION 2
TO dbo.PartitionSwitchTarget
This is a RANGE RIGHT bigint partition function. This means that the left endpoints are included in each range and the right endpoints are excluded. When I run this on my SQL Server instance, I get this message:
Msg 4972, Level 16, State 1, Line 47
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'TestDb.dbo.PartitionSwitchSource' allows values that are not allowed by check constraints or partition function on target table 'TestDb.dbo.PartitionSwitchTarget'.
Since this is a bigint partition function, the “less than or equal to 99” predicate is logically equivalent to the “strictly less than 100” predicate. In fact, if I change the check constraint above to say “< 100", the partition switch works without issue.
The SQL Server optimizer also has problems with these types of filters. This blog post by Paul White has a pretty good write up about it:
“The SQL Server 2008 (and later) optimizers do not quite get the internal logic right when an interval references, but excludes, a boundary value belonging to a different partition. The optimizer incorrectly thinks that multiple partitions will be accessed, and concludes that it cannot use the single-partition optimization for MIN and MAX aggregates.”
It goes beyond MIN/MAX aggregates indeed. Look at this query using the same partitioned table above:
SELECT COUNT( * )
FROM PartitionSwitchSource
WHERE Id >= 1
AND Id < 100;
The optimizer accesses two partitions:
If I change the filter to “less than or equal 99”, I get 1 partition:
SELECT COUNT( * )
FROM PartitionSwitchSource
WHERE Id >= 1
AND Id <= 99;
If you’re a developer like me and you are trying to write code to return the “appropriate” filter for table that has partitions, it can be tricky. On the one hand, I apparently must use “strictly less than 100” so that I can do a partition switch later. On the other hand, I have to use “less than or equal to 99” so that SQL Server won’t access the partition to the right when it isn’t necessary to do so.
I have had success writing the filter like this:
WHERE Id >= 1
AND ( Id < 100
OR Id <= 100 - 1 );
This filter in the check constraint allows for partition switching as desired. It also convinces the optimizer that it is OK to only look at 1 partition. For now, this may be the workaround.
Concurrency Exchange 