I know this is not the first time this type of question has been asked.
But why in the following scenario is the persisted computed column being created "non-deterministic". The answer should always be the same, right?
CREATE TABLE dbo.test (Id INT, EventTime DATETIME NULL, PosixTime INT NOT NULL) GO DECLARE @EventTime DATETIME = '20181001 12:00:00' DECLARE @GPSTime INT = DATEDIFF(SECOND, '19700101', @EventTime) INSERT INTO dbo.Test(Id, EventTime, PosixTime) VALUES (1, @EventTime, @GPSTime) , (2, NULL, @GPSTime) GO SELECT * FROM dbo.test GO ALTER TABLE dbo.test ADD UTCTime AS CONVERT(DATETIME2,ISNULL(EventTime, DATEADD(SECOND, PosixTime, CONVERT(DATE,'19700101'))),112) PERSISTED GO
Msg 4936, Level 16, State 1, Line 42 Computed column 'UTCTime' in table 'test' cannot be persisted because the column is non-deterministic.
I think I'm following the deterministc rules here.
Is it possible to create a persisted computed column here?