Deterministic function for getting today's date

Question

I am trying to create an indexed view using the following code (so that I can publish it to replication it as a table):

CREATE VIEW lc.vw_dates
WITH SCHEMABINDING
AS

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), number) AS SettingDate
FROM lc.numbers
WHERE number<8

GO

CREATE UNIQUE CLUSTERED INDEX
idx_LCDates ON lc.vw_dates(SettingDate)

lc.numbers is simply a table with 1 column (number) which is incremented by row 1-100.

However, I keep getting the error:

Column 'SettingDate' in view 'lc.vw_dates' cannot be used in an index or statistics or as a partition key because it is non-deterministic.

I realize that GETDATE() is non-deterministic. But, is there a way to make this work without drawing a calendar table?

Hannah Vernon · Accepted Answer · 2015-11-24T19:17:48.083

You cannot use the function GETDATE() as the basis for a persisted view since the output of the function changes moment-by-moment. This what SQL Server means in the error "because it is non-deterministic." Function results MUST be predictably the same every time they are called in order for SQL Server to persist the results.

Luckily, there is an easy way for you to persist this data once-per-day using a scheduled job. Perhaps using SQL Server Agent, or Windows Job Scheduler, etc.

Here I'm creating the numbers table, and the table to hold the next 7 days:

CREATE TABLE dbo.Numbers
(
    Number INT NOT NULL
        CONSTRAINT PK_Numbers
        PRIMARY KEY CLUSTERED
);

;WITH cte AS
(
    SELECT TOP(100) Number = ROW_NUMBER() OVER (ORDER BY t1.num, t2.num)
    FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) t1(num)
        , (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) t2(num)
    ORDER BY t1.num * t2.num
)
INSERT INTO dbo.Numbers (Number)
SELECT cte.Number
FROM cte;

CREATE TABLE dbo.Next7Days
(
    SettingDate DATETIME NOT NULL
        CONSTRAINT PK_Next8Days
        PRIMARY KEY CLUSTERED
);

Schedule this to happen once per day:

TRUNCATE TABLE dbo.Next7Days;

INSERT INTO dbo.Next7Days(SettingDate)
SELECT SettingDate = DATEADD(DAY, n.Number, DATEDIFF(DAY, 0, GETDATE()))
FROM dbo.Numbers n
WHERE n.Number < 8;

Since the dates of the next 7 days only changes once per day, this solution should work well.

The dbo.Next7Days table contains the following after I ran the above code:

score -1 · Answer 2 · answered Mar 20 '20 at 19:04

This is hacky, but you can add a non-deterministic column to the table, rather than calling the non-deterministic function i.e. RAND(), GETDATE() from inside of the UDF. First in the column definition give it a default value, i.e. DEFAULT(GETDATE()). Then use this new column to feed the udf to compute the calculated column.

IF OBJECT_ID('dbo.sometest','U') is not null drop table dbo.sometest
GO
IF OBJECT_ID (N'dbo.udf_Next_Run', N'FN') IS NOT NULL  DROP FUNCTION dbo.udf_Next_Run
GO

CREATE FUNCTION dbo.[udf_Next_Run]
(
    @seconds int, 
    @seed DATETIME --new column
)
RETURNS varchar(20)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @NextRun varchar(20);

    SET @NextRun =  ( Select TOP 1 TRY_CONVERT(datetime, DATEADD(SECOND, @seconds, @seed) , 112) ) ; 

    RETURN @NextRun;

END


GO


CREATE TABLE dbo.sometest ( id int identity(1,1), numb_sec int, SEED DATETIME DEFAULT( GETDATE() ) , Next_Run_Date AS dbo.udf_Next_Run(numb_sec, SEED) PERSISTED )
GO
--external Python function reading a cron expression and getting # of seconds till next run

INSERT INTO dbo.sometest(numb_sec)VALUES(2020),(180)
select * from sometest

Deterministic function for getting today's date

2 Answers2