Split partition in sql server

Sql server 2005 introduced partitioning to segregate large table data into separate filegroup. Split and merge partitoin used to manage table data as per requirement.

Split partition helps you to move data in different filegroup, Sql server enterprise edition supports partitioning of tables that improves query performance and makes easy for DBAs to handle large table maintenance. Merge partition helps you to combine two different partition in single partition.

To understand more about table parition in sql server, follow the link http://www.sqlserver-query.com/sql-server-table-partition/

Lets say, I have large data in the table, also applied partition to improve performance table. Now we want to move our data stored in primary filegroup into another filegroup, this process called as split partition in sql server.

Step by step approach to complete this activity

1. Add new filegroup to hold new file.

Alter database partitiondb add filegroup Partitiondb_fg4

2. Add the file by modifying database as shown below.

alter database partitiondb

add file (  Name = p4,  Filename = ‘c:\p4.ndf’, size= 1024kb )

to filegroup Partitiondb_fg4

3. Alter the partition scheme to used newly created filegroup

alter partition scheme Partitiondb_PartitionScheme next used Partitiondb_fg4

4. Alter the partition function to split the data in the new filegroup

alter partition function Partitiondb_PartitionRange (INT) split range(20)

5. Now insert some values greater then 15, it should go in new filegroup or partition. As last value for identity column is 20 so it will insert 10 more records which store in filegroup Partitiondb_fg4


INSERT INTO Stdetail (Date)
GO 10


Run the below query to check the whether data has move to 4th partition or not.


Select * from sys.partitions where object_name(object_id) = ‘Stdetail‘

Leave a Reply




+ 9 = 17