Search

Tuesday, September 20, 2011

Difference between CUBE and ROLLUP


Some time we need to summarize data very quickly without writing very complex query. CUBE And ROLL UP are great commands that give us very good summary options.
We can summarize the data without writing dirty and complex queries with the help of CUBE and ROLL Up Operators.
CUBE generates a result set that represents aggregates for all combinations of values in the selected columns.
ROLLUP generates a result set that represents aggregates for a hierarchy of values in the selected columns.
Example to see the difference
DECLARE @t TABLE(
ItemName varchar(100),
Color varchar(100),
Combinations int
)
insert into @t values ('Chair','Black',10)
insert into @t values ('Chair','Blue',10)
insert into @t values ('Table','Black',10)
insert into @t values ('Table','Blue',10)
GO
select ItemName,Color,SUM(Combinations) as TotalCnt from @t
Group by ItemName,Color with Rollup
GO
select ItemName,Color,SUM(Combinations) as TotalCnt from @t
Group by ItemName,Color with cube
GO
Rollup added below rows in addition to general group by rows.
Chair   NULL    20
Table   NULL    20
NULL    NULL    40


Cube added below rows in addition to general group by rows.
Chair   NULL    20
Table   NULL    20
NULL    NULL    40
NULL    Black   20
NULL    Blue    20


Difference between Cube and rollup is, cube calculated the sum of combinations w.r.t both Item and Color, where as rollup calculated the sum of combinations only w.r.t Item. In other words, CUBE evaluates aggregate expression with all possible combinations of columns specified in group by clause, where as the Rollup evaluates aggregate expressions in only the order of columns specified in group by clause.To easily remember this remind the shape of cube and rollup.

2 comments: