{"id":62,"date":"2013-05-17T13:56:00","date_gmt":"2013-05-17T18:56:00","guid":{"rendered":"https:\/\/www.chrystalsander.com\/blog\/?p=62"},"modified":"2023-07-26T12:34:09","modified_gmt":"2023-07-26T17:34:09","slug":"sql-server-using-partition-function","status":"publish","type":"post","link":"https:\/\/www.chrystalsander.com\/blog\/?p=62","title":{"rendered":"SQL Server : Using $partition Function"},"content":{"rendered":"
\n
\n

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<\/a>."<\/o:p><\/p>\n

 <\/o:p><\/p>\n

The syntax is:<\/o:p><\/p>\n

 <\/o:p><\/p>\n

[ database_name. ] $PARTITION.partition_function_name(expression)<\/o:p><\/span><\/p>\n

 <\/o:p><\/span><\/p>\n

This handy function can also be used to obtain column information, row counts, and other information you might want to know per partition: <\/o:p><\/span><\/p>\n

 <\/o:p><\/span><\/p>\n

–STEP ONE:<\/span><\/o:p><\/p>\n

–Get the name of your partition function:<\/o:p><\/span><\/p>\n

SELECT<\/span> NAME FROM<\/span> SYS<\/span>.<\/span>partition_functions<\/span><\/span><\/o:p><\/p>\n

 <\/o:p><\/p>\n\n\n\n
\n

NAME<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TableAPartitionFunc<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n<\/table>\n

 <\/o:p><\/p>\n

–STEP TWO:<\/o:p><\/span><\/p>\n

–Get the name of the column from which your table is partitioned<\/o:p><\/span><\/p>\n

SELECT<\/span> c.<\/span>name AS<\/span> PartitionedColumnName<\/o:p><\/span><\/p>\n

FROM<\/span>  sys<\/span>.<\/span>tables<\/span> t<\/o:p><\/span><\/p>\n

JOIN<\/span>  sys<\/span>.<\/span>indexes<\/span>  i <\/o:p><\/span><\/p>\n

ON <\/span>(<\/span>i.<\/span>object_id<\/span> =<\/span> t.<\/span>object_id<\/span> <\/o:p><\/span><\/p>\n

AND<\/span> i.<\/span>index_id <<\/span> 2)<\/o:p><\/span><\/span><\/p>\n

JOIN<\/span>  sys<\/span>.<\/span>index_columns<\/span>  ic <\/o:p><\/span><\/p>\n

ON<\/span>(<\/span>ic.<\/span>partition_ordinal ><\/span> 0 <\/o:p><\/span><\/p>\n

AND<\/span> ic.<\/span>index_id =<\/span> i.<\/span>index_id and<\/span> ic.<\/span>object_id<\/span> =<\/span> t.<\/span>object_id<\/span>)<\/o:p><\/span><\/span><\/p>\n

JOIN<\/span>  sys<\/span>.<\/span>columns<\/span> c <\/o:p><\/span><\/p>\n

ON <\/span>(<\/span>c.<\/span>object_id<\/span> =<\/span> ic.<\/span>object_id<\/span> <\/o:p><\/span><\/p>\n

AND<\/span> c.<\/span>column_id =<\/span> ic.<\/span>column_id)<\/o:p><\/span><\/span><\/p>\n

WHERE<\/span> t.<\/span>object_id<\/span> =<\/span> LOWER<\/span>(<\/span>object_id<\/span>(<\/span>‘yourtablename’<\/span>))<\/span><\/span><\/o:p><\/p>\n

 <\/o:p><\/p>\n\n\n\n
\n

PartitionedColumnName<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

DatePort<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n<\/table>\n

 <\/o:p><\/p>\n

–STEP THREE:<\/o:p><\/span><\/p>\n

–Use acquired data to query your partition values:<\/o:p><\/span><\/p>\n

 <\/o:p><\/span><\/p>\n

SELECT<\/span> DISTINCT<\/span> $partition<\/span>.<\/span>TableAPartitionFunc(<\/span>DatePort)<\/span> AS<\/span> PartitionNumber,<\/span> <\/o:p><\/span><\/p>\n

CONVERT<\/span>(<\/span>VARCHAR<\/span>(<\/span>8),<\/span> DatePort,<\/span> 1)<\/span> AS<\/span> RangedDate,<\/span> ‘yourtablename’<\/span> AS<\/span> PartitionedTable<\/o:p><\/span><\/p>\n

from<\/span> yourtablename<\/o:p><\/span><\/span><\/p>\n

ORDER<\/span> BY<\/span> $partition<\/span>.<\/span> TableAPartitionFunc(<\/span>DatePort)<\/span><\/span><\/o:p><\/p>\n

 <\/o:p><\/p>\n\n\n\n\n\n\n
\n

PartitionNumber<\/o:p><\/span><\/p>\n<\/td>\n

\n

RangedDate<\/o:p><\/span><\/p>\n<\/td>\n

\n

PartitionedTable<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

1<\/o:p><\/span><\/p>\n<\/td>\n

\n

5\/13\/2013<\/o:p><\/span><\/p>\n<\/td>\n

\n

yourtablename<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

2<\/o:p><\/span><\/p>\n<\/td>\n

\n

5\/14\/2013<\/o:p><\/span><\/p>\n<\/td>\n

\n

yourtablename<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

3<\/o:p><\/span><\/p>\n<\/td>\n

\n

5\/15\/2013<\/o:p><\/span><\/p>\n<\/td>\n

\n

yourtablename<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

4<\/o:p><\/span><\/p>\n<\/td>\n

\n

5\/16\/2013<\/o:p><\/span><\/p>\n<\/td>\n

\n

yourtablename<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n<\/table>\n

 <\/o:p><\/p>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

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. ] … Continue reading “SQL Server : Using $partition Function”<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[52,50],"_links":{"self":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/62"}],"collection":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=62"}],"version-history":[{"count":1,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/62\/revisions"}],"predecessor-version":[{"id":176,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/62\/revisions\/176"}],"wp:attachment":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=62"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=62"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=62"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}