Use the following to check the current range for the partition.

SELECT
	 OBJECT_SCHEMA_NAME(pstats.object_id) AS SchemaName
	,OBJECT_NAME(pstats.object_id) AS TableName
	,ps.name AS PartitionSchemeName
	,ds.name AS PartitionFilegroupName
	,pf.name AS PartitionFunctionName
	,CASE pf.boundary_value_on_right WHEN 0 THEN 'Range Left' ELSE 'Range Right' END AS PartitionFunctionRange
	,CASE pf.boundary_value_on_right WHEN 0 THEN 'Upper Boundary' ELSE 'Lower Boundary' END AS PartitionBoundary
	,prv.value AS PartitionBoundaryValue
	,c.name AS PartitionKey
	,CASE 
		WHEN pf.boundary_value_on_right = 0 
		THEN c.name + ' > ' + CAST(ISNULL(LAG(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.object_id, pstats.partition_number), 'Infinity') AS VARCHAR(100)) + ' and ' + c.name + ' <= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100)) 
		ELSE c.name + ' >= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100))  + ' and ' + c.name + ' < ' + CAST(ISNULL(LEAD(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.object_id, pstats.partition_number), 'Infinity') AS VARCHAR(100))
	END AS PartitionRange
	,pstats.partition_number AS PartitionNumber
	,pstats.row_count AS PartitionRowCount
	,p.data_compression_desc AS DataCompression
FROM 
	sys.dm_db_partition_stats AS pstats
	INNER JOIN sys.partitions AS p ON pstats.partition_id = p.partition_id
	INNER JOIN sys.destination_data_spaces AS dds ON pstats.partition_number = dds.destination_id
	INNER JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.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 ps.function_id = pf.function_id
	INNER JOIN sys.indexes AS i ON pstats.object_id = i.object_id AND pstats.index_id = i.index_id AND dds.partition_scheme_id = i.data_space_id AND i.type <= 1 /* Heap or Clustered Index */
	INNER JOIN sys.index_columns AS ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.partition_ordinal > 0
	INNER JOIN sys.columns AS c ON pstats.object_id = c.object_id AND ic.column_id = c.column_id
	LEFT JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id AND pstats.partition_number = (CASE pf.boundary_value_on_right WHEN 0 THEN prv.boundary_id ELSE (prv.boundary_id+1) END)
WHERE
	OBJECT_NAME(pstats.object_id) = 'tbTable01'
ORDER BY 
	TableName, PartitionNumber;

Example:

Extend existing partition from 2018 to 2019. Add additional file group first, then modify partition schema and function. No need to modify partition table itself if the table is already setup to use partition.

Step 1: Add Additional File Group

-- YEAR 2019
ALTER DATABASE MyDB01 
ADD  FILEGROUP [fg201901_tbTable01]
GO
ALTER DATABASE MyDB01 
ADD FILE 
(NAME = N'fg201901_tbTable01', FILENAME = N'C:\MSSQL\MyDB01\PartitionData\2019\fg201901_tbTable01.ndf' , SIZE = 528KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
TO FILEGROUP [fg201901_tbTable01]
GO
-----------------------
ALTER DATABASE MyDB01 
ADD  FILEGROUP [fg201902_tbTable01]
GO
ALTER DATABASE MyDB01 
ADD FILE 
(NAME = N'fg201902_tbTable01', FILENAME = N'C:\MSSQL\MyDB01\PartitionData\2019\fg201902_tbTable01.ndf' , SIZE = 528KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
TO FILEGROUP [fg201902_tbTable01]
GO
---------------------------------
ALTER DATABASE MyDB01 
ADD  FILEGROUP [fg201903_tbTable01]
GO
ALTER DATABASE MyDB01 
ADD FILE 
(NAME = N'fg201903_tbTable01', FILENAME = N'C:\MSSQL\MyDB01\PartitionData\2019\fg201903_tbTable01.ndf' , SIZE = 528KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
TO FILEGROUP [fg201903_tbTable01]
GO
---------------------------------
ALTER DATABASE MyDB01 
ADD  FILEGROUP [fg201904_tbTable01]
GO
ALTER DATABASE MyDB01 
ADD FILE 
(NAME = N'fg201904_tbTable01', FILENAME = N'C:\MSSQL\MyDB01\PartitionData\2019\fg201904_tbTable01.ndf' , SIZE = 528KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
TO FILEGROUP [fg201904_tbTable01]
GO
---------------------------------
ALTER DATABASE MyDB01 
ADD  FILEGROUP [fg201905_tbTable01]
GO
ALTER DATABASE MyDB01 
ADD FILE 
(NAME = N'fg201905_tbTable01', FILENAME = N'C:\MSSQL\MyDB01\PartitionData\2019\fg201905_tbTable01.ndf' , SIZE = 528KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
TO FILEGROUP [fg201905_tbTable01]
GO
---------------------------------
ALTER DATABASE MyDB01 
ADD  FILEGROUP [fg201906_tbTable01]
GO
ALTER DATABASE MyDB01 
ADD FILE 
(NAME = N'fg201906_tbTable01', FILENAME = N'C:\MSSQL\MyDB01\PartitionData\2019\fg201906_tbTable01.ndf' , SIZE = 528KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
TO FILEGROUP [fg201906_tbTable01]
GO
---------------------------------
ALTER DATABASE MyDB01 
ADD  FILEGROUP [fg201907_tbTable01]
GO
ALTER DATABASE MyDB01 
ADD FILE 
(NAME = N'fg201907_tbTable01', FILENAME = N'C:\MSSQL\MyDB01\PartitionData\2019\fg201907_tbTable01.ndf' , SIZE = 528KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
TO FILEGROUP [fg201907_tbTable01]
GO
-----------------------
ALTER DATABASE MyDB01 
ADD  FILEGROUP [fg201908_tbTable01]
GO
ALTER DATABASE MyDB01 
ADD FILE 
(NAME = N'fg201908_tbTable01', FILENAME = N'C:\MSSQL\MyDB01\PartitionData\2019\fg201908_tbTable01.ndf' , SIZE = 528KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
TO FILEGROUP [fg201908_tbTable01]
GO
---------------------------------
ALTER DATABASE MyDB01 
ADD  FILEGROUP [fg201909_tbTable01]
GO
ALTER DATABASE MyDB01 
ADD FILE 
(NAME = N'fg201909_tbTable01', FILENAME = N'C:\MSSQL\MyDB01\PartitionData\2019\fg201909_tbTable01.ndf' , SIZE = 528KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
TO FILEGROUP [fg201909_tbTable01]
GO
---------------------------------
ALTER DATABASE MyDB01 
ADD  FILEGROUP [fg201910_tbTable01]
GO
ALTER DATABASE MyDB01 
ADD FILE 
(NAME = N'fg201910_tbTable01', FILENAME = N'C:\MSSQL\MyDB01\PartitionData\2019\fg201910_tbTable01.ndf' , SIZE = 528KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
TO FILEGROUP [fg201910_tbTable01]
GO
---------------------------------
ALTER DATABASE MyDB01 
ADD  FILEGROUP [fg201911_tbTable01]
GO
ALTER DATABASE MyDB01 
ADD FILE 
(NAME = N'fg201911_tbTable01', FILENAME = N'C:\MSSQL\MyDB01\PartitionData\2019\fg201911_tbTable01.ndf' , SIZE = 528KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
TO FILEGROUP [fg201911_tbTable01]
GO
---------------------------------
ALTER DATABASE MyDB01 
ADD  FILEGROUP [fg201912_tbTable01]
GO
ALTER DATABASE MyDB01 
ADD FILE 
(NAME = N'fg201912_tbTable01', FILENAME = N'C:\MSSQL\MyDB01\PartitionData\2019\fg201912_tbTable01.ndf' , SIZE = 528KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
TO FILEGROUP [fg201912_tbTable01]
GO

Step 2: Add Additional Range to Existing Partition Schema and Partition Function

-- 2019
ALTER PARTITION SCHEME [pstbTable01] NEXT USED [fg201901_tbTable01]
GO
ALTER PARTITION FUNCTION [pftbTable01]() SPLIT RANGE('1/1/2019') 
GO
ALTER PARTITION SCHEME [pstbTable01] NEXT USED [fg201902_tbTable01]
GO
ALTER PARTITION FUNCTION [pftbTable01]() SPLIT RANGE('2/1/2019') 
GO
ALTER PARTITION SCHEME [pstbTable01] NEXT USED [fg201903_tbTable01]
GO
ALTER PARTITION FUNCTION [pftbTable01]() SPLIT RANGE('3/1/2019') 
GO
ALTER PARTITION SCHEME [pstbTable01] NEXT USED [fg201904_tbTable01]
GO
ALTER PARTITION FUNCTION [pftbTable01]() SPLIT RANGE('4/1/2019') 
GO
ALTER PARTITION SCHEME [pstbTable01] NEXT USED [fg201905_tbTable01]
GO
ALTER PARTITION FUNCTION [pftbTable01]() SPLIT RANGE('5/1/2019') 
GO
ALTER PARTITION SCHEME [pstbTable01] NEXT USED [fg201906_tbTable01]
GO
ALTER PARTITION FUNCTION [pftbTable01]() SPLIT RANGE('6/1/2019') 
GO
ALTER PARTITION SCHEME [pstbTable01] NEXT USED [fg201907_tbTable01]
GO
ALTER PARTITION FUNCTION [pftbTable01]() SPLIT RANGE('7/1/2019') 
GO
ALTER PARTITION SCHEME [pstbTable01] NEXT USED [fg201908_tbTable01]
GO
ALTER PARTITION FUNCTION [pftbTable01]() SPLIT RANGE('8/1/2019') 
GO
ALTER PARTITION SCHEME [pstbTable01] NEXT USED [fg201909_tbTable01]
GO
ALTER PARTITION FUNCTION [pftbTable01]() SPLIT RANGE('9/1/2019') 
GO
ALTER PARTITION SCHEME [pstbTable01] NEXT USED [fg201910_tbTable01]
GO
ALTER PARTITION FUNCTION [pftbTable01]() SPLIT RANGE('10/1/2019') 
GO
ALTER PARTITION SCHEME [pstbTable01] NEXT USED [fg201911_tbTable01]
GO
ALTER PARTITION FUNCTION [pftbTable01]() SPLIT RANGE('11/1/2019') 
GO
ALTER PARTITION SCHEME [pstbTable01] NEXT USED [fg201912_tbTable01]
GO
ALTER PARTITION FUNCTION [pftbTable01]() SPLIT RANGE('12/1/2019') 
GO
Last modified: June 29, 2020

Author

Comments

Write a Reply or Comment