/*********************************************************************
The following script shows how to use table partition to archive history data (the first partition)
--1. Create Partition Function to define a partition range
--2. Create Partition Sheme to map the partition to file group(s)
--3. Create a partitioned table using the sheme
--4. Create a partitioned archive table using the same scheme
--5. Populate the partitioned table with test data
--6. Archive the first partition
---a. move the first partition of the partitioned table to the ptable_archive table
---b. Check the data distribution in the partitioned table and the ptable_archive table
--Using the following script to check the partition informaiton
select * from sys.partitions where object_id = Object_id('ptable')
select * from sys.partition_range_values
**********************************************************************/
---------------------------------------------------------------
--1. Create Partition Function to define a partition range
---------------------------------------------------------------
CREATE PARTITION FUNCTION pfunc (int)
AS RANGE LEFT FOR VALUES (50,100,150)
---------------------------------------------------------------
--2. Create Partition Sheme to map the partition to file group(s)
---------------------------------------------------------------
CREATE PARTITION SCHEME psche
AS PARTITION pfunc ALL TO ([Primary])
---------------------------------------------------------------
--3. Create a table on the sheme
---------------------------------------------------------------
CREATE TABLE ptable(tid INT IDENTITY(1,1) Primary Key Clustered, tval VARCHAR(200)) ON psche(tid)
---------------------------------------------------------------
--4. Create a archive table
---------------------------------------------------------------
CREATE TABLE ptable_archive(tid INT Primary Key Clustered, tval VARCHAR(200)) ON psche(tid)
---------------------------------------------------------------
--5. Populate the partitioned table with test data
---------------------------------------------------------------
INSERT ptable(tval)
SELECT [name] FROM syscolumns
--Check the data distribution in the partitioned table
--SELECT $PARTITION.pfunc(tid), COUNT(*) FROM ptable GROUP BY $PARTITION.pfunc(tid)
--SELECT $PARTITION.pfunc(tid), COUNT(*) FROM ptable_archive GROUP BY $PARTITION.pfunc(tid)
---------------------------------------------------------------
--6. Archive the first partition
---------------------------------------------------------------
---a. move the first partition of the partitioned table to the temp table
ALTER TABLE ptable SWITCH PARTITION 1 TO ptable_archive PARTITION 1
---b. Check the data distribution in the partitioned table and the ptable_archive table
SELECT $PARTITION.pfunc(tid), COUNT(*) FROM ptable GROUP BY $PARTITION.pfunc(tid)
SELECT $PARTITION.pfunc(tid), COUNT(*) FROM ptable_archive GROUP BY $PARTITION.pfunc(tid)
/*********************************************************************
--run the following script to clean it up
if object_id('ptable') is not null
begin
drop table ptable
end
if object_id('ptable_archive') is not null
begin
drop table ptable_archive
end
if exists(select * from sys.partition_schemes where [name] = 'psche')
begin
drop partition scheme psche
end
if exists(select 1 from sys.partition_functions where [name] = 'pfunc')
begin
drop partition function pfunc
end
go
**********************************************************************/