In SQL Server, both ROLLUP and CUBE are sub-clause of the GROUP BY clause and are used in conjunction with aggregate functions to produce summary reports. It helps to generate multiple group sets using the hierarchy. To enhance the capabilities of grouping and aggregation, SQL Server provides two powerful extensions: CUBE and ROLLUP. These extensions allow for the creation of more sophisticated and multi-dimensional summaries of data.
Rollup vs Cube
Here we are going to consider the supermarket database having tables (Orders, Customers, Employees, OrderDetails ). Now, we are going to calculate the total and subtotal by roll up and cube.
Actual Difference
|
ROLL UP |
CUBE |
|---|---|
|
Ex: ROLLUP(col1, col2, col3) |
Ex: CUBE(col1, col2, col3) |
|
Grouping Sets: (col1 ,col2, col3) (col1, col2) (col3) () |
Grouping Sets: (col1 ,col2, col3) (col1, col2) (col2, col3) (col1, col3) (col1) (col2) (col3) () |
Polarities
|
CRITERIA |
ROLL UP |
CUBE |
|---|---|---|
|
Syntax |
SELECT c1, c2, c3, aggregate_function (c4) FROM table_name GROUP BY ROLLUP (c1, c2, c3); |
SELECT c1, c2, …, cn, aggregate_function (cx) FROM table_name GROUP BY CUBE (c1, c2, …, cn); |
|
Definition |
ROLL UP performs aggregation on a set of columns, generating subtotals and grand totals along a specified hierarchy. |
CUBE performs aggregation on the dimensions given in the CUBE(). |
|
Result Set |
It only generates grouping sets based on hierarchy of column chosen. |
It gives the subset(2^n) grouping sets, where n is the columns mentioned in the cube. |
|
Hierarchy |
Specifies a hierarchy of columns along which subtotals and grand totals are calculated. |
Considers all possible combinations of columns, exploring multiple hierarchies simultaneously. |
|
Aggregation Level |
It is also known as “Super-aggregate-Rows”. When GROUPING() was used, it returns 1 when NULL or returns 0. |
It returns the aggregates of all possible combinations. |
|
Efficiency |
It provides a more controlled summary based on a predefined hierarchy. It helps to specify the exact columns along which subtotals and grand totals are calculated. |
Generates a comprehensive summary by considering all possible combinations of columns. Offers a complete set of all possible aggregation levels. |
|
Use Case |
When the user want to generate subtotals and grand totals along a particular hierarchy. |
For multidimensional analysis, to examine data from multiple perspectives. |
|
Example |
SELECT OrderDate, CustomerID, SUM(TotalAmount) AS TotalOrderAmount FROM Orders GROUP BY ROLLUP (OrderDate, CustomerID); |
SELECT OrderDate, CustomerID, SUM(TotalAmount) AS TotalOrderAmount FROM Orders GROUP BY CUBE (OrderDate, CustomerID); |
|
Output |
|
|
Which is More Better?
ROLLUP is generally more efficient than CUBE because it calculates subtotals based on a specific hierarchy and ROLL UP with multiple columns gives hierarchy among the GROUPBY clause . It is also used with GROUPING() clause to return 1 for NULL or 0.CUBE involves more computation due to considering all possible combinations and increases the space and time complexities.
Conclusion
It depends on the analytics needed to implement for user consideration. Rollup is users modifiable and used for better summarization. It performs on multiple dimensions with single query which results in giving more detailed analysis .However ,Cube is used for returning all possible combinations .