First, sorry for the long title.
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.
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:
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.