THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

The DIY guide for local-global aggregation

This blog has moved! You can find this content at the following new location:

https://SQLServerFast.com/blog/hugo/2016/08/the-diy-guide-for-local-global-aggregation/

Published Wednesday, August 31, 2016 11:08 PM by Hugo Kornelis

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Paul said:

The links aren't links, just styles...

August 31, 2016 6:44 PM
 

tobi said:

Small correction: MIN and MAX can't be indexes because it's not possible to efficiently remove rows from these aggregates. Funnily, you said it's not rocket science when in fact it is impossible :)

September 3, 2016 8:33 AM
 

Hugo Kornelis said:

@Paul: Thanks for pointing that out. Somehow, the publishing software I use for this blog sometimes decides that links arer highly overrated and changes them to styles. I'll change them back to be proper links.

@Tobi: You arer right, I messed this up. The blog post is intended to be a generic guide on DIY local-global aggregation, and there are definitely cases (for instance, when trying to force batch mode for queries on columnstore tables) where you can apply this method on MIN/MAX.

When I decided to use an example that is realistic, and not on columnstore (because I already have lots of those examples in the columnstore stairway at SQL Server Central), I did not realize that by adding view indexability into the equation, I introduced an unintended contradiction.

So I guess that the accurate text would have been that MIN and MAX arre impossible to do with indexed views, but that in other cases of local-global aggegation you can use the same technique (and that is still not rocket science).

Thanks for keeping me sharp!

September 15, 2016 4:48 PM

Leave a Comment

(required) 
(required) 
Submit

About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.
Privacy Statement