3

I'm trying to create indexed view containing only the data for the last 2 weeks.

This part works fine:

CREATE VIEW [dbo].[MainLogView]
WITH SCHEMABINDING
AS
SELECT        Id, Date, System, [Function], StartTime, EndTime, Duration, ResponseIsSuccess, ResponseErrors
FROM            dbo.MainLog
WHERE        (Date >= DATEADD(day, - 14, GETDATE()))

But when I try add index:

CREATE UNIQUE CLUSTERED INDEX IDX_V1 
    ON MainLogView (Id);

I'm geting:

Cannot create index on view 'dbo.MainLogView'. The function 'getdate' yields nondeterministic results. Use a deterministic system function, or modify the user-defined function to return deterministic results.

I know why, but how to reduce the data in a view for the last 2 weeks? I need small and fast querable portion of data from my table.

BWA
  • 5,672
  • 7
  • 34
  • 45
  • You can't. Maintenance of an indexed view is performed effectively by triggers against the base tables, and based purely on the rows affected by the individual actions on those base tables (hence all of their restrictions). Last two weeks would require some alternative maintenance mechanism, which doesn't exist in SQL Server. – Damien_The_Unbeliever May 17 '16 at 13:09
  • Why are you creating an index on ID – TheGameiswar May 17 '16 at 13:22
  • @TheGameiswar because I want test it. First colum. In this case, it is negligible. Beceuse it doesn't work. – BWA May 17 '16 at 13:26
  • if mainlog is a table why dont you create an index on dateadd function and simply use the select in your view ? – TheGameiswar May 17 '16 at 13:28
  • @TheGameiswar I need something like materializedview in oracle. I want get data from view without impact to MainLog. And refresh data in view once a day. – BWA May 17 '16 at 13:30

1 Answers1

3

You could (I think, but I have no real world experience with indexed views) create a one record table (an actual table, since a view is not allowed in an indexed view) which you fill with the current date - 14 days. This table you can keep up to date; either manually, with a trigger or some other clever mechanism. You can use that table to join, and in effect use as filter.

Of course, when you query the view, you have to be sure to update your 'currentDate' table first!

You'd get something like:

CREATE VIEW [dbo].[MainLogView]
WITH SCHEMABINDING
AS
SELECT        Id, Date, System, [Function], StartTime, EndTime, Duration, ResponseIsSuccess, ResponseErrors
FROM            dbo.MainLog ML
INNER JOIN      dbo.CurrentDate CD
           ON   ML.Date >= CD.CurrentDateMin14Days

(Totally untested, might not work... This is basically a hack, I am not at all sure the indexed view will give you any performance increase. You might be better off with a regular view.)

HoneyBadger
  • 14,750
  • 3
  • 34
  • 48