/*********************************************************************

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

**********************************************************************/