Partitioned Views, Aggregates, and Cool Query Plans

The Max for the Minimum

Paul White (obviously of course as always) has a Great Post, Brent® about Aggregates on partitioned tables

Well, I’m not that smart or good looking, so I’ll have to settle for a So-So Post about this.

There are actually quite a few similarities between the way a partitioned table and a partitioned view handle these things.

Building on previous examples with the Votes table converted into a partitioned view, let’s try a couple queries out.

Selecting the global min and max give me this query plan.

And I know, it looks big and mean. Because it kind of is.

It keeps going, too.

BUT LOOK HOW LITTLE WORK IT DOES!

wtf I hate millennials now

Just like in Paul’s post that I linked to above, each one of the top operators is a TOP 1.

For the Min, you get a forward scan, and for the Max you get a backwards scan.

That happens once per table in the partitioned view.

Easier to visualize

If we focus on a single table, it’s easier to parse out.

Simpleton

A lot of people may complain that there are two index accesses here, but since SQL Server doesn’t have anything quite like a Skip Scan where it could hit one of the index and then jump to the other end without reading everything in between, this is much more efficient.

Thanks for reading!

Brent says: remember, a scan doesn’t mean SQL Server read the whole table, and a seek doesn’t mean it only read a few rows. It’s so hard to tell this stuff at a glance in execution plans, especially in estimated plans.

, , ,
Previous Post
It’s the last week to save $2,997.50 on my Live Class Season Pass.
Next Post
Coming in SQL Server 2019: Approximate_Count_Distinct

11 Comments. Leave new

Menu
{"cart_token":"","hash":"","cart_data":""}