Difference between cube and rollup in SQL Server



difference between cube and rollup in sql server 2008
sql server rollup vs cube
difference between cube and rollup in sql server 2005
rollup and cube in sql server 2005

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.

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)

Link for all dot net and sql server video tutorial playlists
https://www.youtube.com/user/kudvenkat/playlists?sort=dd&view=1

Link for slides, code samples and text version of the video
http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-cube-and-rollup-in.html

Source: kudvenkat