October 21, 2016 at 8:45 am
Am I the only numpty who didn't know that indexed views are used without directly referencing them in the code?
For any other person who wants to see, code to repro below.
Notice that the query does not reference the view, but the plan attached does.
Voodoo and dark magic imo.
No question here, just thought I would evangelise as my Senior and I didn't know.
use adventureworks
GO
create view VWorkorderTotals
with schemabinding
as
SELECT [ProductID] ,sum([OrderQty]) as Quantity,COUNT_BIG(*) Total
FROM [Production].[WorkOrder]
group by [ProductID]
GO
create unique clustered index CLIX_VWorkOrderTotal
On VWorkorderTotals(ProductID)
GO
SELECT [ProductID] ,sum([OrderQty]) as Quantity
FROM [Production].[WorkOrder]
group by [ProductID]
GO
SELECT [ProductID]
FROM [Production].[WorkOrder]
group by [ProductID]
October 21, 2016 at 9:11 am
Nah, many of our customers are baffled by this when they see it the first time too (and so was I when I first started playing with indexed views many moons ago) 🙂
For official reference (from https://technet.microsoft.com/en-us/library/ms181151(v=sql.105).aspx):
A query does not have to explicitly reference an indexed view in the FROM clause for the query optimizer to use the indexed view. If the query contains references to columns in the base tables that are also present in the indexed view, and the query optimizer estimates that using the indexed view provides the lowest cost access mechanism, the query optimizer chooses the indexed view, similar to the way it chooses base table indexes when they are not directly referenced in a query. The query optimizer may choose the view when it contains columns that are not referenced by the query, as long as the view offers the lowest cost option for covering one or more of the columns specified in the query.
Cheers!
October 21, 2016 at 10:31 am
I learned about this not too long ago and think it's a good thing. Grant Fritchey touches on this in his excellent book, SQL Server Execution Plans (page 162):
Since the indexes that define an indexed view are available to the optimizer, they are also
available to queries that don't even refer to the view. For example, the query in Listing
4.13 gives the exact same execution plan as the one shown in Figure 4.17, because the
optimizer recognizes the index as the best way to access the data.
-- Itzik Ben-Gan 2001
October 25, 2016 at 10:04 am
Thanks both for the references.
Must say, this was a great day learning about this.(MS Dev's really earning their pay with this grand feature.)
Should be required reading.
Just thinking about the problems I had where many queries were joining together 4 lookup tables as part of a greater query in many procs in an OLTP workload i.e. (user, userrole,rolegroup,group).
October 26, 2016 at 8:06 am
Just like a non-clustered covering index can eliminate reads from the base table, an indexed view can be thought us as a covering index for eliminating multiple joined tables.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply