SQL Server partitions: finding min / max value from metadata (i.e. sys.partition_range_values) rather than the table itself

Question

I've an unique requirement for finding min / max value from a partition's range values. The idea is to gain on response time by querying metadata rather than the table itself.

for e.g. I've a table partitioned on business date. when I do a min / max / distinct count of business date on the table - the result does not match when I try to apply the same logic on SQL Server's partition range system table.

Code:

SQL Server, query #1

Select 
    Min(BusinessDate) min_date, 
    Max(BusinessDate) max_date, 
    Count(Distinct BusinessDate) as working_days 
From tbl_History

Output: min_date=7/13/2018, max_date=9/10/2020, working_days=396

SQL Server query #2

Select 
    Object_Name(t.object_id),
    Min(lv.value) min_left_val,
    Max(lv.value) max_left_val, -- only this matches with Query 1's max date
    Min(rv.value) min_right_val, -- checked min value on RIGHT boundary
    Max(rv.value) max_right_val, -- checked max value on RIGHT boundary
    (DateDiff(dd, Cast(Min(lv.value) As Date), Cast(Max(lv.value) As Date)) + 1) - (DateDiff(wk, Cast(Min(lv.value) As Date), Cast(Max(lv.value) As Date)) *2) - (Case When DateName(dw, Cast(Min(lv.value) As Date)) = 'Sunday' Then 1 Else 0 End) 
 - (Case When DateName(dw, Cast(Min(lv.value) As Date)) = 'Saturday' Then 1 Else 0 End) As working_days -- this is off by a day which is ok
From
    sys.partitions p
join 
    sys.allocation_units au on p.hobt_id = au.container_id
join 
    sys.indexes i on p.object_id = i.object_id and i.index_id = 1 and p.rows > 0
join 
    sys.tables t on t.object_id = i.object_id and t.object_id = Object_Id('tbl_History')
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
group By
    Object_Name(t.object_id)

Output:

object_name=tbl_History, min_lv=3/6/2019, max_lv=9/10/2020, min_rv=3/6/2019, max_rv=3/6/2019, working_days=397

-- tbl_History is LEFT range partitioned at our end, and it's evident that output dates between Query 1 and Query 2 only agree on Max value of LEFT range boundary. I adjusted the index_id (among clustered and non-clustered) as well as partitions with row # > 0 but didn't help much.

I would like to know why and if this is achievable (i.e. matching min value).

Answer 1

What you are trying to do is not possible with your current setup because the actual min date (2018-07-13) is less than the first boundary (2019-03-06).

To accomplish what you want you'll need a boundary for every date in the table. With a RANGE LEFT function, you can create and maintain a check constraint to ensure there are no rows less than the first boundary but it will be up to you to ensure you have partition boundaries with no gaps.