SQL Server - creating a schema binding with an indexed view using the 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); 

It works great. However, what I really would like to do is to include only rows in this view that are two days or newer from the current date / time when the request is requested. I cannot find a way to do this because I cannot use GetDate () in the Where predicate because it is not deterministic. In other words, I would like to do something similar, but I cannot:

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

Is there any way around this?

Answer 1

AFAIK, you are not going to bypass the deterministic function for the SCHEMABINDING requirement. You will always get an error

The getdate function gives non-deterministic results. Use a deterministic system function or modify a user-defined function to return deterministic results.

If the marker is just one table, I’m not sure that the indexed view will have any performance advantage over the regular view against the table with the same cluster index in the table below (ReadTime, EquipmentID)

However, if the Marker itself is composite, such as VIEW , OR, if you do not want to change the clustered index in the marker table, you might consider something like:

• Create a schematic view without filter ReadDate (vwMarker)
• Create an indexed view in unfiltered view
• Create a second vwMarkerRecent view, not related to the scheme, or one that is added to the non-deterministic GetDate filter.

Sql script example here

i.e. Sort of:

 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