Difference between cube and rollup in SQL Server

Suggested Videos
Part 101 - Grouping sets in SQL Server
Part 102 - Rollup in SQL Server
Part 103 - Cube in SQL Server



In this video we will discuss the difference between cube and rollup in SQL Server.



CUBE generates a result set that shows aggregates for all combinations of values in the selected columns, where as ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

Let us understand this difference with an example. Consider the following Sales table.


SQL Script to create and populate Sales table

Create table Sales

(

    Id int primary key identity,

    Continent nvarchar(50),

    Country nvarchar(50),

    City nvarchar(50),

    SaleAmount int

)

Go

Insert into Sales values('Asia','India','Bangalore',1000)

Insert into Sales values('Asia','India','Chennai',2000)

Insert into Sales values('Asia','Japan','Tokyo',4000)

Insert into Sales values('Asia','Japan','Hiroshima',5000)

Insert into Sales values('Europe','United Kingdom','London',1000)

Insert into Sales values('Europe','United Kingdom','Manchester',2000)

Insert into Sales values('Europe','France','Paris',4000)

Insert into Sales values('Europe','France','Cannes',5000)

Go

ROLLUP(Continent, Country, City) produces Sum of Salary for the following hierarchy
Continent, Country, City
Continent, Country, 
Continent
()

CUBE(Continent, Country, City) produces Sum of Salary for all the following column combinations
Continent, Country, City
Continent, Country, 
Continent, City
Continent
Country, City
Country,
City
()

SELECT Continent, Country, City, SUM(SaleAmount) AS TotalSales

FROM Sales

GROUP BY ROLLUP(Continent, Country, City)

SELECT Continent, Country, City, SUM(SaleAmount) AS TotalSales

FROM Sales

GROUP BY CUBE(Continent, Country, City)

You won't see any difference when you use ROLLUP and CUBE on a single column. Both the following queries produces the same output.

SELECT Continent, Sum(SaleAmount) AS TotalSales

FROM Sales

GROUP BY ROLLUP(Continent)

-- OR

SELECT Continent, SUM(SaleAmount) AS TotalSales

FROM Sales

GROUP BY CUBE(Continent)