cant make a compute column persisted

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

cant make a compute column persisted

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2008-12-31 : 00:34:26

Hi,

I have a UDF as follows:


CREATE FUNCTION dbo.fnreturnDatetime (@SecondVal int)
RETURNS DATETIME
AS
BEGIN
	DECLARE @Output Datetime
	SELECT @Output = DATEADD(SECOND,@SecondVal,'19700101'); 
	RETURN @Output

END

I m using this in one of my columns 
 
create table tblTest
(
	[CreatedDateTimeINT] [int] NULL,
	[CreatedDateTime] as (dbo.fnreturnDatetime([CreatedDateTimeINT])) PERSISTED ,
)

The above works fine, but as soon as i add PERSISTED keyword, it throws an error -
Computed column 'CreatedDateTime' in table 'tblTest' cannot be persisted because the column is non-deterministic.

I don't think value in Createddatetime is non-deterministic because it will remain the same irrespective of the time it is executed.

Am i missing something here?

Thanks :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-31 : 01:16:42

It's non-deterministic because you are using a function.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 02:48:07

cant you just give calculation inline rather than using a function?

zion99
Posting Yak Master

141 Posts

Posted - 2008-12-31 : 02:59:03

Do you mean something like this?


create table tblTest
(
	[CreatedDateTimeINT] [int] NOT NULL
	,[CreatedDateTime] as (DATEADD(SECOND,[CreatedDateTimeINT],'19700101')) PERSISTED
	--,[CreatedDateTime] as (dbo.fnreturnDatetime([CreatedDateTimeINT])) PERSISTED ,
)

I tried that as well, but i get the same error

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources