-- Add additional file group.
ALTER DATABASE Test02
ADD FILEGROUP [fg202001_tbTable]
GO
-- Assoicate file to file group.
ALTER DATABASE Test02
ADD FILE
(NAME = N'fg202001_tbTable', FILENAME = N'D:\MSSQL\DATA\Test02\fg202001_tbTable.ndf' , SIZE = 1000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
TO FILEGROUP [fg202001_tbTable]
GO
-- Add additional file group to partition scheme.
ALTER PARTITION SCHEME [pstbTable] NEXT USED [fg202001_tbTable]
GO
-- Add additional function to partition function.
ALTER PARTITION FUNCTION [pftbTable]() SPLIT RANGE('12/1/2020')
GO

Add File Group and File Association must go together. Alter partition scheme and partition function must go together.

Once you have added the addition partition, SQL Server will automatically move the records that meet the condition to the new partition.

New partitions can be added to the start or end, or in the middle of an existing range.

Sources:

https://dba.stackexchange.com/questions/28983/best-way-to-increase-no-of-partition-in-sql-server-table

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-partition-function-transact-sql?view=sql-server-2017

Last modified: April 12, 2019

Author

Comments

Write a Reply or Comment