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