patternsqlMajor
Why is AT TIME ZONE nondeterministic?
Viewed 0 times
whyzonetimenondeterministic
Problem
SQL Server 2016's
Why is
Example Showing Non-Determinism
Executing:
Returns the following error:
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.