Why does a WHERE clause with an indexed, computed datetime column on an indexed view seek the clustered index on the original table

Question

First, sorry for the long title.

The setup

I reduced the example to the minimum, to make it clearer, so there is no meaningful semantic anymore.

DBMS: Azure SQL Database V12.

Assume there is following table:

CREATE TABLE [dbo].[MuchDataTable](
    [SmallDateTimeColumn] [smalldatetime] NOT NULL
)

GO
CREATE CLUSTERED INDEX [IX_MuchDataTable_SmallDateTimeColumn] ON [MuchDataTable] ([SmallDateTimeColumn])

Now I want to get aggregated data specific for a range of an arbitrary amount of days. As the table contains a lot of rows (over 150 Mio.) I created an indexed view, which looks basically like following:

CREATE VIEW [dbo].[AggregatedDateView]
    WITH SCHEMABINDING
AS
    SELECT CONVERT(DATE, [SmallDateTimeColumn]) AS [DateColumn], COUNT_BIG(*) AS [Count]
    FROM [dbo].[MuchDataTable]
    GROUP BY CONVERT(DATE, [SmallDateTimeColumn])

GO
CREATE UNIQUE CLUSTERED INDEX [IX_AggregatedDateView_Date] ON [dbo].[AggregatedDateView] ([DateColumn])

For my day grouping I convert each DateTime to a Date. Then I create an clustered index over the view.

The problem

Executing a simple SELECT * over the view scans the index of the view. That's fine:

However, if I query with a WHERE constraint on the computed date column, a clustered index seek on the original table is performed:

The question

My understanding is, that there should be a B-Tree consisting of all converted dates for the indexed view. Why does SQL Server choose to scan the clustered index of the underlying table? Is this not possible with an indexed view and I have to build a solution atop of triggers and a normal table?

Edit: for more clarification, it becomes a real problem, once a range of dates is scanned, so something like

SELECT * FROM [AggregatedDateView] WHERE [DateColumn] > '2016-05-20' AND [DateColumn] < '2016-08-20'

Here the index over 3 months is scanned, which is in my case already a lot.

Answer 1

SQL Server expands the view and considers accessing the base tables instead. If the view or the base tables are used is a cost based decision by the optimizer.

To force SQL Server to use your indexed view you should use the noexpand hint.

SELECT * 
from [AggregatedDateView] WITH(NOEXPAND) 
where [DateColumn] > '2016-05-20' AND 
      [DateColumn] < '2016-08-20'