How to manage partition

Partition are useful function for managing large data in database. I have posted an article on how to create partition. Now we will look into how to manage partitions.

First we will create partition as given in my past article.


USE bi

CREATE PARTITION FUNCTION part_test_func(datetime) AS range LEFT FOR VALUES ('2008-09-10', '2008-09-20', '2008-09-30')

/* so it has range values like

<= '2008-09-10' > '2008-09-10 and <= '2008-09-20' > '2008-09-20 and <= '2008-09-30' >'2008-09-30'

*/

/* Now we need to map file group to each of this partition

Here first , second and third are filegroups */

CREATE PARTITION scheme part_test_scheme AS PARTITION part_test_func TO(FIRST, SECOND, third, FIRST)

/*Now we will cretae a table on this partition scheme */

CREATE TABLE part_test

(

id INT IDENTITY(1, 1),

data VARCHAR(100),

DATE DATETIME

)

ON part_test_scheme(DATE)

INSERT INTO part_test

VALUES ('a',

'2008-09-05')

INSERT INTO part_test

VALUES ('a',

'2008-09-15')

INSERT INTO part_test

VALUES ('a',

'2008-09-25')

INSERT INTO part_test

VALUES ('a',

'2008-10-05')

CREATE TABLE part_test_archive

(

id INT IDENTITY(1, 1),

data VARCHAR(100),

DATE DATETIME

)

ON part_test_scheme(DATE)

--Now as part of maintenance we want to move all data of september to archive table.

--We need to switch partition for range between '2008-09-01' and '2009-08-30'

DECLARE @partition_no_start INT

DECLARE @partition_no_last INT

SET @partition_no_start = bi.$partition.Part_test_func('2008-09-01')

SET @partition_no_last = bi.$partition.Part_test_func('2008-09-30')

SELECT @partition_no_start,

@partition_no_last

WHILE @partition_no_start <= @partition_no_last

BEGIN

Alter TABLE part_test switch PARTITION @partition_no_start TO part_test_archive PARTITION @partition_no_start

SET @partition_no_start = @partition_no_start + 1

END

--Lets verify movement of data

SELECT Min(DATE),

Max(DATE)

FROM part_test_archive

SELECT Min(DATE),

Max(DATE)

FROM part_test