Suppose you have a partition function and a corresponding partition scheme in SQL Server. The partition scheme maps each partition to a filegroup. It also indicates the next used filegroup if one was specified. Sometimes it is useful to know which partition maps to which filegroup as well as which filegroup is marked as the next used filegroup (if any). Here is a query that does exactly that:

SELECT
DestinationId= DestinationDataSpaces.destination_id ,
FilegroupName= Filegroups.name ,
PartitionHighBoundaryValue= PartitionRangeValues.value ,
IsNextUsed=
CASE
WHEN
DestinationDataSpaces.destination_id > 1
AND
LAG (PartitionRangeValues.value , 1) OVER (ORDER BY DestinationDataSpaces.destination_id ASC) IS NULL
THEN
1
ELSE
0
END
FROM
sys.partition_schemes AS PartitionSchemes
INNER JOIN
sys.destination_data_spaces AS DestinationDataSpaces
ON
PartitionSchemes.data_space_id = DestinationDataSpaces.partition_scheme_id
INNER JOIN
sys.filegroups AS Filegroups
ON
DestinationDataSpaces.data_space_id = Filegroups.data_space_id
LEFT OUTER JOIN
sys.partition_range_values AS PartitionRangeValues
ON
PartitionSchemes.function_id = PartitionRangeValues.function_id
AND
DestinationDataSpaces.destination_id = PartitionRangeValues.boundary_id
WHERE
PartitionSchemes.name = N'YourPartitionScheme'
ORDER BY
DestinationId ASC;

Download script here: Partition to Filegroup Mapping in SQL Server- Script

The post Partition to Filegroup Mapping in SQL Server appeared first on .