Horizontal table partitioning is first introduced in Sql Server 2005. Table partitioning allows to store data from each period (month, year, etc) to individual partitions (FileGroups). This enables us to perform archiving or purging activities with little data movement. It is a meta data operation, where a partition (of main table) is instantaneously moved to another table (archive table) . At the end of the operation, the data located in the partition is now with  (or moved to) the archive table. So through this meta data operation we can essentially move data from one table to the other in an instant. While there is no actual data movement, it is just a meta data operation where this partition is not assigned to a new table.

There are some limitations to this operation.

• A table could have many partitions and each partition could be assigned to a separate file on dedicated.
• Sliding widow technique allows us to fully automate the whole process to allow growth of data to newer partitions.

Following procedure could be used to automate this sliding-window using a Sql Agent Scheduled job.

[sourcecode language=”sql”]

— * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
— * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
— * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
—
— Automate Sliding Window for Partitioning (SPLIT & MERGE)
—
— * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
— * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
— * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
CREATE PROCEDURE dbo.sp_split_merge_SampleTable
AS
DECLARE @merge_range INT
, @split_range INT

— STEP 1:
— SWITCH PARTITION 1 (both tables)
— so data from Partition 1 on SampleTable will now be associated with
— Partition 1 in Archive table.

ALTER TABLE dbo.SampleTable
SWITCH PARTITION 1
TO dbo.SampleTable_Archive PARTITION 1

— STEP 2:
— get smallest range vlaue from
— "select * from sys.partition_range_values ORDER BY boundary_id ASC"
— and MERGE it

SELECT @merge_range = ( SELECT TOP 1 CONVERT(INT, value)
FROM sys.partition_range_values
ORDER BY boundary_id ASC)

ALTER PARTITION FUNCTION pf_k_rows()
MERGE RANGE (@merge_range)

— STEP 3:
— get largest range vlaue from
— "select * from sys.partition_range_values ORDER BY boundary_id DESC"
— and SPLIT last range with a new value

ALTER PARTITION SCHEME ps_k_rows
NEXT USED [PRIMARY]

SELECT @split_range = (SELECT TOP 1 CONVERT(INT, value)
FROM sys.partition_range_values
ORDER BY boundary_id DESC)
SELECT @split_range = @split_range + 1000

ALTER PARTITION FUNCTION pf_k_rows()
SPLIT RANGE (@split_range)

— STEP 4:
— TRUNCATE SampleTable_Archive to keep its partitions empty for
— SWITCH in next iteration
—
TRUNCATE TABLE dbo.SampleTable_Archive

GO

— EXECUTE dbo.sp_split_merge_SampleTable

[/sourcecode]

Hope this helps,
_SqlTimes