2

For a given partition scheme name, I need to retrieve the set of

  • Boundary Value
  • FileGroup Name

This must work without the existence of any tables participating in the scheme.

i.e. joins via sys.indexes will not work.

I think the answer lies around sys.destination_data_spaces, but have not yet figured the joins to sys.partition_range_values.

Improve this question
0
3

This relationship isn't altogether intuitive but I think this query will get you what you need:

SELECT 
  PScheme       = ps.name, 
  PFunction     = pf.name,
  Boundary      = dds.destination_id,
  BoundaryValue = prv.value,
  [Filegroup]   = fg.name
FROM sys.partition_schemes AS ps
INNER JOIN sys.partition_functions AS pf
        ON ps.function_id = pf.function_id
INNER JOIN sys.destination_data_spaces AS dds
        ON ps.data_space_id = dds.partition_scheme_id
INNER JOIN sys.filegroups AS fg
        ON dds.data_space_id = fg.data_space_id
LEFT OUTER JOIN sys.partition_range_values AS prv
        ON dds.destination_id = pf.boundary_value_on_right + prv.boundary_id 
       AND prv.function_id = pf.function_id;

And because LEFT/RIGHT is always a tedious mental exercise for me, it might be that you need to flip the boundary_value_on_right part on the second-last line:

ON dds.destination_id = prv.boundary_id + ABS(1-pf.boundary_value_on_right)

You don't need to have created any tables.

Improve this answer
5
  • Looking good so far, thanks... can I expand the requirement ever so slightly... ...how do we augment that set with an additional row to represent the 'boundaryless' partition, e.g. in range right, the partition that will store everything below the lowest boundary. Obviously there will be no BoundaryValue (or BoundaryId), but there will be a filegroup. – Paul Holmes Oct 25 '18 at 17:38
  • 1
    @Paul I updated the query to account for that, I think it's simpler than your original attempt. I've tested on both RANGE LEFT and RANGE RIGHT. I prefer keeping the boundary values in there but YMMV. – Aaron Bertrand Oct 25 '18 at 18:12
  • Aha... snap! Yes, I spotted that my second join to partition function was unnecessary, and came up with the same solution you have done. Good to validate that. I’ll mark yours as the answer. – Paul Holmes Oct 25 '18 at 18:41
  • and now I grumble at MS for using different column names for things that are actually the same between sys tables, thus frustrating attempts to work it out for onseself. Thankfully we have stackexchange. – Paul Holmes Oct 25 '18 at 18:54
  • 1
    @Paul and overloading the same column names for things that clearly mean different things, like data_space_id. – Aaron Bertrand Oct 25 '18 at 18:58
1

Using Aaron's answer as a jump off, I've now got this. It's to allow - I think - for the 'boundaryless' partition that doesn't feature in sys.partition_range_values.

The key thing here is the potential 'shift' in the boundary_id, on the assumption that in sys.destination_data_spaces

  • RANGE RIGHT, the boundaryless partition is boundary_id = 1
  • RANGE LEFT, the boundaryless partition is max(boundary_id)

Is this the correct evaluation?

SELECT PScheme = ps.name,
       PFunction = pf.name,
       Boundary = prv.boundary_id, -- Null for 'boundaryless'
       BoundaryValue = prv.Value, -- Null for 'boundaryless'
       [Filegroup] = fg.name
FROM sys.destination_data_spaces AS dds
INNER JOIN sys.filegroups AS fg
    ON dds.data_space_id = fg.data_space_id
INNER JOIN sys.partition_schemes AS ps
    ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN sys.partition_functions AS pf
    ON pf.function_id = ps.function_id
LEFT JOIN sys.partition_range_values AS prv
    ON pf.function_id = prv.function_id AND
        dds.destination_id = (prv.boundary_id + pf.boundary_value_on_right)
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.