Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Cannot persist computed column - not deterministic

I have this function for a computed column :

CREATE FUNCTION [dbo].[GetAllocatedStartTime](@Year INT, @Week INT)
RETURNS DATETIME

WITH schemabinding
AS BEGIN
    RETURN dateadd(week,@Week-(1),dateadd(day,(-1),dateadd(week,datediff(week,(0),CONVERT([varchar](4),@Year,(0))+'-01-01'),(1))))
END

GO

I added the WITH schemabinding in the hope it would make it deterministic so I can persist it. It should be as the two inputs [Week] and [Year] will always yield the same results.

The exact error is :

Computed column 'AllocatedTimeStart' in table 'Tmp_Bookings' cannot be persisted because the column is non-deterministic.

I am using this formula in the column :

([dbo].[GetAllocatedStartTime]([Year],[Week]))

And the column defs :

[Week] [int] NOT NULL,
[Year] [int] NOT NULL,
[AllocatedTimeStart]  AS ([dbo].[GetAllocatedStartTime]([Year],[Week])),

Any ideas?

EDIT:

Changed line to :

RETURN dateadd(week,@Week-(1),dateadd(day,(-1),dateadd(week,datediff(week,(0),CONVERT(datetime,CONVERT([varchar](4),@Year,(0))+'0101',112)),(1))))

But now I get an error saying the formula for the column is invalid. Even though the function saves fine.

EDIT 2:

I've shown exactly what I am doing (or atleast I've tried). There is nothing extra really. As it says the previous function (original one) coupled with the formula ref [dbo].AllocatedStartDate(...) to it in the column worked, but was not persisting, it said it was non deterministic. So according to the suggestion I changed the FUNCTION, replacing the conversion part with the new code, so the function now looks like :

FUNCTION [dbo].[GetSTime](@Year INT, @Week INT)

RETURNS DATETIME
WITH schemabinding
AS BEGIN
    RETURN dateadd(week,@Week-(1),dateadd(day,(-1),dateadd(week,datediff(week,(0),CONVERT(datetime,CONVERT([varchar](4),@Year,(0))+'0101',112)),(1))))
END

Then I tried the same formula as before in the computed field (([dbo].[GetAllocatedStartTime]([Year],[Week]))) ... and it rejects the formula, says its not valid... which is strange as the formula is the same, so it must be doing some sort of check of the changed function and finding that to be invalid, which is also strange because I did a plain SELECT dbo.GetAllocatedStartTime(2012,13) and it worked...

So yes I am confused, and I've never seen SqlFiddle never mind use it. But really there is nothing more than what I have just said.

like image 499
sprocket12 Avatar asked Jan 02 '13 15:01

sprocket12


People also ask

How do you make a computed column persisted?

To make a computed column as persisted, it should be deterministic. We always get the same value provided we supply specific values in a deterministic function. It should have the same database as well.

Is persisted in SQL computed column?

Computed columns can be persisted. It means that SQL Server physically stores the data of the computed columns on disk. When you change data in the table, SQL Server computes the result based on the expression of the computed columns and stores the results in these persisted columns physically.

How do I alter a calculated field in SQL Server?

When altering a computed column the only thing you can do is drop it and re-add it. Show activity on this post. This is one of those situations where it can be easier and faster to just use the diagram feature of SQL Server Management Studio.

What is a persisted table?

PERSISTED. Specifies that the Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated.


1 Answers

CONVERT([varchar](4),@Year,(0))+'-01-01' is being passed to a DATEDIFF call, in a position where a date is expected, forcing an implicit conversion to occur.

From the rules for deterministic functions:

CAST

Deterministic unless used with datetime, smalldatetime, or sql_variant.

CONVERT

Deterministic unless one of these conditions exists:

...

Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified. To be deterministic, the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.

Well, you're calling neither, but you're relying on an implicit conversion, which I'd expect to act like CAST. Rather than rely on this, I'd switch to using CONVERT and give a deterministic style parameter.

So, I'd do: CONVERT(datetime,CONVERT([varchar](4),@Year,(0))+'0101',112) in its place. Having done so, the function itself becomes deterministic

like image 122
Damien_The_Unbeliever Avatar answered Oct 24 '22 19:10

Damien_The_Unbeliever