date ranges in where clause of index view

Question

I have an index view which I run a query against with

WHERE GETDATE() BETWEEN start_date AND end_date

in the WHERE clause.

I suspect that the answer to my question is probably going to be no, but, is it possible to push this into the index view or do something clever to achieve the same effect?

My concern is that what the index view will use from GETDATE() will be whatever GETDATE() returns when the index view is created, which is not what I want. I always want this to be the be current date and time. Also, I'm using SQL Server 2008.

Answer 1

You will not be able to do this within an indexed view.

Think about how SQL Server would have to implement this. Every 3ms (or however often the GETDATE() value can change), SQL Server would have to reassess whether any new rows should be added to the view, or whether any existing rows would need to be removed.

Compare this to the usual examples and usage restrictions of Indexed views, where it's hopefully obvious that SQL server can make inclusion/exclusion decisions during appropriate INSERT/UPDATE statements for the base tables.