Welcome to TSQL Tuesday! Click the picture on the right for more information.
TSQL is a great tool for aggregating lots of data but what do you do with it once it’s aggregated? Often the answer is to aggregate it again through subtotals and totals. Normally this will require a second query or if you want to see the totals “in-line”, a trip to Excel. But, when using aggregation, you can use the WITH ROLLUP or WITH CUBE operator in the GROUP BY clause to generate totals.
MSDN explains the difference between ROLLUP and CUBE as follows:
-
CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.
-
ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.
If that seems a little fuzzy don’t worry about it – just run the query below with each operator and note the difference in output.
This query will run on SQL 2005 or 2008 DBs and shows the number of reads since the last restart. Normally these operators (ROLLUP & CUBE) are used to present an application with pre-aggregated data for performance reasons. However in this example it is quite useful for ad hoc I/O performance queries.
SELECT
OBJECT_SCHEMA_NAME(S.object_id) AS SchemaName
,OBJECT_NAME(S.object_id) AS ObjectName
,SUM(S.user_seeks + S.user_scans + S.user_lookups) AS TotalReads
FROM
sys.dm_db_index_usage_stats AS S
INNER JOIN
sys.indexes AS I
ON S.object_id = I.object_id
WHERE
OBJECTPROPERTY(S.object_id,‘IsUserTable’) = 1
AND I.index_id = S.index_id
AND S.database_id = DB_ID()
GROUP BY
OBJECT_SCHEMA_NAME(S.object_id)
,OBJECT_NAME(S.object_id)
–WITH ROLLUP / CUBE
ORDER BY
1,2,3
In Figure1 we see the results from the basic aggregation.
Figure 1
Adding WITH ROLLUP produces the following. Row 1 (NULL NULL) is a grand total. Note that row 2 gives the sub-total for the Person group and so on.
Figure 2
Finally WITH CUBE produces all possible subtotals.
Figure 3
These operators aren’t Rocket Science but if they save you acouple minutes every time you’d have to make a trip to Excel then I think they are worth adding to the toolbox.