Partition table based on variable value - SQL Server 2008

Can I specify in a range that all rows having value in CreatedDate column earlier than one month from GETDATE() should be placed in one partition and the rest in other, so that I should query the 2nd partition for latest data a开发者_如何学编程nd 1st one for archived data?

No, you can't. Partition function must be deterministic. Deterministic functions always return the same result any time they are called with a specific set of input values. Unfortunately, GetDate() is nondeterministic function. Unfortunately, you can't use GetDate(), because GetDate() is nondeterministic function.

See http://shannonlowder.com/2010/08/partitioning/ for more details

@Ismail There are alternatives:

Create bit column LastMonth and partition function based on LastMonth column. You need to update field every day, before you start using your data. You don't need to do it daily, maybe is better way to update column you choose to flag your fresh data (or change your partition function), once in a period you choose (week/month/quarter). I don't try this approach, you may need to start some maintenance on table for full performance after updating column.

Another idea that might be work is to make partition for every month, and change filegroups when new month start. For example, if you want your latest data on fast disk f: and history on s:, you will have PartitionJan on s: and PartitionFebruary on f:, when martch started move PartitionFebruary to s:, and start using PartitionMartch on f:.