I am currently looking at a database that has been setup by the default installation routine of AppFabric. Looking at the creation scripts I encountered this partition function:

CREATE PARTITION FUNCTION [MSAppServerPF_ASWcfEventsTable](datetime2(7)) AS 
RANGE RIGHT FOR VALUES (N'0001-01-01T00:00:00.000')

And it is being applied to a table that way:


I wonder what this specific partition style effects in. I was under the impression that you store boundary values into the partition function by which the data will be partitioned. Unfortunately, looking for this on the web I could not find a similar example using just one DATETIME2 value in the partition function definition.

Can someone shed some light on this for me?

1 Answer 1


You are correct in your thinking of the boundary values. Basically what your partition function dictates, is that anything greater than or equal to (range right) 0001-01-01T00:00:00.000 will be on the second partition, and anything less than that will be on the first partition.

The peculiar thing about the partition function is that there will never be any value less than 0001-01-01T00:00:00.000, due to the datetime2 date range. As far as I can see, this would mean that the first partition will never have data, and the second partition will have all of the data (provided there is no split going forward).

One thing this strategy could be used for is partition switching and sliding window operations. For more information on exactly what that is you can see it on this blog article. But as for this isolated partitioning structure as a standalone solution, I'm not convinced that there's anything gained here.

  • Thanks for the quick reply, indeed that daterange was what really struck me here :) Commented Sep 10, 2013 at 12:46
  • It might be intended to make it a little easier on the DBA if you decide to start partitioning data. Just add a few boundaries, rather than rebuilding the table/indexes on a new partition scheme. I've never personally run across an application that does this, however.
    – db2
    Commented Sep 10, 2013 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.