Use of CUBE and ROLLUP – Difference of CUBE and ROLLUP in SQL Server 2005/2008

SQL Server 2005+ came up with exciting facility of CUBE and ROLLUP clause. Herewith, I am going to show you use and difference of CUBE and ROLLUP in SQL Server 2005 and SQL Server 2008.

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

Let us see one practical scenario which will make your concept much clear about both these exciting features.

--Table 1 for Demo

create table dbo.Orders

(

OrderID varchar(5),

OrderDate varchar(50)

)

 

--date for table1

insert into dbo.Orders

select 'A1000',GETDATE()-1 union all

select 'A1001',GETDATE()

 

--table 2 for demo

create table dbo.OrderDetails

(

OrderID varchar(5),

SampleNo Varchar(8),

SampleDate varchar(50)

)

 

--data for table 2

insert into dbo.OrderDetails

select 'A1000','A1000-01',GETDATE()-1 union all

select 'A1000','A1000-02',GETDATE() union all

select 'A1000','A1000-03',GETDATE() union all

select 'A1001','A1001-01',GETDATE() union all

select 'A1001','A1001-02',GETDATE()

 

--let us check both table

select * from dbo.Orders

select * from dbo.OrderDetails

 

--let us check how many samples came for each order id

select

o.orderID,

count(od.SampleNo) as 'Total Sample'

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID

 

/************************************************************

answer would be.

orderID Total Sample

------- ------------

A1000   3

A1001   2

 

(2 row(s) affected)

 

*************************************************************/

 

 

select

o.orderID,

count(od.SampleNo) as 'Total Sample'

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID

with cube

/************************************************************

answer would be.

orderID Total Sample

------- ------------

A1000   3

A1001   2

NULL    5

 

(3 row(s) affected)

 

*************************************************************/

 

 

select o.orderID,

count(od.SampleNo) as 'Total Sample'

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID

with rollup

/************************************************************

answer would be.

orderID Total Sample

------- ------------

A1000   3

A1001   2

NULL    5

 

(3 row(s) affected)

 

*************************************************************/

 

--You will not be able to find big difference between ROLLUP and CUBE with above query.

--isn't there any difference at all? NO, there is a difference. let us see the difference.

 

 

 

select

case when grouping(o.orderID)=1 THEN 'Total Sample on Date' else o.OrderID end as OrderID,

case when grouping(od.SampleDate)=1 THEN 'Total Sample for OrdID' else od.SampleDate end as SampleDate,

count(od.SampleNo) as 'Total Sample'

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID,SampleDate

with cube

/************************************************************

answer would be.

OrderID              SampleDate                                         Total Sample

-------------------- -------------------------------------------------- ------------

A1000                May 24 2009  3:53PM                                1

Total Sample on Date May 24 2009  3:53PM                                1

A1000                May 25 2009  3:53PM                                2

A1001                May 25 2009  3:53PM                                2

Total Sample on Date May 25 2009  3:53PM                                4

Total Sample on Date Total Sample for OrdID                             5

A1000                Total Sample for OrdID                             3

A1001                Total Sample for OrdID                             2

 

(8 row(s) affected)

 

*************************************************************/

 

 

select

case when grouping(o.orderID)=1 THEN 'Total Sample on Date' else o.OrderID end as OrderID,

case when grouping(od.SampleDate)=1 THEN 'Total Sample for OrdID' else od.SampleDate end as SampleDate,

count(od.SampleNo) as 'Total Sample'

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID,SampleDate

with rollup

/************************************************************

answer would be.

OrderID              SampleDate                                         Total Sample

-------------------- -------------------------------------------------- ------------

A1000                May 24 2009  3:53PM                                1

A1000                May 25 2009  3:53PM                                2

A1000                Total Sample for OrdID                             3

A1001                May 25 2009  3:53PM                                2

A1001                Total Sample for OrdID                             2

Total Sample on Date Total Sample for OrdID                             5

 

(6 row(s) affected)

 

*************************************************************/

In last two queries, you find the difference between CUBE and ROLLUP. Second from last query with CUBE shows you 8 rows while last query which is using ROLLUP, showing 6 rows. CUBE will show you sample received for each orderID on each day and finally it will show you total sample received whereas ROLLUP will show you bit less summary like: which day for which ORDERID, how many samples come in. and finally total sample received.

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com