Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Create a new partition in a new filegroup
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Norsk Yak Master

3271 Posts

Posted - 12/10/2009 :  11:12:41  Show Profile  Reply with Quote
The following is a script for adding a new filegroup and partition to an already partitioned table. What it basically does is to find the partitioning factor based on the distribution of data between partition number 1 and 2 and then add that to the largest partition. Example

Partition 1: less then 10
Partition 2: less than 20
Partition 8: less than 80
Partition 9: everything else

Partitioning factor = 10 since 20 - 10 = 10. Then it finds the latest partition (80) and adds a partition with a range of 90 (80 +10) to that.

There are some prerequisites for the script to function:

- The script only supports tables partitioned by an int column
- Each partition must have it's own filegroup
- The table must have at least 3 active partitions; 2 range partitions and the "catch-all" partition
- Partitions/files/filegroups must have matching naming convention and have a 3 digit number at the end (i.e FG001, FG002, etc)
- Filegroup datafilename must be in the form FG004_data where the "FG"-part can be whatever

I'm aware that these prerequisites probably disqualifies the script for general public use but maybe you can make it to fit your needs...I made it to fit my own needs primarily.
CREATE PROCEDURE dbo.AddPartitions 
	@TableName varchar(200)



	@OldFGName varchar(50),
	@NewFGName varchar(50),
	@OldFileName varchar(400),
	@NewFileName varchar(400),
	@FGPrefix varchar(400),
	@RangeFactor int,
	@NewRange int,
	@SQL nvarchar(2000)
DECLARE @PartitionInfo table (
	Object_name varchar(200),
	Index_name varchar(200),
	Partition_number int,
	Filegroup_name varchar(200),
	Value bigint,
	SchemeName varchar(200),
	FunctionName varchar(200)

INSERT INTO @PartitionInfo	
	CAST(value AS bigint),,
FROM sys.partitions p 
	JOIN sys.indexes i
		ON p.object_id = i.object_id 
			and p.index_id = i.index_id
	JOIN sys.partition_schemes ps 
		ON ps.data_space_id = i.data_space_id
	JOIN sys.partition_functions f 
		ON f.function_id = ps.function_id
	LEFT JOIN  sys.partition_range_values rv    
		ON f.function_id = rv.function_id 
			AND p.partition_number = rv.boundary_id
	JOIN sys.destination_data_spaces dds
		ON dds.partition_scheme_id = ps.data_space_id 
			AND dds.destination_id = p.partition_number
	JOIN sys.filegroups fg 
		ON dds.data_space_id = fg.data_space_id
WHERE  i.index_id < 2 
	AND OBJECT_NAME(i.object_id) = @TableName
SET @FGPrefix = REPLACE(@OldFGName, RIGHT(@OldFGName, 3), '')
SET @NewFGName = @FGPrefix + RIGHT('000' + CAST(CAST(RIGHT(@OldFGName, 3) AS int) + 1 AS varchar(50)), 3)

SET @SQL = 'ALTER DATABASE ' + db_name() + ' ADD FILEGROUP ' + @NewFGName + CHAR(13)

SET @OldFileName = (SELECT physical_name FROM sys.database_files WHERE name = @OldFGName + '_data')
SET @NewFileName = REPLACE(@OldFileName, @OldFGName, @NewFGName)

SET @SQL = @SQL + 'ALTER DATABASE ' + db_name() + ' ADD FILE (' + 
	'NAME = ' + @NewFGName + '_data, ' + 
	'FILENAME = ''' + @NewFileName + ''', ' + 
	'SIZE = 100MB, ' + 
	'FILEGROWTH = 10%) ' + 
	'TO FILEGROUP ' + @NewFGName + CHAR(13)

SET @SQL = @SQL + 'ALTER PARTITION SCHEME ' + (SELECT TOP 1 SchemeName FROM @PartitionInfo) + ' NEXT USED ' + @NewFGName + CHAR(13)

SET @RangeFactor = (SELECT CAST(b.value AS int) - CAST(a.value AS int)
	FROM @PartitionInfo a
		CROSS JOIN @PartitionInfo b
	WHERE a.partition_number = 1 
		and b.partition_number = 2)
SET @NewRange = (SELECT @RangeFactor + MAX(CAST(value as int)) FROM @PartitionInfo WHERE value IS NOT NULL)

SET @SQL = @SQL + 'ALTER PARTITION FUNCTION ' + (SELECT TOP 1 FunctionName FROM @PartitionInfo) + '() SPLIT RANGE (' + CAST(@NewRange as varchar(50)) + ')'

--EXEC sp_executesql @SQL
PRINT @sql


- Lumbago

Edited by - Lumbago on 12/10/2009 11:16:28

Starting Member

1 Posts

Posted - 01/05/2010 :  05:34:42  Show Profile  Reply with Quote
Adding a partition to a partitioned table is an important partitioning maintenance activity. You must add a new partition periodically depending on your partitioning needs. For example, our partitioned Orders table partitions the data by year, so every year you need to add a new partition. You add a new partition by splitting an existing partition-typically the last. The last partition covers the range - 8 (infinity). In our case, the last partition covers the range >= 2004. To create a new partition that covers the range >= 2005, split the last existing partition by altering the partitioning function and specifying the new boundary point: 20050101. After the operation, the original last partition is split into two partitions: one covering the range >= 2004 and < 2005, the other covering the range >= 2005. . . .

Price Comparison
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 2.22 seconds. Powered By: Snitz Forums 2000