Computed column cannot be persisted because the column is non-deterministic

Clash Royale CLAN TAG#URR8PPP

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
1
down vote

favorite

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?

sql-server sql-server-2012 computed-column

share|improve this question

asked 37 mins ago

Mazhar

235114

add a comment | 

up vote
1
down vote

favorite

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?

sql-server sql-server-2012 computed-column

share|improve this question

asked 37 mins ago

Mazhar

235114

add a comment | 

up vote
1
down vote

favorite

up vote
1
down vote

favorite

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?

sql-server sql-server-2012 computed-column

share|improve this question

asked 37 mins ago

Mazhar

235114

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?

sql-server sql-server-2012 computed-column

sql-server sql-server-2012 computed-column

share|improve this question

asked 37 mins ago

Mazhar

235114

share|improve this question

asked 37 mins ago

Mazhar

235114

share|improve this question

share|improve this question

asked 37 mins ago

Mazhar

235114

asked 37 mins ago

Mazhar

235114

asked 37 mins ago

Mazhar

235114

235114

add a comment | 

add a comment | 

2 Answers
2

active

oldest

votes

up vote
3
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...

share|improve this answer

answered 28 mins ago

Aaron Bertrand♦

146k19280470

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks, I thought you had to persist a computed column to index it.
  – Mazhar
  19 mins ago
  

  
  
  
  

add a comment | 

up vote
4
down vote

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.

share|improve this answer

answered 21 mins ago

Paul White♦

47.4k14256405

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f220560%2fcomputed-column-cannot-be-persisted-because-the-column-is-non-deterministic%23new-answer', 'question_page');

);

Post as a guest

Name Email

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

active

oldest

votes

active

oldest

votes

up vote
3
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...

share|improve this answer

answered 28 mins ago

Aaron Bertrand♦

146k19280470

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks, I thought you had to persist a computed column to index it.
  – Mazhar
  19 mins ago
  

  
  
  
  

add a comment | 

up vote
3
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...

share|improve this answer

answered 28 mins ago

Aaron Bertrand♦

146k19280470

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks, I thought you had to persist a computed column to index it.
  – Mazhar
  19 mins ago
  

  
  
  
  

add a comment | 

up vote
3
down vote



accepted

up vote
3
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...

share|improve this answer

answered 28 mins ago

Aaron Bertrand♦

146k19280470

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...

share|improve this answer

answered 28 mins ago

Aaron Bertrand♦

146k19280470

share|improve this answer

share|improve this answer

answered 28 mins ago

Aaron Bertrand♦

146k19280470

answered 28 mins ago

Aaron Bertrand♦

146k19280470

answered 28 mins ago

Aaron Bertrand♦

146k19280470

146k19280470

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks, I thought you had to persist a computed column to index it.
  – Mazhar
  19 mins ago
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks, I thought you had to persist a computed column to index it.
  – Mazhar
  19 mins ago
  

  
  
  
  

Thanks, I thought you had to persist a computed column to index it.
– Mazhar
19 mins ago

Thanks, I thought you had to persist a computed column to index it.
– Mazhar
19 mins ago

add a comment | 

up vote
4
down vote

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.

share|improve this answer

answered 21 mins ago

Paul White♦

47.4k14256405

add a comment | 

up vote
4
down vote

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.

share|improve this answer

answered 21 mins ago

Paul White♦

47.4k14256405

add a comment | 

up vote
4
down vote

up vote
4
down vote

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.

share|improve this answer

answered 21 mins ago

Paul White♦

47.4k14256405

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.

share|improve this answer

answered 21 mins ago

Paul White♦

47.4k14256405

share|improve this answer

share|improve this answer

answered 21 mins ago

Paul White♦

47.4k14256405

answered 21 mins ago

Paul White♦

47.4k14256405

answered 21 mins ago

Paul White♦

47.4k14256405

47.4k14256405

add a comment | 

add a comment | 

 

draft saved

draft discarded

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f220560%2fcomputed-column-cannot-be-persisted-because-the-column-is-non-deterministic%23new-answer', 'question_page');

);

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Name Email

Name

Email

Name Email

Name

Email