How to Find Partition Range Values
Published: Feb 26, 2017
This is something I struggled to put together the first time I needed it because partitioning uses internal tables with some very strange IDs and even stranger links between tables.

However, once you have the script then you don’t need to worry about them anymore as this will take care of it for you.

The part of the script you need is at the end of this, but first we need a partitioned table to look at:

use AdventureWorks2012

if object_id('testPartition') is not null drop table testPartition

if exists
select *
from sys.partition_schemes
where name = 'testPartitionScheme'
partition scheme testPartitionScheme

select *
from sys.partition_functions
where name = 'testPartitionFunction'
partition function testPartitionFunction

partition function testPartitionFunction(date)
as range left
for values('2008-01-01', '2008-02-01', '2008-03-01', '2008-04-01')

create partition scheme testPartitionScheme
as partition testPartitionFunction
all to ([primary])

create table testPartition
id int identity,
orderDate date,
accountNumber varchar(20),
purchaseOrderNumber varchar(20),
totalDue decimal(19, 8),
constraint pk_testPartition primary key clustered(id, orderDate)
on testPartitionScheme(orderDate)

insert into testPartition
select orderDate, AccountNumber, PurchaseOrderNumber, TotalDue
from sales.SalesOrderHeader
where orderDate between '2007-12-01' and '2008-05-01'

Okay, now we have our partitioned table, how do we obtain our boundary values using tSQL?

select partition_number, lv.value leftValue, rv.value rightValue
from sys.partitions p
join sys.allocation_units a
on p.hobt_id = a.container_id
join sys.indexes i
on p.object_id = i.object_id
join sys.partition_schemes s
on i.data_space_id = s.data_space_id
join sys.partition_functions f
on s.function_id = f.function_id
left join sys.partition_range_values rv
on f.function_id = rv.function_id
and p.partition_number = rv.boundary_id
left join sys.partition_range_values lv
on f.function_id = lv.function_id
and p.partition_number - 1 = lv.boundary_id
where p.object_id = object_id('testPartition')

Not the most intuitive in respect to joins, but the outcome is what we need…

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London



Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron