dbo.Partition_Boundary_Values.sql

-------------------------------------------------------------------------------
---
--- Reverse $PARTITION function - returns the boundary values for a given
--- partition function and partition number.
---
--- Returns:
---
--- Lower_Boundary              sql_variant         NULL for first partition
--- Lower_Boundary_Condition    varchar(2)          "<=" or "<"
--- Upper_Boundary_Condition    varchar(2)          "<=" or "<"
--- Upper_Boundary              sql_variant         NULL for last partition
---
-------------------------------------------------------------------------------
 
CREATE OR ALTER FUNCTION dbo.Partition_Boundary_Values (
    @partition_function     sysname,
    @partition_number       int
) RETURNS TABLE
AS
 
RETURN (

    --- Collect partition function and the partition function's
    --- range boundary values
    WITH fn AS (
        SELECT pf.function_id, pf.boundary_value_on_right,
               prv.boundary_id, prv.[value]
        FROM sys.partition_functions AS pf
        INNER JOIN sys.partition_range_values AS prv ON pf.function_id=prv.function_id
        WHERE pf.[name]=@partition_function),

    --- Compute the ranges
    ranges AS (
        SELECT boundary_id as partition_number,
               NULL AS Lower_Boundary,
               boundary_value_on_right,
               [value] AS Upper_Boundary
        FROM fn
        WHERE boundary_id=1
 
        UNION ALL
 
        SELECT boundary_id+1 as partition_number,
               [value] AS Lower_Boundary,
               boundary_value_on_right,
               LEAD([value], 1) OVER (ORDER BY boundary_id) AS Upper_Boundary
        FROM fn)

    --- Return the range that applies for @partition_number
    SELECT TOP (1)
           Lower_Boundary,
           CAST((CASE WHEN boundary_value_on_right=0 THEN '<' ELSE '<=' END) AS varchar(2)) AS Lower_Boundary_Condition,
           CAST((CASE WHEN boundary_value_on_right=1 THEN '<' ELSE '<=' END) AS varchar(2)) AS Upper_Boundary_Condition,
           Upper_Boundary
    FROM ranges
    WHERE partition_number=@partition_number);
 
GO