Difference between cube and rollup in SQL Server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 1 0 4 of sequence /
tutorial in this video we'll discuss the
difference between cube and roll up in
sequel server cube generates a result
set that shows aggregates for all
combinations of values in the selected
columns
whereas roll-up generates a result set
that choose aggregates for a hierarchy
of values in the selected columns let's
understand this with an example we'll
use this sales table notice we have sale
amount column in addition these three
columns city country and continent there
is a hierarchy relationship between
these three columns city is present
within a country and country is present
within a continent now if we use roll up
on those three columns and if the
aggregation that we want to perform is
sum on the sale amount column then this
roll-up is going to produce some of sale
amount grouped by all the three columns
that is continent country and city sum
of sale amount grouped just by these two
columns that is continental country sum
of sale amount grouped just by continent
and the grand total so here we are
getting that aggregation that is sum of
sale amount based on the hierarchy
relationship between those three columns
whereas when we use Cube on the same set
of three columns then this is going to
produce some of sale amount based on all
the possible combinations of these three
columns so we are going to get sum of
sale amount based on all the three
columns continent country city continent
country continent city just by continent
country city country city and the grand
total so cube is going to produce the
aggregations based on all the possible
combinations of those three columns
whereas roll-up is going to produce the
aggregations based on just the hierarchy
relationship of those three columns
let's look at this in action let's flip
to sequence of a management studio so
with no result we want continent country
city all the three dimensions and then
sum of sale amount so we are using
roll-up and on those three columns so
when we execute this notice the first
and second
rose we have the sum of sale amount
grouped by all the three columns all the
three levels and then row number three
we have the sum of sale amount group
just by the continent and country level
and if you look at rule number seven
notice that here in this case the sale
amount is grouped just by the continent
level so the groupings here are
happening only based on the hierarchy
relationship between those three columns
and if you look at row number 15 we have
the grand total so that's what rollup
does now let's use cube instead of
roll-up what is cube going to do it's
going to perform this aggregation that
is sum of sale amount based on all the
possible combinations of these three
columns so when we used roll-up look at
the number of rows produced it is 15 now
let's use cube and look at the number of
rows pre used for t3 because definitely
when we combine all the possible
combinations of these three columns we
are definitely going to get you know
more results than when we group you know
the sum of sale amount just based on the
hierarchy relationship so for example
can we find the grouping done by
continent and City absolutely so we want
the grouping based on just the continent
and city that means country is going to
be null but not continent and City so
if you look at this row right here
notice that we have continental city but
countries now and we have the total sale
amount now let's look at another example
can I find where we can say we get sale
amount group just by city absolutely in
this case we should have only city but
the other two columns that is continent
and country should be enough so if you
look at rule number nine notice
continent country are now but City is
not so in this case we have the sale
amount group just by city so with Cube
you get all the possible combinations of
the columns whereas with roll-up you
only get you know the aggregation
performed based on the hierarchy
relationship between those three columns
that we pass to the role of function now
you won't see any difference when you
use roll-up and cube on a single column
so if you look at the query right here
we are getting just two columns in the
result set the name of the continent and
sum of sale amount and if you look at
the group by clause here - the role of
function we are passing just the
continent and the same is the case with
Q function so when we execute these two
queries within the result set you won't
find any visible difference let's look
at that quickly in action so we want
just the continent and then sum of sale
amount grew by cube and I'm using just
continent let's make a copy of this and
instead of cube I'm going to use roll-up
so when we execute both of these two
queries we are going to get identical
results so when you are using you know
roll-up and cube and when you're passing
only one column to those functions
within the result you won't see any
visible difference thank you for
listening and have a great day