sql-server - 带日期部分的索引 View ，日期部分上的查询未使用索引

Question

我需要按 datepart(day, BornDate) 和/或月份对一个有几百万行的表进行查询，这非常占用 CPU。

我尝试将索引 View 与日期部分列一起使用，甚至在索引 View 本身的 datepart(day, BornDate) 列上创建非聚集索引。但执行计划仍然告诉我查询是使用基础表上的 datepart 计算的。

我运行的查询如下:

set statistics time on
SELECT count(1) FROM [dbo].[DemandsBornDateParts] where borndateday = 5 OPTION (RECOMPILE)
set statistics time off

我总是将它与指向基础表的相同查询进行比较:

set statistics time on
select count(1) from dbo.Demands where DAY(borndate) = 5
set statistics time off

它们都显示了几乎相同的查询计划，具有几乎相同的子树成本、CPU 和运行时间，都使用谓词 datepart(day,[dbo].[Demands].[BornDate]) 进行聚簇索引扫描=(5)

View 定义如下:

GO  
--Set the options to support indexed views.  
SET NUMERIC_ROUNDABORT OFF;  
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,  
    QUOTED_IDENTIFIER, ANSI_NULLS ON;  
GO  
--Create view with schemabinding.  
IF OBJECT_ID ('dbo.DemandsBornDateParts', 'view') IS NOT NULL  
DROP VIEW dbo.DemandsBornDateParts ;  
GO  
CREATE VIEW dbo.DemandsBornDateParts
WITH SCHEMABINDING  
AS  
    SELECT id,
           Datepart(DAY, borndate)   AS BornDateDay,
           Datepart(MONTH, borndate) AS BornDateMonth,
           Datepart(YEAR, borndate)  AS BornDateYear
    FROM   DBO.demands  
GO  

--Create an index on the view.  
CREATE UNIQUE CLUSTERED INDEX [PK_dbo.DemandsBornDateParts]
    ON dbo.DemandsBornDateParts (Id);  
GO
CREATE NONCLUSTERED INDEX [IX_BornDateDay] ON [dbo].[DemandsBornDateParts]
(
    [BornDateDay] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

如何在不一遍又一遍地重新计算 datepart 的情况下使用持久化/索引列？我不能使用持久化列，我需要使用 View 。

Answer 1

在查询中的 View 名称后添加 WITH(NOEXPAND)。来自 Table Hints 上的文档:

NOEXPAND

Specifies that any indexed views are not expanded to access underlying tables when the query optimizer processes the query. The query optimizer treats the view like a table with clustered index. NOEXPAND applies only to indexed views.

来自Remarks on Using NOEXPAND在同一页上:

To force the optimizer to use an index for an indexed view, specify the NOEXPAND option.