PartitionNumbersInValueOrder.sql

USE tempdb
GO
SET STATISTICS TIME, IO ON
GO

-- Create a partition function, using a few merges and splits
CREATE PARTITION FUNCTION PF_INT_1to10(INT)
	AS RANGE LEFT FOR VALUES (1, 2, 3, 4, 6, 7, 8, 9);
GO
ALTER PARTITION FUNCTION PF_INT_1to10()
	MERGE RANGE (3)
GO
ALTER PARTITION FUNCTION PF_INT_1to10()
	SPLIT RANGE (5)
GO
ALTER PARTITION FUNCTION PF_INT_1to10()
	SPLIT RANGE (3)
GO
-- Confirm that partitions numbers are in order
SELECT x, $PARTITION.PF_INT_1to10(x)
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS v(x)
GO

-- Create a partition scheme and test data
CREATE PARTITION SCHEME PS_INT_1to10
	AS PARTITION PF_INT_1to10 ALL TO ([PRIMARY]);
GO
CREATE TABLE dbo.test_partition_ranges (
	x INT NOT NULL
)
GO
CREATE CLUSTERED INDEX CI_test_partition_ranges
	ON dbo.test_partition_ranges (x) on PS_INT_1to10(x);
GO
INSERT INTO dbo.test_partition_ranges WITH (TABLOCK) (x)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) % 10 AS x
FROM master..spt_values
GO

-- Run all queries with "Include Actual Execution Plan" on.
-- The predicate x BETWEEN 4 AND 7 is converted to a range seek
-- on the partition number, strongly indicating that there is a
-- guarantee that partition numbers are in order by value.
-- Seek Keys[1]:
--		Start: PtnId1001 >= Scalar Operator(...[@1]...),
--		End: PtnId1001 <= Scalar Operator(...[@2]...)
SELECT COUNT(*)
FROM dbo.test_partition_ranges
WHERE x BETWEEN 4 AND 7
GO

-- If we change our predicate to use the partition number directly,
-- we see the same seek predicate.
-- Seek Keys[1]:
--		Start: PtnId1001 >= Scalar Operator(...[@1]...),
--		End: PtnId1001 <= Scalar Operator(...[@2]...)
SELECT COUNT(*)
FROM dbo.test_partition_ranges
WHERE $PARTITION.PF_INT_1to10(x) BETWEEN 4 AND 7
GO

-- If we mix conflicting predicates that use values and partition numbers,
-- SQL Server figures knows that no partitions are eligible.
-- "Actual Partition Count" is 0 and there are 0 logical reads
SELECT COUNT(*)
FROM dbo.test_partition_ranges
WHERE $PARTITION.PF_INT_1to10(x) > 5
	AND x <= 5
GO

-- All 10 partitions are accessed, and ordering by the partition column
-- does not produce a sort! It appears that SQL Server is once again
-- relying on the partition numbers being in order by value.
SELECT x
FROM dbo.test_partition_ranges
ORDER BY x
GO

-- Cleanup
DROP TABLE dbo.test_partition_ranges;
GO
DROP PARTITION SCHEME PS_INT_1to10;
GO
DROP PARTITION FUNCTION PF_INT_1to10;
GO