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.

Answer 1

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.