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:

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))