How to Partition - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to Partition

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-05-26 : 11:50:46

I'm new to SQL Server Partitioning. I have a set of data that I wish to partition per day. Each day, I want to create that days parttion and swap out the oldest day (defined by a configurable retention period).

For instance, I have a retention period of 5 days, therefore, on initial creation:

CREATE DATABASE CRDM
GO
USE CRDM
GO
CREATE TABLE Header
(
  TransactionID		INT
, EndTransDateTime	DATETIME
, Amount			DECIMAL(10,2)
)
GO
CREATE PARTITION FUNCTION Pfn_Header_EndTransDateTime (DATETIME)
AS RANGE RIGHT
FOR VALUES ('20100522','20100523','20100524','20100525','20100526')
GO

But tomorrow, the function needs to be:

CREATE PARTITION FUNCTION Pfn_Header_EndTransDateTime (DATETIME)
AS RANGE RIGHT
FOR VALUES ('20100523','20100524','20100525','20100526','20100527')
GO

How do I go about doing this? Do I need to create a job to 'alter' the partition function on a daily basis, or do I drop and recreate it, or is there TSQL that caters for this requirement (swap out partitions and bring in new)? If so, then how will the partition function reflect the correct values? Sorry, but I really know nothing about his subject, so any help will be greatly appreciated........

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-05-27 : 03:31:35

Found a very good white paper for those in a similar position to myself:

[url]http://technet.microsoft.com/en-us/library/dd578580.aspx[/url]

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources