What is CUBE and ROLLUP???

Question

 

Hi,

Can anyone explain me that WHAT IS CUBE AND ROLLUP Statments for?? Why to use, Where to use and When to use???

Are they usefull in any point of view from ADO.NET???

Answer 1

CUBE and ROLLUP are sometimes called the super aggregates but these are not functions but operators used for grouping data, CUBE includes duplicate while ROLLUP does not to get NULL values you need to use the GROUPING clause.  There are changes coming in 2008 you cannot use the WITH CUBE and WITH ROLLUP versions of these operators because per the 2008 BOL those are not ANSI SQL compliant.  The first link is the 2008 BOL and the last is SQL Server SELECT code samples which includes samples of both operators.

 

http://msdn2.microsoft.com/en-us/library/ms177673(SQL.100).aspx

 

http://msdn2.microsoft.com/en-us/library/ms187731.aspx

 

Answer 2

They are not statements, rather they are modifiers for the GROUP BY clause.  They both give you additional summarizations returned from your group by aggregates.  Check the following example:

 

create table testCubeRollup
(
    group1 int,
    group2 int,
    value int
)
insert into testCubeRollup
select 1, 1, 10
union all
select 1, 2, 20
union all
select 1, 3, 30
union all
select 2, 1, 40
union all
select 2, 2, 20
go

 

With rollup adds summarizations from left to right:

select group1, group2, sum(value)
from   testCubeRollup
group by group1, group2 with rollup

 

group1      group2     
----------- ----------- -----------
1           1           10
1           2           20
1           3           30
1           NULL        60
2           1           40
2           2           20
2           NULL        60
NULL        NULL        120

 

Where the nulls are, its summarizing all of thevalues for that group.  So 1 NULL 60 is the summarization of the 3 rows with 1.  (Check out the GROUPING() function for a way to not just return NULL..)

 

CUBE goes both directions, rather than just left to right. 

 

select group1, group2, sum(value)
from   testCubeRollup
group by group1, group2 with cube

group1      group2     

 

 

group1      group2     
----------- ----------- -----------
1           1           10
1           2           20
1           3           30
1           NULL        60
2           1           40
2           2           20
2           NULL        60
NULL        NULL        120
NULL        1           50
NULL        2           40
NULL        3           30

 

See, it added 3 more rows that were centered on group 2.

 

Hope this helps

Answer 3

Hi Louis,
Ur example realy helps me to understand both these operator.

So, that means if we are using CUBE it will consider all the column that are used to group your data and summarize all columns that are used to group the resultset.

AND

In case of Rollup it will consider only the first column and summarize data of that first column which is present(used) to group your data.

-----------------------------
BUT Tell me one thing that can we use both of these operators only with Grouping statements or querry's or we can use thse in a simple select querry also.
BUT i think we can';t use these in the simple select querry caz as your said these are only used for summarize your data and when i am trying to use these with a simple qyuerry it gives me error.

Wht your opinion???

Answer 4

You are right.  They have to be a part of a group by clause.  They cube and rollup the aggregate results from the groupings.