gotchasqlMinor
Why does a WHERE clause with an indexed, computed datetime column on an indexed view seek the clustered index on the original table
Viewed 0 times
clusteredwhycomputedseekthecolumnwithoriginalwhereview
Problem
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:
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:
For my day grouping I convert each
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
Here the index over 3 months is scanned, which is in my case already a lot.
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.
Solution
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.
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'Code Snippets
SELECT *
from [AggregatedDateView] WITH(NOEXPAND)
where [DateColumn] > '2016-05-20' AND
[DateColumn] < '2016-08-20'Context
StackExchange Database Administrators Q#164668, answer score: 4
Revisions (0)
No revisions yet.