DATETIME2 Partition Functions and SSMS Scripting Weirdness

Kendra Little’s blog post on DATETIME2 and Partition Elimination today reminded me of an interesting behavior in SQL Server Management Studio that I encountered recently. After putting together a partitioned database on my workstation, scripting it out, and deploying it to a test server, I started seeing problems with partition switching because partitions that should have been empty were ending up with data in them.

After a bit of investigation, I discovered that it seems that regardless of the precision specified when you create a partition function with an input parameter type of DATETIME2, if you later use SSMS to generate a CREATE script of that partition function, it will default to a precision of (3).

Take the following script, for example:

AS RANGE LEFT FOR VALUES (N'2016-01-31T23:59:59.9999999', N'2016-02-29T23:59:59.9999999', N'2016-03-31T23:59:59.9999999');

After creating this partition function, I can then generate the CREATE script by right-clicking on the partition function in Object Explorer and be presented with this:

CREATE PARTITION FUNCTION [pf_MonthsLeft](DATETIME2(7)) AS RANGE LEFT FOR VALUES (N'2016-01-31T23:59:59.999', N'2016-02-29T23:59:59.999', N'2016-03-31T23:59:59.999')

The data type is still DATETIME2(7), but the range values have all been truncated to 3 digit precision. In a RANGE RIGHT scenario where it’s all zeros after the decimal, you might not even notice. However, in the example above, you could end up with a partitioned table where a row with a time stamp of ‘2016-01-31T23:59:59.9000001’ ends up in one partition, while ‘2016-01-31T23:59:59.9990001’ ends up in the next partition.

I can’t think of a real-world situation where I would have partitions smaller than milliseconds, but consider this partition function:

AS RANGE LEFT FOR VALUES (N'2016-01-31T23:59:59.9990000', N'2016-01-31T23:59:59.9999999')

SSMS generates the script for this one with two identical values:

AS RANGE LEFT FOR VALUES (N'2016-01-31T23:59:59.999', N'2016-01-31T23:59:59.999')

If you tried to run that script, you would be presented with this nice error:

Msg 7708, Level 16, State 1, Line 5
Duplicate range boundary values are not allowed in partition function boundary values list. Partition boundary values at ordinal 1 and 2 are equal.

Once again, this proves there is really no substitute for writing (and keeping) your own scripts. I won’t make that mistake again.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s