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谓词中使用GetDate(),因为它是非确定性的。换句话说,我想做这样的事情,但不能:
Where dbo.Marker.ReadTime >= Convert(dateTime,DateAdd(dd,-2,GetDate()) ,120)
Is there a way around this?
有没有解决的办法?
1 个解决方案
#1
13
AFAIK you aren't going to get around the deterministic function for the SCHEMABINDING requirement. You'll always receive the error
AFAIK您不会绕过SCHEMABINDING要求的确定性函数。你总会收到错误
The function 'getdate' yields nondeterministic results. Use a deterministic system function, or modify the user-defined function to return deterministic results.
函数'getdate'产生不确定的结果。使用确定性系统函数,或修改用户定义函数以返回确定性结果。
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)
如果Marker只是一个表,我不确定索引视图是否会比基于(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:
但是,如果“Marker”本身是一个复合体,例如VIEW,或者如果您不想更改Marker表上的Clustered Index,那么您可能会考虑以下内容:
- 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.
创建没有ReadDate过滤器的模式绑定视图(vwMarker)
在未过滤的视图上创建索引视图
创建第二个非模式绑定视图vwMarkerRecent等,它在非确定性GetDate过滤器中添加。
Sql小提琴这里的例子
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
#1
13
AFAIK you aren't going to get around the deterministic function for the SCHEMABINDING requirement. You'll always receive the error
AFAIK您不会绕过SCHEMABINDING要求的确定性函数。你总会收到错误
The function 'getdate' yields nondeterministic results. Use a deterministic system function, or modify the user-defined function to return deterministic results.
函数'getdate'产生不确定的结果。使用确定性系统函数,或修改用户定义函数以返回确定性结果。
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)
如果Marker只是一个表,我不确定索引视图是否会比基于(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:
但是,如果“Marker”本身是一个复合体,例如VIEW,或者如果您不想更改Marker表上的Clustered Index,那么您可能会考虑以下内容:
- 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.
创建没有ReadDate过滤器的模式绑定视图(vwMarker)
在未过滤的视图上创建索引视图
创建第二个非模式绑定视图vwMarkerRecent等,它在非确定性GetDate过滤器中添加。
Sql小提琴这里的例子
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