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?

up vote 6 down vote accepted

Converting a string to a date without a style number is not deterministic, also there is no reason to use a style number when converting a date or datetime to datetime2. Try:

ALTER TABLE dbo.test 
    ADD UTCTime AS CONVERT(datetime2,ISNULL(EventTime, 
    DATEADD(SECOND, PosixTime, CONVERT(datetime,'1970-01-01',120)))) 
    PERSISTED;

Though I'm curious why you need to persist this column. If it is so you can index it, you don't need to persist a column to index it...

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.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.