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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE OR ALTER VIEW dbo.distincto WITH SCHEMABINDING AS SELECT b.UserId, p.LastEditorUserId, COUNT_BIG(*) AS why_is_this_still_a_thing FROM dbo.Badges AS b JOIN dbo.Posts AS p ON b.UserId = p.OwnerUserId WHERE p.LastEditorUserId > 0 GROUP BY b.UserId, p.LastEditorUserId; GO CREATE UNIQUE CLUSTERED INDEX cx_distincto ON dbo.distincto (UserId, LastEditorUserId); |
Here’s my query:
|
1 2 3 4 5 6 7 |
SELECT DISTINCT b.UserId, p.LastEditorUserId FROM dbo.Badges AS b JOIN dbo.Posts AS p ON b.UserId = p.OwnerUserId WHERE p.LastEditorUserId > 0; |
And here’s the query plan:
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.
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.
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…
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.
[…] Erik Darling has a series of posts on indexed views, with the latest covering query matching even wh…: […]
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.