In my last post on partitioning I used the $Partition command in passing. I’ve been thinking it deserves a bit more attention.

So what does it do? Per BOL

Returns the partition number into which a set of partitioning column values would be mapped for any specified partition function in SQL Server 2016.

So it basically tells us which partition any given row is in. This can be particularly handy at times. For example, if you want to know the min and max values of a column per partition.

-- Set up the partition function and scheme
CREATE PARTITION FUNCTION pfWhatsInAPart (datetime)
AS RANGE RIGHT FOR VALUES ('1/1/2017','2/1/2017','3/1/2017','4/1/2017','5/1/2017','6/1/2017');
GO
CREATE PARTITION SCHEME psWhatsInAPart
AS PARTITION pfWhatsInAPart
ALL TO ( [PRIMARY] );
GO
-- Create the table 
CREATE TABLE WhatsInAPart (Col1 INT NULL, Modified datetime)
ON psWhatsInAPart (Modified);
GO
-- Insert some test values across the full range of 
-- possibilities. All of the named values and just 
INSERT INTO WhatsInAPart
SELECT TOP (300000) ROW_NUMBER() OVER (ORDER BY column_id),
DATEADD(minute, ROW_NUMBER() OVER (ORDER BY column_id), '12/15/2016')
FROM sys.all_columns
CROSS APPLY sys.objects;
GO
SELECT $Partition.pfWhatsInAPart(Modified) PartitionNo, 
min(Modified) MinModified, max(Modified) MaxModified
FROM WhatsInAPart
GROUP BY $Partition.pfWhatsInAPart(Modified)
ORDER BY $Partition.pfWhatsInAPart(Modified);
GO

What’s really cool is that even works if the partition isn’t set up yet. You can get a quick feel for what will be in each partition.

SELECT $Partition.pfWhatsInAPart(Modified) PartitionNo, COUNT(1) AS PartCount,
min(Modified) MinModified, max(Modified) MaxModified
FROM WhatsInAPart2
GROUP BY $Partition.pfWhatsInAPart(Modified)
ORDER BY $Partition.pfWhatsInAPart(Modified);
GO

Filed under: Microsoft SQL Server, Partitioning, SQLServerPedia Syndication Tagged: microsoft sql server, Partitioning