Tag: Partitioning

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

–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’))

–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)

— =============================================

— This is a basic query intended to provide

— a quick look at partition info.  

— =============================================

SELECT b.name as ‘table_name’, a.partition_number,

a.[rows] as ‘row_count’, a.partition_id,

a.object_id, a.hobt_id,

b.schema_id, c.name as ‘schema_name’, 

a.index_id, b.create_date, b.modify_date

FROM  sys.partitions AS a INNER JOIN

sys.objects AS b ON a.object_id = b.object_id

INNER JOIN sys.schemas AS c On c.schema_id = b.schema_id              

WHERE LOWER(b.name) = ‘yourPartitionedTableName’

–pull only first index for a simpler view

and a.index_id < 2

ORDER BY b.name, partition_number;

— =============================================

–Helpful info for a deeper dive

— =============================================

SELECT * FROM SYS.partition_range_values ;

SELECT * from sys.partitions;

SELECT * FROM SYS.partition_functions;

SELECT * FROM sys.partition_schemes;

SELECT * from sys.schemas;

SELECT * FROM sys.filegroups;

–query sys.partitions to view and verify that the table contains partitions as expected

SELECT * FROM sys.partitions where OBJECT_NAME(Object_Id)=‘yourPartitionedTableName’;

SELECT * FROM sys.objects where OBJECT_NAME(Object_Id)=‘yourPartitionedTableName’;