Recently in a customer code review I came across some interesting logic about Datetime2 and DatetimeOffset datatype. Here is the problem statement for your reference:
Customer was trying to create a view that contains a computed column that converts a datetime2(7) column to a datetimeoffset(7) column. The CAST function looks like:
CAST((CONVERT(varchar(255), DATEADD(hh, +8, RawLog.[Created]),126) + ‘+08:00’) AS datetimeoffset(7)) AS [Created_GMTPlus8]
This returned them an error when a index was created on this view:
Cannot create index on view "MyVideo..RawLogView". The view contains a convert that is imprecise or non-deterministic.
So the customer was asking, how to make this deterministic? Would love to learn if you have any other option.
Here are couple of possible solutions to this problem. The root reason is that the cast string to datetimeoffset(7) is not deterministic, we need use convert with style 126. Let me show how we can rewrite this query to achieve the same. Note, in below example Option 2, we use SWITCHOFFSET built-in to convert a datetime UTC values to a datetimeoffset, and then SWITCH the timezone to +08:00. Let us see the code now:
— If it exists Drop it.
DROP TABLE rawlog
CREATE TABLE rawlog (id INT IDENTITY(1,1) PRIMARY KEY,
INSERT INTO rawlog VALUES(SYSDATETIME())
We have created our dummy table for use with a value. Let us check if the string conversion returns the same result.
— Make sure SWITHOFFSET return the same value as we expected
SELECT created,CAST(CONVERT(VARCHAR(255), DATEADD(hh, +8, [Created]),126) + ‘+08:00’ as DATETIMEOFFSET(7)) CASTStringValue,
SWITCHOFFSET(CAST(created AS DATETIMEOFFSET),‘+08:00’) AS result FROM dbo.rawlog
Our output looks like:
Our values are exactly the same. Let us create the view next, and we will then check if it can be indexed as per our problem statement.
CREATE VIEW v1 WITH SCHEMABINDING AS
SELECT CAST(CONVERT(VARCHAR(255), DATEADD(hh, +8, [Created]),126) + ‘+08:00’ as DATETIMEOFFSET(7)) AS result
–return 0 because of the case string to datetimeoffset is not deterministic
Next let us try to use the proper Conversion function and check. First add the offset with DATEADD and then convert it into DATETIMEOFFSET(7).
CREATE VIEW v2 WITH SCHEMABINDING AS
SELECT CONVERT(DATETIMEOFFSET(7), CONVERT(VARCHAR(255), DATEADD(hh, +8, [Created]),126) + ‘+08:00’,126) AS result
–return 1 because convert with 126 is deterministic
Other than using convert with 126 style, we can also use SWITCHOFFSET function which is also deterministic
CREATE VIEW v3 WITH SCHEMABINDING AS
SELECT SWITCHOFFSET(CAST(created as datetimeoffset),‘+08:00’) AS result FROM dbo.rawlog
— Return 1 because SWITHCHOFFSET is deterministic
These are just two of the methods to achieve the goal. Do let me know if you use Indexed views in your code today? What are the scenarios you use them? Have you encountered any of these deterministic restrictions in your coding anytime? Do let me know, would love to learn from you too.