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:
Transact-SQL
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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
Kommentare