20

SQL Server 2016's AT TIME ZONE appears to be nondeterministic. However, I haven't been able to find documentation officially stating this or giving a rationale as to the reasoning behind it.

Why is AT TIME ZONE nondeterministic?

Example Showing Non-Determinism

Executing:

CREATE TABLE Test (
    LegacyTimestamp DATETIME,
    Timestamp AS LegacyTimestamp AT TIME ZONE 'Eastern Standard Time' PERSISTED
);

Returns the following error:

Msg 4936, Level 16, State 1, Line 1
Computed column 'Timestamp' in table 'Test' cannot be persisted because the column is non-deterministic.
Ben Gribaudo
  • 659
  • 4
  • 13

2 Answers2

22

AT TIME ZONE employs some logic to calculate Daylight Savings Time. DST offset values are not immutable (they are subject to change via windows updates) and are contained externally in the Windows registry, so therefor the AT TIME ZONE function cannot be deterministic since it is relying on external data.

Similarly, this is why sys.time_zone_info is a view and not a static reference table, it needs to be calculated depending on the registry values which have the most up to date timezone information.

LowlyDBA - John M
  • 10,922
  • 11
  • 42
  • 62
  • 1
    But it's shouldn't it be calculated in reference to the date being converted? If it's non-deterministic, it's because the rule of what day daylight savings starts on may change in the future, as it did in 2009. – Random832 Sep 23 '16 at 17:52
  • @Random832 Right! I skipped over some the details for this, I've updated to be more clear. – LowlyDBA - John M Sep 23 '16 at 18:38
  • 2
    @Random832, consider no only past dates but future dates. If a future date is stored based on the time change rules that exist today the value will become invalid if rules change between now and then, – Dan Guzman Sep 24 '16 at 11:46
  • 1
    John: this is good info, but wouldn't it be more technically accurate to rearrange this a little to say that the actual reason that it is non-deterministic is merely due to the external dependency on getting the info from the registry? Sure, why it has to get the info from there as opposed to being hard-coded into the app code (i.e. the root cause) is mostly due to how frequently the DST rules change, and the fact that new Time Zones can be introduced, but that is really secondary, right? But regardless of the "why", any external dependency should make any function non-deterministic. – Solomon Rutzky Sep 26 '16 at 14:54
  • @srutzky attempted to clean up a bit. – LowlyDBA - John M Sep 26 '16 at 15:16
  • 1
    Awesome! FYI, I found some rather interesting info here -- https://en.wikipedia.org/wiki/Tz_database -- that seems to be one of the few documents (at least that I could find so far) that indicate that DST is not the only thing to change. From what I can tell from looking at the C:\Windows\Globalization\Time Zone\timezones.xml file, even the base offsets can change over time, though less frequently since 1970, I guess. +1 :-) (had to repost this as the link had a bad character in it) – Solomon Rutzky Sep 26 '16 at 15:56
1

I have added AT TIME ZONE to the nondeterministic list on the Deterministic and Nondeterministic topic, and in the topic AT TIME ZONE, I added: Since some information (such as timezone rules) is maintained outside of SQL Server and are subject to occasional change, the AT TIME ZONE function is classed as nondeterministic. Thank you for bringing this up. Rick Byham, SQL Server Books Online.

Rick Byham implies no warrenty
  • 11
  • 1