Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Archiving SQL Server data using partitioning

MSSQLTips author Ben Snaidero By:   |   Read Comments (3)   |   Related Tips: More > Partitioning
Problem

Many companies now have a requirement to keep data for long periods of time. While this data does have to be available if requested, it usually does not need to be accessible by the application for any current transactions. Data that falls into this category are a good candidate for archival.  As we all know, the larger a database/table becomes the more challenges with performance and maintenance we start to encounter. This tip will look into how partitioning can be used in the archiving process to provide users with uninterrupted access to the other data in the table while at the same time making the archiving process as fast as possible.

Solution

For those of you that have not had much experience with partitioning, this tip provides you with some good information to get started.

For this tip you can use the code that follows to setup a partitioned table and load some data into it.

-- Create partition function and scheme
CREATE PARTITION FUNCTION myDateRangePF (datetime)
AS RANGE LEFT FOR VALUES ('20120401', '20120501','20120601',
                          '20120701', '20120801','20120901')
GO
CREATE PARTITION SCHEME myPartitionScheme AS PARTITION myDateRangePF ALL TO ([PRIMARY]) 
GO 
-- Create table and indexes
CREATE TABLE myPartitionTable (i INT IDENTITY (1,1),
                               s CHAR(10) , 
                               PartCol datetime NOT NULL) 
    ON myPartitionScheme (PartCol) 
GO
ALTER TABLE dbo.myPartitionTable ADD CONSTRAINT 
    PK_myPartitionTable PRIMARY KEY NONCLUSTERED (i,PartCol) 
  ON myPartitionScheme (PartCol) 
GO
CREATE CLUSTERED INDEX IX_myPartitionTable_PartCol 
  ON myPartitionTable (PartCol) 
  ON myPartitionScheme(PartCol)
GO
-- Polulate table data
DECLARE @x INT, @y INT
SELECT @y=3
WHILE @y < 10
BEGIN
 SELECT @x=1
 WHILE @x < 20000
 BEGIN  
    INSERT INTO myPartitionTable (s,PartCol) 
              VALUES ('data ' + CAST(@x AS VARCHAR),'20120' + CAST (@y AS VARCHAR)+ '15')
    SELECT @x=@x+1
 END
 SELECT @y=@y+1 
END 
GO

Now that we have a partitioned table with some data in it, let's take a look at the underlying structure of this table.

take a look and the underlying structure of this table

As you can see from the query results we have 7 partitions in this table and we would like to remove the oldest partition. To accomplish this we will use the SWITCH PARTITION clause of the ALTER TABLE statement. A good description of this statement can be found here. When using the SWITCH PARTITION clause there are some requirements that must be adhered to but the main point is that the source and target table schemas must match (with the exception of both needing to be partitioned).

Here is the code we can use to create our archive table.

CREATE TABLE myPartitionTableArchive (i INT NOT NULL,
                                           s CHAR(10) , 
                                           PartCol datetime NOT NULL) 
GO
ALTER TABLE myPartitionTableArchive ADD CONSTRAINT 
    PK_myPartitionTableArchive PRIMARY KEY NONCLUSTERED (i,PartCol) 
GO
CREATE CLUSTERED INDEX IX_myPartitionTableArchive_PartCol
  ON myPartitionTableArchive (PartCol) 
GO

Now that we have an empty archive table we can switch partition 1 from our partitioned table with the main partition of this table. Here is the code to do this.

ALTER TABLE myPartitionTable SWITCH PARTITION 1 TO myPartitionTableArchive 
GO

After running the statement above if we take a look at the sys.partitions catalog view we can see that the first partition in our partitioned table is now empty and the archive table holds these records.

we can see that the first partition in our partitioned table is now empty and the archive table holds these records

We can now merge this first empty partition with the second partition using the following command.

ALTER PARTITION FUNCTION myDateRangePF () MERGE RANGE ('20120401')
GO

Taking a look at the sys.partitions catalog view after this statement is executed we can see that we no longer have the empty partition.

after this statement is executed we can see that we no longer have the empty partition

Now that we have the data we would like to archive in a separate table we can use bcp to export the data to a text file. There is a great tip here that describes how you can do this using TSQL. For our example you can run the following command and a sample output can be found here. Note: you have to replace the text between the # symbols. Once you have exported the data you can back it up to tape or leave it on your server for fast access, whichever works best for your situation.

EXEC xp_cmdshell 'bcp "select * from myPartitionTableArchive" 
queryout "C:\myPartitionTableArchive_#DATEHERE#.txt" -T -S#SERVERNAME# -c -t,'
GO
DROP TABLE myPartitionTableArchive
GO

One final thing that also has to be done when working with partitioned tables is adding new partitions. Now that we have archived the oldest month of data let's assume we are moving into October and we would like to create a new partition for any new data that is added to the table this month. We can accomplish this by altering the partition function and splitting the last partition. Please note that this could also be done even if there was October data already loaded into the table. The TSQL to do this is as follows.

-- Split last partition by altering partition function
-- Note: When splitting a partition you need to use the following command before issuing the 
         ALTER PARTITION command however this is not needed for the first split command issued.
--    ALTER PARTITION SCHEME myPartitionScheme NEXT USED [PRIMARY]
ALTER PARTITION FUNCTION myDateRangePF () SPLIT RANGE ('20121001')
GO
Next Steps
  • Read here for more information regarding partitioning
  • Evaluate your database systems to see if any would benefit from having data archived
  • Create TSQL script and SQL job to automate this archival process


Last Update: 10/11/2012


About the author
MSSQLTips author Ben Snaidero
Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Thursday, October 11, 2012 - 4:54:02 PM - Ranga Read The Tip

Nice tip. I was wondering it would have been really cool if we could archive the data to a table in a different database! This would cut the data transformation task to move the data to a different database.


Wednesday, January 16, 2013 - 6:09:36 PM - Thanh Nguyen Read The Tip

Hi Ben,

Nice article. Thanks!

I'm given a task to do these 2 things:
- 1. Separate customers data across multiple filegroups using table partition (based on CustomerID)
- 2. Archive customers data (using table partition if possible) (based on CreatedDate)

I have a very clear idea on how to do either 1 or 2, but not both. Is that even possible to partition on top of partition? If not, what's the best way to approach this?

Thanks,
Thanh

 


Thursday, January 17, 2013 - 12:16:38 AM - Ben Snaidero Read The Tip

Hi Thanh

Since a partition function can only be defined on a single column you can only partition the table on a single column....I've never really thought of how I would tackle your particular situation.  Off the top of my head could you use a computed column (in your case based on customerid, createddate) and partition based on this column? Just a thought.

Thanks for reading.

Ben.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.