You know everything about GROUP BY. Do you?

SQL is a stout language and SQL Server has so many features that it’s impossible to be an expert in everything. I see a lot of people gravitate towards performance; but, that’s not the best place to focus. If you don’t know the fundamentals of the language then you will never be the best performance tuner.

Today we’re going to look at GROUPING SETS. This is a neat feature that I have never seen anyone use. Perhaps because it’s value is limited for any application purposes where you can simply group in a report or data grid. Where I think I would have seen people using this is for admin and discovery related tasks.

Let’s say that your manager wants to know how much space is used per volume and by data file type. How would you proceed? One simple way is to query sys.master_files cut & paste the data to Excel. From there you’d need to do a few more operations and you’d have your report. Another way may be to use REPORT BUILDER to quickly generate a report. But both of these are much slower than just using SQL to get the groupings.

Here’s a quick example to get a total usage in MB by file type for each volume (when not using mount points):

SELECT type_desc as FileType,
LEFT(physical_name, 1) as Volume,
SUM(size*8/1024) as [Size MB]
FROM
sys.master_files
GROUP BY
GROUPING SETS    (
                                           (type_desc, left(physical_name, 1)),
                                           (type_desc),
                                           (left(physical_name, 1))
                                    )

Using GROUPING SETS gives us a subtotal for each grouping set. Here’s the result:

image

You could also use CUBE but the result would include an additional subtotal, which may be helpful.

SELECT type_desc as FileType, left(physical_name, 1) as Volume,
SUM(size*8/1024) as [Size MB]
FROM
sys.master_files
GROUP BY CUBE
(type_desc, left(physical_name, 1))

image

One thought on “You know everything about GROUP BY. Do you?

  1. Adding the grouping set ,() after (left(physical_name, 1)) will also give the additional sub total that cube gives.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s