Indexed View Matching With GROUP BY And DISTINCT

Last Updated 5 years ago

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:

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

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:

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;

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

Peter Lee
December 6, 2018 12:47 am

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

Reply
- Thomas Franz
  December 6, 2018 7:35 am
  
  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
  - Mark Freeman
    December 11, 2018 1:05 pm
    
    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
Indexed View Matching – Curated SQL
December 6, 2018 7:03 am

[…] 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