Surprised? I think this example (or one very much like it) surprises those of us who started our careers as programmers. Many of us follow up this lesson with the related “UPDATE statements don’t have GROUP BY clauses” and then the lesson “How do I use CTEs?”

Indexed Views Don’t Support Max/Min Aggregates

Indexed views support a couple aggregate functions like COUNT_BIG() and SUM(). And with some trickery you can calculate AVG() and STDEV(). But SQL Server restricts the use of MAX() and MIN() in indexed views despite how useful they’d be.

It might help to understand why. SQL Server maintains indexed views as physical database objects and it can maintain aggregate values like COUNT() and SUM() by processing only the changing rows in the base table while safely ignoring the rest of the table. But I can’t say the same for MAX() or MIN(). If SQL Server supported MAX() and MIN() in indexed views, then when you delete the row in the base table that represents the MAX() value, SQL Server would have to search the rest of the base table to find the new MAX() value.

Check out the microsoft.connect feature suggestion Expand aggregate support in indexed views (MIN/MAX). Aaron Bertrand created this Connect suggestion and I like it because it shows how effective constructive feedback can be. I like it because of its description, comments and the useful workaround. The Microsoft team even gave some insight into how they almost included this feature in SQL 2008. This connect item only seems to be lacking an E.T.A. so go and cast your vote!

Bonus Content

I didn’t draw any illustrations this week so I’m including some bonus content (admittedly written by others):

Non-obvious Things From Twitter Friends

Trivia

Did you know Michael Keaton (born Michael Douglas) changed his name to avoid confusion with that other guy? Pretty wise.

Jes Schultz Borland reminded me recently that good writers use more active verbs. I took that advice to heart and turned the writing of this article into an exercise. I avoided using words like is, was or are here and I think it turned out pretty well.

Heh– I promise to expose sp_blitzindex in public more. I’ve done a bit of a soft launch with it to collect initial feedback and additional ideas. That’s gone super well, so I’m going to webcast it next week! Thanks for the lovely link, and glad you checked it out!

“what if I chose a clustered index whose columns include the primary key. So SQL Server shouldn’t need the uniquifier right?” => No, because the PK can be dropped afterwards. Then, you’d have a CI without uniquifier although one is needed.

Screen Actors Guild regulations have long required that all members have “unique working names”. That’s why Michael Keaton couldn’t use his birth name (Michael Douglas) and why Michael J. Fox had to use the “J.” (there was already a “Michael Fox” – who ever heard of that guy?).

More and more trivia: Wikipedia tells me that the “J” in Michael J. Fox’s name doesn’t stand for anything. Michael J. Fox was born Michael Andrew Fox but took the J as “an homage to actor Michael J. Pollard”. For what it’s worth Michael J. Swart is short for Michael John Swart.