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?