Indexed View used without being referenced

  • 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]

  • 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!

  • 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

  • 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]

  • 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