0

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?

Elaskanator
  • 1,135
  • 10
  • 28
  • I am confused as to why you need to specify `PERSISTED` in a function's output columns. Also, `WITH SCHEMABINDING` doesn't make a UDF deterministic (at least not by default) - it just makes SQL _check_ what's going on in a function. If you want a bit more of an understanding of what it does, [this answer](https://dba.stackexchange.com/a/140382/119724) may be helpful to you. – ZLK Dec 13 '18 at 23:20
  • Because I want to be lazy and have timestamps truncated to `DATE` in various columns, as it takes fewer lines for each column that I can omit from `INSERT` statements. – Elaskanator Dec 14 '18 at 14:16
  • Sorry, I mean why it must be `PERSISTED`... I understand the use of the computed column, but I don't see what `PERSISTED` is actually doing here... It seems useless in a UDF. – ZLK Dec 16 '18 at 21:07
  • It isn't necessary, I just want to use it and am curious why I can't, as it is *only* rejected from UDFs. It is looking liking a bug more than anything. – Elaskanator Dec 17 '18 at 16:29
  • I don't think it's a bug. The problem is that without `WITH SCHEMABINDING`, SQL simply assumes nothing about the function so it doesn't know whether the computed column is or isn't deterministic, it just assumes it isn't. Using `WITH SCHEMABINDING` makes SQL check whether it is or not. Note: This doesn't mean the function has to be deterministic, it just means SQL will perform checks it otherwise wouldn't. But all that aside, `PERSISTED` does nothing useful here. Your UDF is invoked each time you use it, so nothing will really persist from one time you call it to another, unlike a table... – ZLK Dec 17 '18 at 21:05
  • Indeed it is frivolous and I can't think of a benefit to `PERSISTED` in this case, but I still don't understand why the determinism check passes only outside of UDFs. This is my main point. Or are you saying that in a UDF without specifying `WITH SCHEMABINDING` it presumes nothing to be deterministic rather than trying to establish such, so it just automatically fails the check on `PERSISTED`? – Elaskanator Dec 17 '18 at 21:24
  • 1
    Yes. Without `WITH SCHEMABINDING`, it just assumes nothing at all about the function. Since computed columns with `PERSISTED` need to be deterministic and SQL doesn't check anything about the function, it'll just assume it can't be done. This is different from the function itself being deterministic, but `WITH SCHEMABINDING` will obviously also check whether that is the case. But you can technically have a non-deterministic function with a persisted computed column in the output as long as you use `WITH SCHEMABINDING`, I guess, though I think it's pointless to do... – ZLK Dec 17 '18 at 21:26
  • That sounds like the answer then. You wanna turn it into one and I'll accept it? Thanks. – Elaskanator Dec 17 '18 at 22:03

1 Answers1

1

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.

ZLK
  • 2,864
  • 1
  • 10
  • 7