Indexed View Matching With GROUP BY And DISTINCT

Bit Of A Kick

I’ve been playing with indexed views a little bit lately for some demos in my Bits Precon.

There are a whole bunch of limitations in creating indexed views. One of them is that you can’t base the query on DISTINCT.

Fair enough, but you can do GROUP BY.

And what’s pretty cool is that the optimizer can match a query written to find distinct values to an indexed view with a group by.

Best Example Ever

Here’s my indexed view:

Here’s my query:

And here’s the query plan:

Cookies

I think that’s pretty nifty.

Thanks for reading!

Brent says: I’m always amazed at how smart SQL Server is when I’m least expecting it. Microsoft has put so much work into that query optimizer, and it just keeps getting better. It ain’t perfect – but it’s pretty doggone good.

Previous Post
Indexed View Creation And Underlying Indexes
Next Post
Azure SQL DB is Slow: Do I Need to Buy More DTUs?

5 Comments. Leave new

  • I cannot reproduce the result on my SQL Server 2016 SP2. The query plan assessing the underlying tables, rather than the indexed view.

    Reply
    • use SELECT * FROM dbo.v_whatever WITH (NOEXPAND); to use the index on the view instead of querying the underlying tables.

      This is a point where the query optimizer is very stupid (or to smart, because by default it expands the view (replaces it in the query with an subquery) but is unable to regocnize it after expanding, so it would not use the index on the view…

      Reply
      • Even in Azure SQL Databases in the Premium service tier, which is supposed to act like Enterprise Edition, we had to add WITH (NOEXPAND) everywhere because otherwise it was hitting the base tables and showing missing index warnings on those instead of using the indexes on the views.

        Reply
  • […] Erik Darling has a series of posts on indexed views, with the latest covering query matching even wh…: […]

    Reply
  • Marcos Kirchner
    December 10, 2018 3:52 pm

    why_is_this_still_a_thing? I once read someone (Craig Freedman perhaps) saying that most of the limitations is due to the need to be able to incrementally maintain the view as INSERTs/UPDATEs/DELETEs happen.

    In this case I’d say it is because QP needs a way to tell when it’s time to remove a row from the view after you DELETE something from the base tables. Deleting a qualifying row from base tables does a COUNT– on the view, and once the count gets to zero the row must be deleted from the view as well.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}