Computed column cannot be persisted because the column is non-deterministic 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?
You need to use a deterministic style when converting from a string representation.

You were not using a deterministic style with the conversion from string to date.

You were unnecessarily specifying a style when converting from date to datetime2.

There is a confusing mixture of date/time data types in the question.

This works (producing a datetime column):

ALTER TABLE dbo.test 
ADD UTCTime AS 
    ISNULL
    (
        EventTime,
        DATEADD
        (
            SECOND, 
            PosixTime, 
            CONVERT(datetime, '19700101', 112)
        )
    )
    PERSISTED;


As Aaron mentioned (we were answering concurrently), you do not need to persist a deterministic column to index it.