SQL Server - Creating a Schema Bound Indexed View with a current GetDate filter
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.
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
![Randy Minder](https://i.stack.imgur.com/rsQ5Z.jpg?s=256&g=1)
Randy Minder
Azure Data Architect / Business Intelligence / Tabular Modeling / Power BI Significant experience with data architecture (database architecture, data warehouses, data marts), SSIS and Azure Data Factory, Business Intelligence with Power BI. Check out my new course on Udemy titled "The DAX Workshop Part 1". The best way to learn DAX is by working through real-world scenarios. The course is filled with exercises (45) to help you learn how to use DAX. Our careers and hobbies are fun and important. But each of us has a soul which will live forever, after our bodies die. Do you know where you'll spend eternity? Jesus Christ said there is only one way to heaven, and it is through Him. You won't get a second chance after you die.
Updated on July 27, 2022Comments
-
Randy Minder almost 2 years
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?