Search code examples
sql-serverdatetimecalculated-columnsnon-deterministicpersisted-column

SQL Server - DATE conversion from DATETIME is non-deterministic but only in user-defined function

Why is this type conversion rejected as non-deterministic for a PERSISTED computed column in return tables of user-defined functions (UDF) in SQL Server?

CREATE FUNCTION MyTimeIntervalFunction(@Param1 INT)
RETURNS @MyTimeInterval TABLE
(
     StartUtc   DATETIME    NOT NULL PRIMARY KEY
    ,EndUtc     DATETIME    NOT NULL
    ,DateUtc    AS CONVERT(DATE, StartUtc) PERSISTED
)
AS BEGIN
    --do stuff
    RETURN
END

enter image description here

Note this is not converting to or from a string representation, so I don't know why it doesn't work because globalization/region stuff should be irrelevant.

This works outside of a UDF (including stored procedures):

DECLARE @MyTimeInterval TABLE
(
     StartUtc   DATETIME    NOT NULL PRIMARY KEY
    ,EndUtc     DATETIME    NOT NULL
    ,DateUtc    AS CONVERT(DATE, StartUtc) PERSISTED
)
INSERT INTO @MyTimeInterval(StartUtc, EndUtc)
VALUES ('2018-01-01', '2018-01-02')
SELECT * FROM @MyTimeInterval

enter image description here

It seems that adding WITH SCHEMABINDING to the UDF definition shuts it up, but I don't understand why, because it looks like that only marks the function output as deterministic based on input parameters. And I have to do other non-deterministic stuff in my function, so it is not a candidate workaround.

Wonky string manipulation could also be a workaround, but is not preferable. Style 126 for ISO-8601 on CONVERT is still non-deterministic according to SQL Server. It seems the only option is to abandon use of persisted computed columns?

Solution

  • As mentioned at the beginning of this somewhat related answer, not specifying WITH SCHEMABINDING means SQL Server skips checks on such things as determinism and data access.

    Since PERSISTED in a computer column requires the "computed column expression" to be deterministic and SQL Server skips any checks on whether or not it actually is deterministic, it won't be allowed. The same error would occur even if you had something as simple as i AS 1 PERSISTED.

    (This is unrelated to whether everything in the function itself is deterministic.)

    All that said, using PERSISTED in a TVF doesn't actually add anything to the function, as far as I know.