SQL Server - Creating a Schema Bound Indexed View with a current GetDate filter

Question

I want to create the following indexed view:

CREATE VIEW [Cic].[vwMarker] WITH SCHEMABINDING 
    AS

    Select
        SubId,
        marker.EquipmentID,
        marker.ReadTime,
        marker.CdsLotOpside,
        marker.CdsLotBackside,
        marker.CdteLotOpside,
        marker.CdTeLotBackside
    From dbo.Marker 
    Where dbo.Marker.ReadTime >= Convert(dateTime,'10/5/2011',120)
GO

CREATE UNIQUE CLUSTERED INDEX IX_vwMarker_ReadTime_EquipmentID 
       ON Cic.vwMarker (ReadTime, EquipmentID);

This works fine. However, what I would really like to do is to only include rows in this view that are two days old or newer, as of the current date/time the view is queried. I can't find a way to do this because I cannot use GetDate() in the Where predicate because it is non-deterministic. In other words, I'd like to do something like this, but cannot:

Where dbo.Marker.ReadTime >= Convert(dateTime,DateAdd(dd,-2,GetDate()) ,120)

Is there a way around this?

Answer 1

AFAIK you aren't going to get around the deterministic function for the SCHEMABINDING requirement. You'll always receive the error

The function 'getdate' yields nondeterministic results. Use a deterministic system function, or modify the user-defined function to return deterministic results.

If Marker is just a single table, I'm not sure that an indexed view would have any performance benefit over a normal view against the table with the same clustered index on the underlying table of (ReadTime, EquipmentID)

However, if "Marker" is itself a composite such as a VIEW, OR if you don't want to change the Clustered Index on the Marker table, then you might consider something like:

• Create a schema bound view without the ReadDate filter (vwMarker)
• Create the Indexed View on the unfiltered view
• Create a second, non schema-bound view vwMarkerRecent or such, which adds in the non-deterministic GetDate filter.

Sql Fiddle example here

i.e. Something like:

CREATE VIEW [Cic].[vwMarker] WITH SCHEMABINDING 
    AS
    Select
        SubId,
        marker.EquipmentID,
        marker.ReadTime,
        marker.CdsLotOpside,
        marker.CdsLotBackside,
        marker.CdteLotOpside,
        marker.CdTeLotBackside
    From dbo.Marker 
    -- Add only Deterministic where filters here
GO

CREATE UNIQUE CLUSTERED INDEX IX_vwMarker ON Cic.vwMarker (ReadTime, EquipmentID)
GO    


CREATE VIEW [Cic].[vwRecentMarker] -- Not Schema Bound
    AS
        Select
            vm.SubId,
            vm.EquipmentID,
            vm.ReadTime,
            vm.CdsLotOpside,
            vm.CdsLotBackside,
            vm.CdteLotOpside,
            vm.CdTeLotBackside
        From cic.vwMarker vm
        Where vm.ReadTime >= Convert(dateTime,DateAdd(dd,-2,GetDate()) ,120)
    GO