Systems Engineering and RDBMS

Moving data in and out of the partitions in SQL Server 2005

Posted by decipherinfosys on January 10, 2008

A few days back, we had written an introductory post on table data partitioning in SQL Server 2005. You can read more on that post here. In this post, we will look at the SWITCH operator and see how the data can be moved in and out of the partitions. This will become useful in designing archival solutions for larger tables. We will use the same example that we did in that previous post so take a look at that post to see the different partition ranges and how they were set up etc.. So, let’s first see the data set that we had created and which partitions those records belonged to:

/************************************************************
Let’s look at the data first
*************************************************************/

select * from dbo.test_partition;

COL1                 COL2
 -------------------- ----------
 1                    X
 100001               X
 200001               X
 300001               X
 400001               X
 500001               X
 600001               X
 700001               X
 800001               X
 900001               X
 1000001              X

/************************************************************
Now, let us look at the partition distribution
*************************************************************/

select * from sys.partitions where object_id = object_id(‘TEST_PARTITION’);

partition_id         object_id   index_id    partition_number hobt_id              rows
 -------------------- ----------- ----------- ---------------- -------------------- --------------------
 72057594039042048    1253579504  1           1                72057594039042048    1
 72057594039107584    1253579504  1           2                72057594039107584    4
 72057594039173120    1253579504  1           3                72057594039173120    5
 72057594039238656    1253579504  1           4                72057594039238656    1

/************************************************************
Switching the Data OUT from the Partitioned Table:
************************************************************/

Let us create another table for archiving of the data:

Create table dbo.TEST_PARTITION_ARCHIVE
(
COL1 BIGINT IDENTITY,
COL2 NVARCHAR(10),
CONSTRAINT PK_TEST_PARTITION_ARCHIVE PRIMARY KEY (COL1)
) ON PARITION_SCHEME_1 (COL1);

And now, say we want to move the first partition over from the TEST_PARTITION table to the TEST_PARTITION_ARCHIVE table:

ALTER TABLE dbo.TEST_PARTITION SWITCH PARTITION 1 TO dbo.TEST_PARTITION_ARCHIVE PARTITION 1;
GO

Now, let’s run the above SQL to look at the partitions and the data:

/************************************************************
Looking at the data:
************************************************************/

SELECT * FROM dbo.TEST_PARTITION;

COL1                 COL2
 -------------------- ----------
 100001               X
 200001               X
 300001               X
 400001               X
 500001               X
 600001               X
 700001               X
 800001               X
 900001               X
 1000001              X

SELECT * FROM dbo.TEST_PARTITION_ARCHIVE;

COL1                 COL2
 -------------------- ----------
 1                    X

/************************************************************
Looking at the Partitions:
************************************************************/

select * from sys.partitions where object_id = object_id(‘TEST_PARTITION’);

partition_id         object_id   index_id    partition_number hobt_id              rows
 -------------------- ----------- ----------- ---------------- -------------------- --------------------
 72057594039107584    1253579504  1           2                72057594039107584    4
 72057594039173120    1253579504  1           3                72057594039173120    5
 72057594039238656    1253579504  1           4                72057594039238656    1
 72057594039369728    1253579504  1           1                72057594039369728    0

select * from sys.partitions where object_id = object_id(‘TEST_PARTITION_ARCHIVE’);

partition_id         object_id   index_id    partition_number hobt_id              rows
 -------------------- ----------- ----------- ---------------- -------------------- --------------------
 72057594039042048    1317579732  1           1                72057594039042048    1
 72057594039435264    1317579732  1           2                72057594039435264    0
 72057594039500800    1317579732  1           3                72057594039500800    0
 72057594039566336    1317579732  1           4                72057594039566336    0

This can be useful if you have created partitions for say all 52 weeks of the year and on a rolling basis, you can move the partitions from the main table to the archive table. That way, there will not be any need to purge the data from the actual main production table – this will help in keeping fragmentation low since no delete was done and will also minimize the time taken to move away the stale data to another table.

/************************************************************
Switching the Data INTO from the Partitioned Table:
************************************************************/

And switching the data back is also pretty straight-forward but prior to doing that, let us insert some more data in the TEST_PARTITION_ARCHIVE table:

SET IDENTITY_INSERT TEST_PARTITION_ARCHIVE ON
INSERT INTO dbo.TEST_PARTITION_ARCHIVE (COL1, COL2) VALUES (20, ‘Y’);
INSERT INTO dbo.TEST_PARTITION_ARCHIVE (COL1, COL2) VALUES (30, ‘Y’);
SET IDENTITY_INSERT TEST_PARTITION_ARCHIVE OFF

select * from sys.partitions where object_id = object_id(‘TEST_PARTITION_ARCHIVE’);

partition_id         object_id   index_id    partition_number hobt_id              rows
 -------------------- ----------- ----------- ---------------- -------------------- --------------------
 72057594039042048    1317579732  1           1                72057594039042048    3
 72057594039435264    1317579732  1           2                72057594039435264    0
 72057594039500800    1317579732  1           3                72057594039500800    0
 72057594039566336    1317579732  1           4                72057594039566336    0

And now, let us switch this data back to the main table:

ALTER TABLE dbo.TEST_PARTITION_ARCHIVE SWITCH PARTITION 1 TO dbo.TEST_PARTITION PARTITION 1;
GO

And let’s check the data now:

select * from dbo.test_partition;

COL1                 COL2
 -------------------- ----------
 1                    X
 20                   Y
 30                   Y
 100001               X
 200001               X
 300001               X
 400001               X
 500001               X
 600001               X
 700001               X
 800001               X
 900001               X
 1000001              X

select * from dbo.test_partition_archive;

COL1                 COL2
 -------------------- ----------

And the partitions:

select * from sys.partitions where object_id = object_id(‘TEST_PARTITION’);

partition_id         object_id   index_id    partition_number hobt_id              rows
 -------------------- ----------- ----------- ---------------- -------------------- --------------------
 72057594039042048    1253579504  1           1                72057594039042048    3
 72057594039107584    1253579504  1           2                72057594039107584    4
 72057594039173120    1253579504  1           3                72057594039173120    5
 72057594039238656    1253579504  1           4                72057594039238656    1

select * from sys.partitions where object_id = object_id(‘TEST_PARTITION_ARCHIVE’);

partition_id         object_id   index_id    partition_number hobt_id              rows
 -------------------- ----------- ----------- ---------------- -------------------- --------------------
 72057594039369728    1317579732  1           1                72057594039369728    0
 72057594039435264    1317579732  1           2                72057594039435264    0
 72057594039500800    1317579732  1           3                72057594039500800    0
 72057594039566336    1317579732  1           4                72057594039566336    0

This is just a glimpse of the powerful functionality that exists in SQL Server 2005 pertaining to partitioning. In future posts, we will cover more on the partitioning features, the data partitioning scheme etc.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: