Indexed view with datepart, query on datepart is not using the index

Question

I need to do a query by datepart(day, BornDate) and/or month, on a table that has several million rows, which is vey CPU intenstive.

I tried using indexed view with the datepart columns, even creating nonclustered index on the datepart(day, BornDate) column in the indexed view itself. But the execution plan still tells me that the query is being computed using datepart on the underlying table.

The query I run, is the following:

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

I compare it alway to the same query directed to the underlying table:

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

They both show almost identical query plans, with nearly the same subtree cost, CPU and elapsed time, both doing a clustered index scan with predicate datepart(day,[dbo].[Demands].[BornDate])=(5)

The view is defined like this:

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

How can I achieve to use the persisted/indexed columns, without recalculating the datepart over and over again? I can not use persisted columns, I need to use a view.

Answer 1

Add WITH(NOEXPAND) after the view's name in a query. From the documentation on 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.

And from Remarks on Using NOEXPAND on the same page:

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