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]
GOStep 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')
GOBest Practices
Always keep empty partitions at both ends of the partition range. Keep the partitions at both ends to guarantee that the partition split and the partition merge don’t incur any data movement. The partition split occurs at the beginning and the partition merge occurs at the end. Avoid splitting or merging populated partitions. Splitting or merging populated partitions can be inefficient. They can be inefficient because the split or merge may cause as much as four times more log generation, and may also cause severe locking.
Modify a Partition Function
You can change the way a table or index is partitioned in SQL Server by adding or subtracting the number of partitions specified, in increments of 1, in the partition function of the partitioned table or index by using Transact-SQL. When you add a partition, you do so by “splitting” an existing partition into two partitions and redefining the boundaries of the new partitions. When you drop a partition, you do so by “merging” the boundaries of two partitions into one. This last action repopulates one partition and leaves the other partition unassigned.
Comments