Indexed View used without being referenced

Question

Indexed View used without being referenced

MadAdmin

SSChampion

Points: 11261
More actions
October 21, 2016 at 8:45 am

#400914

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]
Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

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!

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

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.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

MadAdmin SSChampion Points: 11261 More actions · Answer 3

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).

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Eric M Russell SSC Guru Points: 125561 More actions · Answer 4

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