SQL Table Partition.

IF EXISTS (

SELECT name

FROM sys.databases

WHERE name = N'TestPartition')

DROP DATABASE TestPartition;

GO

CREATE DATABASE TestPartition

ON PRIMARY

(NAME='TestPartition_Part1', FILENAME= 'D:\Partition test\primary\TestPartitionPrimary.mdf', SIZE=2, MAXSIZE=100, FILEGROWTH=1 ),

FILEGROUP TestPartition_Part2

(NAME = 'TestPartition_Part2', FILENAME = 'D:\Partition test\Secondary\TestPartitionSecondary.ndf', SIZE = 2, MAXSIZE=100, FILEGROWTH=1 );

GO



use TestPartition

go

--DROP TABLE TestTable

GO

DROP PARTITION SCHEME TestPartition_PartitionScheme

GO



DROP PARTITION FUNCTION TestPartition_PartitionRange

GO



CREATE PARTITION FUNCTION TestPartition_PartitionRange (INT)

AS RANGE LEFT FOR

VALUES (10);

GO

CREATE PARTITION SCHEME TestPartition_PartitionScheme

AS PARTITION TestPartition_PartitionRange

TO ([PRIMARY], TestPartition_Part2);

GO







-- drop table TestTable

CREATE TABLE TestTable

(ID INT NOT NULL,

Date DATETIME)

ON TestPartition_PartitionScheme (ID);

GO





CREATE UNIQUE CLUSTERED INDEX IX_TestTable

ON TestTable(ID)

ON TestPartition_PartitionScheme (ID);

GO







--- Step 6 : Insert Data in Partitioned Table

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 1

VALUES (1,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2

VALUES (11,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2

VALUES (123,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2

VALUES (-1,GETDATE());

GO





SELECT *

FROM TestTable;

GO

-- drop table [staging_TestTable_20120704-162405]

SELECT *

FROM [staging_TestTable_20120704-162405];

GO



SELECT *

FROM sys.partitions

WHERE OBJECT_NAME(OBJECT_ID)='TestTable';

GO





select GETUTCDATE ()

-- drop table [TestTableArchive]

CREATE TABLE [TestTableArchive]

(ID INT NOT NULL,

Date DATETIME)

ON TestPartition_PartitionScheme (ID);

GO



CREATE UNIQUE CLUSTERED INDEX IX_TestTable

ON [TestTableArchive](ID)

ON TestPartition_PartitionScheme (ID);

GO







ALTER TABLE [TestTable]

SWITCH PARTITION 1

TO [TestTableArchive] PARTITION 1;

select * from [TestTableArchive]

select * from [TestTable]