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)

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)