SQL Server : Using $partition Function

Recently, I needed to validate date-range value on each partition on a partitioned table. To do this, I utilized the SQL Server "$Partition" function. Per MSDN, "$Partition returns the partition number into which a set of partitioning column values would be mapped for any specified partition function."

The syntax is:

[ database_name. ] $PARTITION.partition_function_name(expression)

This handy function can also be used to obtain column information, row counts, and other information you might want to know per partition:

–STEP ONE:

–Get the name of your partition function:

SELECT NAME FROM SYS.partition_functions

NAME

TableAPartitionFunc

–STEP TWO:

–Get the name of the column from which your table is partitioned

SELECT c.name AS PartitionedColumnName

FROM sys.tables t

JOIN sys.indexes i

ON (i.object_id = t.object_id

AND i.index_id < 2)

JOIN sys.index_columns ic

ON(ic.partition_ordinal > 0

AND ic.index_id = i.index_id and ic.object_id = t.object_id)

JOIN sys.columns c

ON (c.object_id = ic.object_id

AND c.column_id = ic.column_id)

WHERE t.object_id = LOWER(object_id(‘yourtablename’))

PartitionedColumnName

DatePort

–STEP THREE:

–Use acquired data to query your partition values:

SELECT DISTINCT $partition.TableAPartitionFunc(DatePort) AS PartitionNumber,

CONVERT(VARCHAR(8), DatePort, 1) AS RangedDate, ‘yourtablename’ AS PartitionedTable

from yourtablename

ORDER BY $partition. TableAPartitionFunc(DatePort)