Full Time DBA - SQL Server Table Partition

SQL Server Table Partitioning - How to Archive History Data

Full Time DBA — Mon, 09 Mar 2009 00:11:21 GMT

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

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

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

SQL Server Table Partitioning - How to Delete History Data

Full Time DBA — Fri, 06 Mar 2009 18:59:54 GMT

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

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

if object_id('temp') is not null

begin

drop table temp

end

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