What’s in a partition?

Leave a comment

May 22, 2017 by Kenneth Fisher

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,655 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: