A few days ago, I explained how to partition a table in SQL Server 2005.  I also mentioned how moving data is very fast when using partitions.  This is because you can swap partitions to other tables without having to move data.  All that happens is a pointer to the data is changed.  This makes a file partition move great for archiving data.

For instance, say that we have two invoice tables, one that should contain the last two years of current invoices and an archival invoice data table.  Each year is partitioned into its own filegroup.  Suppose that our production table has the year 2005 in it. This has to be moved over to our archive table.  On the archival data table we have to make a partition to contain 2005. 

First, we need to add a filegroup for the data to be put into.

ALTER PARTITION SCHEME myscheme
NEXT USED [filearch2005]

To add a partition, we must split them.  Therefore, let's use the following function:

ALTER PARTITION FUNCTION mypartfunc ()
SPLIT ('1/1/2005')

Now everything after 2005 is in its own partition and we just need to SWITCH from our Invoice table to our InvoiceArch table.

ALTER TABLE dbo.Invoice
SWITCH PARTITION 1 TO dbo.InvoiceArch PARTITION 5

After the PARTITION word we need to specify the number of the partition.  Now we have moved all the 2005 data from Invoice table to the InvoiceArch table in no time without locks.