SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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:

 

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 .

Guy Glantser

Guy Glantser, Data Platform MVP, is the leader of the Israeli PASS chapter and also the CEO and founder of Madeira Data Solutions. His career has been focused on the Microsoft Data Platform for the past 20 years, performing various database roles as either an on-site DBA, an external consultant or a speaker. Guy is involved in many activities in the Microsoft Data Platform community. He occasionally speaks at community events, such as PASS Summit, SQLBits, SQL Saturdays and user groups around the world. He also co-hosts the SQL Server Radio podcast.

Comments

Leave a comment on the original post [www.madeiradata.com, opens in a new window]

Loading comments...