user defined function (TSQL) non deterministic and computed persisted column

Question

I have a table, which among it's columns there are two columns of type INT and they are called:

ExpirationMonth

and

ExpirationYear

I want to add a new column to this table, whose value will be calculated based on the values of ExpirationMonth and ExpirationYear. So this column would be a computed column.

For this purpose, I have created a user defined scalar function that calculates the value of this column based on the values of ExpirationMonth and ExpirationYear. The defintion of the function is the following:

CREATE FUNCTION [dbo].[IsExpired]
(
    @ExpirationMonth INT,
    @ExpirationYear INT
)
RETURNS BIT
AS
BEGIN
    DECLARE @Today DATE = GETDATE()
    DECLARE @PreviousMonth INT = MONTH(@today)-1
    DECLARE @CurrentYear INT = YEAR(@today)

    DECLARE @IsExpired BIT = 0

    IF(@ExpirationYear<@CurrentYear OR
      (@ExpirationYear=@CurrentYear AND @ExpirationMonth<=@PreviousMonth))
        SET @IsExpired = 1

    RETURN @IsExpired

END

Initially, I tried to add the column as below:

ALTER Table_Name
ADD IsExpired AS [dbo].[IsExpired]([ExpirationMonth], [ExpirationYear]) PERSISTED

And I got the following error:

Computed column 'IsExpired' in table 'Table_Name' cannot be persisted because the column is non-deterministic.

I removed the PERSISTED from the above statement and I ran it again. Then I noticed that the column had been added with the expected values.

My question is how can I make this column to be persisted, if that is possible at all?

I realized that the reason for this error is the usage of the GETDATE function in the IsExpired function. Since GETDATE is not deterministic, the IsExpired is not deterministic.

However, I don't see how we could make this function, IsExpired, to be deterministic and as a result to define the computed column as persisted, if that is possible at all.

Could you please tell me if that's possible and if it is, how can I do this.

You can save on the overhead of calling the function by just using the expression in the computed column statement. — Gordon Linoff, Apr 21 '15 at 15:50
Would it suit your needs to create a view of the table, rather than creating a function? This way, every time you query the view it can re-calculate whether or not the product has passed it's expiry date. I think some of the answers to [this question](http://stackoverflow.com/questions/29728026/tsql-compare-dates-year-and-calendar-week-formatted-as-strings/29728857?noredirect=1#comment47639066_29728857) could actually help you here. — Aidan, Apr 21 '15 at 15:53
@GordonLinoff I am sorry but I didn't get you. You suggest I pass the `GETDATE` to the `IsExpired` as a parameter? — Christos, Apr 21 '15 at 15:54

score 2 · Accepted Answer · answered Apr 21 '15 at 15:35

2

The value is nondeterministic because it's dependent on the current date. You can't persist computed columns whose values can change over time.

See this article on MSDN for more information on Deterministic Functions.

answered Apr 21 '15 at 15:35

Jon Tirjan

3,556
2
16
24

Hi, I am aware of this. Could you see any way in which we could refactor the user defined function, in order to be deterministic and provide the expected result? – Christos Apr 21 '15 at 15:39
1

Sorry, but it's not possible. You're asking for a column's value to change over time, which is by definition nondeterministic. – Jon Tirjan Apr 21 '15 at 15:41
thanks for the article. I read it. It's quite informative. I will leave active the question for some time, in case of taking any other valuable input as yours. if we don't take any other input, I will mark your answer as the solution. (+1) for me. Thank you dude ! – Christos Apr 21 '15 at 15:43
No problem! You might also consider not using a computed column, and instead adding this calculation to a view. Not much difference, just keeps your table a little cleaner. – Jon Tirjan Apr 21 '15 at 15:49

score 0 · Answer 2 · answered Apr 21 '15 at 16:08

I would suggest doing it in a view

SELECT * INTO yourTable
FROM (VALUES(2015,1),(2015,2),(2015,3),(2015,4),(2015,5),(2015,6)) AS A(ExpirationYear,ExpirationMonth);
GO

CREATE VIEW vw_Expiration
AS
SELECT  ExpirationYear,
        ExpirationMonth,
        CASE
            WHEN    ExpirationYear < YEAR(GETDATE()) OR
                    (ExpirationYear = YEAR(GETDATE()) AND ExpirationMonth <= MONTH(GETDATE()))
                THEN 1
            ELSE 0
        END AS IsExpired
FROM yourTable;
GO

SELECT *
FROM vw_Expiration

Results:

ExpirationYear ExpirationMonth IsExpired
-------------- --------------- -----------
2015           1               1
2015           2               1
2015           3               1
2015           4               1
2015           5               0
2015           6               0

user defined function (TSQL) non deterministic and computed persisted column

2 Answers2