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

  • Article

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