Partition to Filegroup Mapping in SQL Server

STAY IN TOUCH

Get New posts delivered straight to your inbox

  • Madeira Team

Partition to Filegroup Mapping in SQL Server

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

#sqlserver #scripts #partitioning

JOIN OUR MAILING LIST

CONTACT US

 3 Rapaport St. Kfar Saba, Israel

 +972-9-7400101

  • Google+ - White Circle
  • Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle