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

The following script shows how to use table partition to delete 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 table on the sheme

--4. Populate the partitioned table with test data

--5. Check the data distribution in the partitioned table

--6. Delete the first partition

---a. create a temp table with the same structure of the partitioned table

---b. move the first partition of the partitioned table to the temp table

---c. Check the data distribution in the partitioned table and the temp table

---d. drop the temp 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), tval VARCHAR(200)) ON psche(tid)

 

---------------------------------------------------------------

--4. Populate the partitioned table

---------------------------------------------------------------

INSERT ptable(tval)

SELECT [name] FROM syscolumns

 

---------------------------------------------------------------

--5. Check the data distribution in the partitioned table

---------------------------------------------------------------

SELECT $PARTITION.pfunc(tid), COUNT(*) FROM ptable

GROUP BY $PARTITION.pfunc(tid)

 

---------------------------------------------------------------

--6. Delete the first partition

---------------------------------------------------------------

---a. create a temp table with the same structure of the partitioned table

   CREATE TABLE temp(tid INT NOT NULL, tval VARCHAR(200))

 

---b. move the first partition of the partitioned table to the temp table

   ALTER TABLE ptable

    SWITCH PARTITION 1 TO temp

 

---c. Check the data distribution in the partitioned table and the temp table

   SELECT $PARTITION.pfunc(tid), COUNT(*) FROM ptable

   GROUP BY $PARTITION.pfunc(tid)

 

   SELECT count(*) FROM temp

 

 

---d. drop the temp table

   DROP TABLE temp

 

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

run the following script to clean it up

       if object_id('ptable') is not null

       begin

               drop table ptable

       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

       if object_id('temp') is not null

       begin

               drop table temp

       end

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