HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

Why is AT TIME ZONE nondeterministic?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
whyzonetimenondeterministic

Problem

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.

Solution

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.

Context

StackExchange Database Administrators Q#150509, answer score: 22

Revisions (0)

No revisions yet.