4

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?

  • 2
    How about you create a computed column and then create index on it ? – Kin Shah Nov 24 '15 at 18:24
  • This is, essentially, a computed column for a view. – bbailes Nov 24 '15 at 18:30
  • If you don't want the data to expand when the Numbers table is expanded why don't you just select this data into a regular table? That will side-step any of the problems with the indexed view, and I believe it will give you the persistent data you're looking for. Does that work for you or am I missing something? – Erik Nov 24 '15 at 18:40
  • The data in the query is not static. The numbers table is being used to draw a table of dates, starting with TODAY and ending 8 days from now. I suppose it could be done with a daily job that deletes the previous day and adds the eighth day. But, I would prefer to not have that possible point of failure. – bbailes Nov 24 '15 at 18:47
  • It does not fix the non-deterministic problem but I am not getting why you are doing the DATEDIFF. Is that just to get midnight? – paparazzo Nov 24 '15 at 18:53
4

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:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.