1. Create a Database OYCO with a Primary filegroup and two additional filegroup: FG1 and FG2.
USE MASTER
CREATE DATABASE Oyco ON PRIMARY
(NAME=Oyco_dat,FILENAME='c:\data\Oyco_dat.mdf',size=3MB),
FILEGROUP FG1
(Name=FG1_dat,FILENAME='c:\data\FG1_dat.ndf',size=3MB),
FILEGROUP FG2
(NAME=FG2_dat,FILENAME='c:\data\FG2_dat.ndf',size=3MB)
LOG ON
(NAME=Oyco_log,FILENAME='c:\data\Oyco_log.ldf',size=3MB,FILEGROWTH=10%);
GO
2. Create a partition function and partition scheme
USE OYCO
GO
CREATE PARTITION FUNCTION pf(INT) AS RANGE LEFT
FOR VALUES(6);
GO
CREATE PARTITION SCHEME ps AS PARTITION pf
TO (FG1,FG2);
GO
3. Create a table based on the partition scheme; the table will be created on FG1 and FG2.
USE OYCO
GO
CREATE TABLE dbo.Customer
(
CustomerID INT Identity(1,1)NOT NULL ,
FirstName NVARCHAR(25) NOT NULL,
LastName NVARCHAR(25) NOT NULL,
Address NVARCHAR(50) NULL,
Months INT NOT NULL,
--You must include the Months column in the primary key
--in order to use the partition scheme ps(Months).
PRIMARY KEY CLUSTERED (CustomerID,Months)
)
ON ps(Months);
All records with Months field <=6 will be stored in FG1 filegroup. All records with Months field between 7 to 12 will be stored in FG2 filegroup. 4. Generate some data USE OYCO
GO
SET NOCOUNT ON
DECLARE @count INT, @m INT
SET @count=1
SET @m=1
WHILE @count<100>
BEGIN
INSERT dbo.customer(FirstName,LastName,Months)
VALUES('F'+CAST(@count AS NVARCHAR(3)),'L'+CAST(@count AS NVARCHAR(3)),@m)
SET @count=@count+1
SET @m=@m+1
IF @m>12
BEGIN
SET @m=1
END
END
GO
5. Check the data in the dbo.customer table
SELECT * FROM dbo.customer
WHERE $PARTITION.pf(Months)=1
--Partition 1 includes the data from Month 1 to 6.
6. Create an archive table
CREATE TABLE dbo.FirstHalf
(
CustomerID INT Identity(1,1)NOT NULL ,
FirstName NVARCHAR(25) NOT NULL,
LastName NVARCHAR(25) NOT NULL,
Address NVARCHAR(50) NULL,
Months INT NOT NULL,
--You must create the same primary key as dbo.customer.
PRIMARY KEY CLUSTERED (CustomerID,Months)
)
ON FG1
As you see, the dbo.FirstHalf table has the same table structure as dbo.customer. This is important. Creating the dbo.FirstHalf table on Filegroup FG1, you can use the SWITCH operator to archive the partition 1 of dbo.customer table. Partition 1 is on FG1.
7. Archive the data
USE OYCO
ALTER TABLE dbo.customer
SWITCH PARTITION 1 TO dbo.FirstHalf
SELECT * FROM dbo.Customer
WHERE $PARTITION.pf(Months)=1
--All data is gone.
8. Archive the partition 2
USE OYCO
GO
CREATE TABLE dbo.SecondHalf
(
CustomerID INT Identity(1,1)NOT NULL ,
FirstName NVARCHAR(25) NOT NULL,
LastName NVARCHAR(25) NOT NULL,
Address NVARCHAR(50) NULL,
Months INT NOT NULL,
--You must create the primary key to match the dbo.customer structure.
PRIMARY KEY CLUSTERED (CustomerID,Months)
)
ON FG2
USE OYCO
ALTER TABLE dbo.customer
SWITCH PARTITION 2 TO dbo.SecondHalf
Observation:
If you want to move partitioned data by SWITCH operator, both the partition and the destination table must reside on the same filegroup. The destination table is empty.
