SQL Server: Move a Table from the Primary Partition to a Secondary Partition


I get often asked by customers how they can best move data from their primary partition to a secondary partion. Often databases are created with a simple MDF & LDF set up and the data is on the Primary Default partition. One of the easiest ways to accomplish this task is to use the Create Index while using the With Move To option. I have included the script here:
 
/* -- clean up
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'TestMoveTableToPartition'
GO
USE [master]
GO
ALTER DATABASE [TestMoveTableToPartition] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [TestMoveTableToPartition]
GO
*/



CREATE
DATABASE [TestMoveTableToPartition] ON PRIMARY
(
NAME = N'TestMoveTableToPartition', FILENAME = N'C:\DATA\SQL\TestMoveTableToPartition.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) 
 
LOG ON

(
NAME = N'TestMoveTableToPartition_log', FILENAME = N'C:\DATA\SQL\TestMoveTableToPartition_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10 %)

GO
use

TestMoveTableToPartition

go

-- create a table on Primary (as default)

drop
table Table01
go

create

table Table01(col1 int, col2 int, col3 binary(6000 ))

go

-- fill the table with some sample data

declare
@i int
set
@i=0
while
@i < 10000
begin
insert
into Table01(col1, col2) values (rand()*1000, rand()*1000 )
set
@i=@i+1
end
go

-- create a clustered index (needed for the move)

create
clustered index IX_Col1 on Table01 (col1 )

-- check the datafiles (mdf) to see how big it is, my test was 80 Mb
-- Now add a new Partition and a File (ndf) to it

USE

[master]

GO
ALTER

DATABASE [TestMoveTableToPartition] ADD FILEGROUP [TMTTP02]

GO
USE

[master]

GO
ALTER
DATABASE [TestMoveTableToPartition]

ADD
FILE ( NAME = N'TestMoveTableToPartitionNew', FILENAME = N'C:\DATA\SQL\TestMoveTableToPartitionNew.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )

TO
FILEGROUP [TMTTP02]

GO
USE

[TestMoveTableToPartition]

GO
CREATE
CLUSTERED INDEX [IX_Col1] ON [dbo].[Table01]
(

[col1]
ASC

)

WITH (DROP_EXISTING = ON, ONLINE = OFF) ON [TMTTP02]
GO


Skip to main content