SQL tips for database administrator RSS 2.0
# Monday, 09 March 2009

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

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

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

Monday, 09 March 2009 00:11:21 UTC  #    Comments [0] -
SQL Server Table Partition
# Friday, 06 March 2009

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

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

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

Friday, 06 March 2009 18:59:54 UTC  #    Comments [0] -
SQL Server Table Partition
Archive
<2017 August>
SunMonTueWedThuFriSat
303112345
6789101112
13141516171819
20212223242526
272829303112
3456789
Statistics
Total Posts: 6
This Year: 0
This Month: 0
This Week: 0
Comments: 2
All Content © 2017, Full Time DBA