SQL Server ROLLUP and CUBE. Is it Same

Hey Folks,

You might have heard about this feature which was introduced earlier in the SQL Server. This was introduced with the syntax name WITH ROLLUP and WITH CUBE which is Non-Ansi standard keywords, and being deprecated. Since it still works for now, it soon will be removed from the future version of SQL Server. So it’s better, if you stop using this.

Since then, with the release of SQL Server 2008; these keywords has been modified and being replaced by ROLLUP and CUBE extensions to GROUP BY which generate OLAP-type summaries of the data with subtotals and totals.

• The ROLLUP and CUBE aggregate functions generate subtotals and grand totals as separate rows, and supply a null in the GROUP BY column to indicate the grand total.
• ROLLUP generates subtotal and total rows for the GROUP BY columns.
• CUBE extends the capabilities by generating subtotal rows for every GROUP BY column.
• ROLLUP and CUBE queries also automatically generate a grand total row.
• A special GROUPING () function is true when the row is a subtotal or grand total row for the group.

I think so, by now you should be very much familiar with GROUPING () function; if that might not be the case with you, then you have an option to get the idea about GROUPING () function, for which I have posted a blog recently. So you can follow up via link;

We will discuss each one of them separately and will see where actually these both (ROLLUP and CUBE) differs from each other:

ROLLUP () Function:

The ROLLUP option, placed after the GROUP BY clause, instructs SQL Server to generate an additional total row. This will be clear to you if you see this example:

Let see the table that I have used in the example:

So, now the GROUPING () function is being used by a CASE expression to convert the total row to something understandable.

If I want to display the “Class” and “Salary” with the “Lname”, then we can write the query:

SELECT LName,GROUPING(Class) AS 'Grouping',
Class,
CASE GROUPING(Class)
WHEN 0 THEN Class
WHEN 1 THEN 'All Classes'
END AS ClassRollup,
SUM(Salary) AS Salary
FROM Students   
GROUP BY ROLLUP(Class)

OOPS!!! It displays an error-

Msg 8120, Level 16, State 1, Line 1

Column ‘Students.S_Id’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. 

Well can you guess it the error? If not, then I will suggest you to look at my query once again, is it ok enough to display the “LName” attribute?

Still, if you won’t find any answer, then you don’t have to bother much about it because I have posted a blog on this error as “Aggravating Queries”, which will guide you about some aspects of GROUP BY clause. So just browse this link;

So, now I think you will understand what wrong I had did in my previous query and had modified it as shown below, inserting the non-aggregated column into the GROUP B Y clause:

SELECT LName,GROUPING(Class) AS 'Grouping',
Class,
CASE GROUPING(Class)
WHEN 0 THEN Class
WHEN 1 THEN 'All Classes'
END AS ClassRollup,
SUM(Salary) AS Salary
FROM Students
GROUP BY ROLLUP(Class,LName)

The result can be seen as:

Here, you can see that ROLLUP has added new row at line 4, 8, 12 and 13.

CUBE () Function:

A CUBE query is the next logical progression beyond a rollup query. It adds subtotals for every possible grouping in a multidimensional manner, just like Analysis Services.

This will be clear to you if you see this example in which the CUBE query has subtotals for each “Class” and “Lname”:

SELECT
CASE GROUPING(Class)
WHEN 0 THEN Class
WHEN 1 THEN 'All Classes'
END AS Class,
CASE GROUPING(LName)
WHEN 0 THEN LName
WHEN 1 THEN 'All Names'
END AS LName,
SUM(Salary) AS Salary
FROM Students
GROUP BY CUBE(LName,Class)

The result can be seen as:

The row which has highlighted above is an auto-generated row which has GROUPING value 1 in both the cases, so it displays all and sums up the result.

Hence, this was all about ROLLUP and CUBE in SQL Server.

Hope you got it understood well 🙂

And also comments on this!!

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook