Accidental Haha
While working on some demos, I came across sort of funny behavior during indexed view creation and how the indexes you have on the base tables can impact how long it takes to create the index on the view.
Starting off with no indexes, this query runs in about six seconds.
1 2 3 4 5 6 7 8 9 |
DECLARE @blobeater SQL_VARIANT; SELECT DISTINCT @blobeater = b.UserId, @blobeater = p.LastEditorUserId FROM dbo.Badges AS b JOIN dbo.Posts AS p ON b.UserId = p.OwnerUserId WHERE p.LastEditorUserId > 0; |
Here’s the plan and the query stats:

1 2 3 4 5 6 7 |
Table 'Badges'. Scan count 5, logical reads 50379, Table 'Posts'. Scan count 5, logical reads 4206390, Table 'Workfile'. Scan count 0, logical reads 0, Table 'Worktable'. Scan count 0, logical reads 0, SQL Server Execution Times: CPU time = 19079 ms, elapsed time = 5489 ms. |
The join column is an include, which is kinda weird, but hey…
1 2 3 |
CREATE INDEX ix_whatever ON dbo.Posts ( LastEditorUserId ) INCLUDE ( OwnerUserId ); |
Out of curiosity, I added it, and re-ran the query.
Here’s the new plan and stats:

1 2 3 4 5 6 7 |
Table 'Badges'. Scan count 5, logical reads 50379, Table 'Posts'. Scan count 5, logical reads 13160, Table 'Workfile'. Scan count 0, logical reads 0, Table 'Worktable'. Scan count 0, logical reads 0, SQL Server Execution Times: CPU time = 11969 ms, elapsed time = 3396 ms. |
It wants on on OwnerUserId, LastEditorUserId. Okay then.
1 2 |
CREATE INDEX ix_apathy ON dbo.Posts ( OwnerUserId, LastEditorUserId ); |
… And it doesn’t get used. It’s the same plan and stats as the last time.
I think that’s silly, and I also think it’s silly that The Optimizer doesn’t want an index on the Badges table at all.
Wonder why. Let’s add one.
1 2 |
CREATE INDEX ix_ennui ON dbo.Badges ( UserId ); |
Sweet death, now the query takes about a minute and a half.
Here’s the new plan and stats:

1 2 3 4 5 6 7 |
Table 'Posts'. Scan count 5, logical reads 13188, Table 'Badges'. Scan count 5, logical reads 14076, Table 'Workfile'. Scan count 0, logical reads 0, Table 'Worktable'. Scan count 0, logical reads 0, SQL Server Execution Times: CPU time = 214016 ms, elapsed time = 75999 ms. |
This is where things got funny, I promise.
Creating The View
Here I am, a fairly experienced SQL Server user. I at least remembered that you can’t use DISTINCT in an indexed view.
You can GROUP BY, as long as you remember to COUNT BIG.
Let’s do that.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 |
Then I went to add the unique clustered index.
1 2 |
CREATE UNIQUE CLUSTERED INDEX cx_distincto ON dbo.distincto (UserId, LastEditorUserId); |
Then I waited.
And waited.
And waited.
And after about 30 seconds (seriously, that’s how impatient I am), I decided to see what the create index was up to.
It had the same plan as when I ran the query, and took about the same amount of time.
Feeling a little bit crazy, I backtracked up to the point where I created the index on Badges, and re-created the indexed view.
The new plan for creating indexed view clustered index morphed back into the “fast” plan for the query.
It also only took about 6 seconds.
Is There A Moral?
There are a few!
- Missing index requests are kinda bananas
- Sometimes the lack of a missing index request isn’t bananas
- If you want to create an indexed view for a slow query, you can expect creating the index to take at least as long as running the query
- If you want to create indexed views faster, tuning the underlying indexes might not be a bad idea
Thanks for reading!
1 Comment. Leave new
Wouldn’t a filtered index be better in this case (I know, SQL Server does not suggests it, but see 1.)? Together with the userid-index on the Badges table it should produce the best performance
CREATE INDEX ix_filtered
ON dbo.Posts ( OwnerUserId) INCLUDE (LastEditorUserId ) WHERE LastEditorUserId > 0;
Furthermore the originial SELECT / VIEW could be optimized too, if you not need the COUNT() on the badges table:
SELECT p.OwnerUserId,
p.LastEditorUserId
FROM dbo.Posts AS p
WHERE p.LastEditorUserId > 0
AND EXISTS (SELECT * FROM dbo.Badges b WHERE b.UserId = p.OwnerUserId)
GROUP BY p.OwnerUserId, p.LastEditorUserId;
PS: indexed views are a nice feature, but I hate it that I have usually to specify the WITH (NOEXPAND) hint to get it used