SQL Server tutorial for beginners - Difference between cube and rollup in SQL Server

Database, Information Technology

SQL Server tutorial for beginners

149 Lessons

Difference between cube and rollup in SQL Server

difference between cube and rollup in sql server 2008
sql server rollup vs cube
difference between cube and rollup in sql server 2005
rollup and cube in sql server 2005

In this video we will discuss the difference between cube and rollup in SQL Server.

CUBE generates a result set that shows aggregates for all combinations of values in the selected columns, where as ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

Let us understand this difference with an example.

ROLLUP(Continent, Country, City) produces Sum of Salary for the following hierarchy
Continent, Country, City
Continent, Country,

CUBE(Continent, Country, City) produces Sum of Salary for all the following column combinations
Continent, Country, City
Continent, Country,
Continent, City
Country, City

SELECT Continent, Country, City, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY ROLLUP(Continent, Country, City)

SELECT Continent, Country, City, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY CUBE(Continent, Country, City)

You won’t see any difference when you use ROLLUP and CUBE on a single column. Both the following queries produces the same output.
SELECT Continent, Sum(SaleAmount) AS TotalSales
FROM Sales

— OR

SELECT Continent, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY CUBE(Continent)

Text version of the video


All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

    1NF 2008 2NF ACID Tests add check constraint to existing column add default constraint to existing column in sql server add foreign key to existing table in sql server add new column with default constraint add unique column to table sql server adhoc sql advanced joins Advantages after delete After insert after triggers after() all tables Alter alter stored procedure sql server alter table add check constraint sql server alter table add default constraint to existing column sql server alter table add foreign key constraint sql server alter table add foreign key sql server alter table check constraint alter table check constraint sql server altering alternatives analyze deadlock graph sql server ASCII asp.net sql server deadlock before delete trigger example before delete trigger in sql server before insert update delete trigger before update trigger in sql before update trigger in sql server before update trigger in sql server 2008 r2 before update trigger mssql before update trigger sql server 2005 before update trigger vs after update benefits Better between built-in calculate exact age in sql server can database views be updated can sql view be updated cannot drop database because it is currently in use Cascading case statement cast catch deadlock exception c# char CHARINDEX check constraint check if table exists sql Clustered clustered index explained clustered index in dbms clustered index order clustered index primary key clustered vs. nonclustered index structures in sql server Coalesce function coalesce in sql server 2008 coalesce sql problem coalesce() example column default columns common table expression common table expressions concurrency problems database concurrency problems lost update constraints convert correlated correlated subquery how it works correlated subquery in dbms correlated subquery tutorial correlated subquery vs non correlated subquery correlated subquery vs subquery correlated subquery working covering queries crear trigger sql server create create before delete trigger sql server create before insert trigger in sql server create function with schemabinding example create global temporary table create sql view with parameters create table referential integrity sql create trigger on all server for ddl events create unique column in sql server Creating Creating Tables cross apply sql server example cross join cross self join cte cte insert update delete cte reference itself cte update and insert cte update join cte vs table variable cte vs temp table vs table variable cursors database database acid examples database acid properties database acid properties with example database concurrency lost update database design normalization database index basics database index best practices database index creation database index example database index guidelines database index how it works database index performance database index simple explanation database index speed up query performance database index techniques database index tutorial database index when to use database locks database normalization database normalization 1nf database normalization 2nd normal form database normalization 3nf database normalization example database normalization made easy database normalization normal form database normalization process database normalization questions and answers database normalization tutorial for beginners database systems normalization database transaction lost update date Date Time DateName datetime day DBCC CHECKIDENT dbcc opentran example dbms dbms 3nf dbms acid properties dbms deadlock dbms normal forms tutorial dbms normalization dbms normalization with example ddl trigger example ddl trigger example in sql server ddl trigger on all server ddl trigger server level ddl triggers can be used to deadlock database deadlock example deadlock example in sql server 2005 deadlock example in sql server 2008 deadlock explanation deadlock graph analysis deadlock graph analyze deadlock graph sql server deadlock graph sql server 2008 deadlock in sql server 2008 with example deadlock questions deadlock retry logic deadlock tutorial default DEFAULT Constraint defined delete default constraint in sql server delete sql constraint delete sql stored procedure delete trigger before delete sql server derived tables DETERMINISTIC difference difference between intersect and except in sql server difference between intersect and union difference between join and subquery in sql difference between not in and except in sql server difference between phantom read and non-repeatable read difference between read committed snapshot and snapshot isolation level difference between repeatable read and serializable difference between subquery and correlated subquery difference between union and join in sql server difference between union and union all different normal forms dirty read concurrency dirty read problem in concurrency control distinct DML trigger dml triggers drop drop check constraint sql server drop sql stored procedure drop unique clustered index drop unique constraint sql server drop unique index sql server dropping enable read committed snapshot isolation sql server encrypting a function in sql server enforcing error Error Handling example except vs not in sql server Exception Handling exec sp_settriggerorder execute procedure with in and out parameter executing existing table new column default constraint explain default constraint with example Filtering groups first normal first normal form foreign key foreign key constraint in sql example foreign key example in sql server FULL JOIN function functions functions in sql server get id of just inserted row get non matching rows in sql global variables Group By group by sql pivot grouping handle deadlock in asp.net handling sql server deadlock how can you get a deadlock graph from sql server how sql server detects deadlocks how to create before update trigger in sql server 2008 how to find deadlock in sql server how to get month number from date in sql how to improve query performance using index how to log deadlocks in sql server how to output row count in sql stored procedure huge tables ident_current vs scope_identity ident_current vs scope_identity vs identity IDENT_CURRENT('TableName') identity Identity column identity column value IDENTITY_INSERT identity_insert in sql server improve sql query performance index in index and performance sql index performance sql server indexed views Indexes information_schema.tables Inline inline sql inline table valued function parameters inline table valued function with parameters INNER JOIN inner join vs cursor inner self join insert insert into select only some columns instead of instead of delete instead of insert trigger instead of insert update delete intelligent joins intersect vs except IsDate ISNULL function Joining with the same table Joins Joins in SQL Server learn everything about sql learn sql at home learn sql for beginners learn sql programming learn sql queries learn sql step by step left LEFT JOIN left join and null values left join case null left join null check left join null coalesce left join null values left join null values sql server left outer join in sql left outer join replace null values LEN like list lost update problem in concurrency control lost update problem in transaction lower ltrim materialized view explained materialized view in dbms materialized view in sql materialized view join materialized view simple example materialized view tutorial materialized view vs view materialized view youtube Materialized Views mathematical merge in sql merge in sql server merge in sql server 2008 merge in sql server 2008 example merge in sql server example merge statement in sql server merge two tables sql union modification affects multiple base tables modify data using view sql server modify stored procedure in sql server month ms sql get age from date of birth ms sql pivot mssql multi non clustered non repeatable read in dbms non repeatable read in sql non repeatable read sql server Nondeterministic only non matching rows Operator Optional order by outer join outer self join output parameters Parameters performance performance stored procedure vs query performance testing phantom read and non repeatable read phantom read database phantom read in dbms phantom read problem in database phantom read problem in dbms phantom read problem in transaction phantom read vs non repeatable read phantom reads in sql server 2008 phantom rows in sql server pivot pivot example in sql server 2014 prevent saving changes sql server management studio prevent saving changes that require the table to be recreated sql primary key primary key column in sql server primary key enforcement primary key foreign key example in sql server primary vs unique key sql proc proc sql count output procedure procedures pros and cons pros and cons of stored procedures purpose Queries query random data rdbms acid properties re-runnable Read and analyze sql server deadlock log Read committed snapshot isolation level in sql server 2008 read committed snapshot vs snapshot isolation level read sql server deadlock log Recreate recursive cte recursive cte ordering hierarchical result recursive cte with level referential integrity constraint referential integrity constraint in database referential integrity constraint in dbms with example referential integrity constraint in relational database referential integrity example referential integrity in database with example referential integrity sql server relational database self join repeatable read isolation level repeatable read isolation level example replace NULL values Replacing cursors rerunnable reseed id column sql server reseed identity column sql server reset identity column sql server Reset identity value restrict column values in sql server Retrieve return values reverse Right RIGHT JOIN rtrim scalar scalar function in select statement Schema binding scope_identity vs @@identity example scope_identity vs @@identity in sql server scope_identity vs ident_current SCOPE_IDENTITY() scripts second normal form second normal form database second normal form examples second normal form in dbms second normal form with simple example select select distinct sql select into copy table structure select into existing table sql server select into from external database select into from multiple tables select into from two tables select into schema only self join self join employee manager example self join find manager self join operation in dbms self join select query self join to get manager name self referencing table example self referencing table query Server single stored procedure for insert update and delete in sql server snapshot isolation example snapshot isolation in dbms snapshot isolation level example snapshot isolation vs read committed snapshot Sorting sorting union query sp_help stored procedure sp_helptext stored procedure sp_helptext stored procedure in sql server sp_readerrorlog sp_settriggerorder example sp_settriggerorder in sql server sql sql abs function example sql acid properties sql add unique constraint existing column sql age from dob sql alter table column sql alter view sql before delete trigger sql calculate power sql calculate square root sql cannot insert duplicate key in object sql case check if null sql ceiling function example sql check if string is valid date sql clustered vs nonclustered index sql coalesce firstname lastname sql coalesce keyword sql coalesce multiple columns sql coalesce operator sql coalesce string sql commit rollback sql complex join queries examples sql concatenate int and string sql concatenate int and varchar sql convert date dd/mm/yyyy sql convert date to dd/mm/yyyy sql convert date to nvarchar sql convert datetime to date sql convert int to string sql convert text to lowercase sql convert text to uppercase sql count characters in string sql covered query sql create column if not exists sql create custom function sql create database tutorial sql create function with encryption sql create table if not exists sql create table only if it doesn't exist sql create trigger sql create trigger after insert sql create view from query sql create view two tables sql cross join sql current_timestamp example sql cursor explained sql cursor inner join sql cursor processing sql cursor tutorial sql datediff birthday age sql datepart date only sql deadlock explained sql delete database sql delete empty space sql delete view sql developer left string sql developer right function sql dob to age sql drop and create table sql drop database sql except example sql except operator example sql exec sp output parameter sql execute output example sql execute output variable sql execute stored procedure sql find index in database sql find pattern in string sql find square root sql floor function sql format date dd/mm/yyyy sql full join example sql full outer join sql full outer join null sql function calculate age sql function in select statement sql function in where clause sql function with encryption sql generate random number between 1 and 100 sql generate random number between range sql get data from two tables sql get day number from date sql get id of inserted row sql get id of last inserted row sql get month name from date sql getdate example sql getutcdate example sql getutcdate function sql group by and having sql group by and sum query sql group by cast date sql group by clause sql group by complex examples sql group by count sql group by date of datetime sql group by datetime sql group by examples sql group by explained sql group by function sql group by having example sql group by multiple aggregate functions sql group by multiple columns sql group by not working sql group by sum total sql group by with multiple columns sql group by youtube sql how to create a view sql if table exists drop and create sql ignore dup key sql index best practices sql information_schema tables sql inner join and outer join sql inner join example sql inner join tutorial sql insert data into temp table sql intersect vs join sql isnull function sql join subquery sql join vs union sql last inserted identity sql left function example sql left join and right join sql left join example sql left join null values sql left join tutorial sql logging deadlocks sql loop for each row in table sql loop through rows sql ltrim rtrim example sql mask characters sql mask phone number sql mask sensitive data sql merge two tables into one sql merge two tables with same columns sql missing index query sql non matching records two tables sql order by tutorial sql outer join null values sql output clause sql pivot interview questions sql pivot multiple aggregate functions sql pivot multiple columns example sql pivot tutorial sql power example sql power exponent sql procedure alter sql profiler deadlock detection sql profiler deadlock graph sql profiler trace deadlocks sql query and index sql query join vs subquery sql query to calculate age from date of birth sql query to reverse string sql rand function example sql referential constraint sql referential integrity constraint violation sql remove blank space at end sql remove empty spaces sql remove trailing blank spaces sql remove whitespace from start of string sql repeat string n times sql Right Join sql right join tutorial sql rollback example sql rollback transaction on error sql round to 2 decimal places sql round to nearest whole number sql rounding decimal places sql saving changes is not permitted sql saving changes is not permitted dropped and recreated sql script to generate test data sql select distinct statement sql select queries sql select statement tutorial sql server sql server 2000 sql server 2005 sql server 2005 deadlock try catch sql server 2008 sql server 2008 concurrency issues sql server 2008 ddl trigger audit sql server 2008 try catch deadlock sql server 2008 while loop insert sql server alter database name sql server ascii character sql server before delete trigger example sql server before insert trigger change value sql server before update trigger change value sql server catch deadlock exception sql server change database name sql server concurrency issues sql server crash course sql server create database sql server create database example sql server create table sql server create table with foreign key sql server create table with primary key sql server cte in union sql server cte inner join sql server database sql server deadlock analysis sql server deadlock code sql server deadlock detection sql server deadlock explained sql server deadlock explanation sql server deadlock graph xml how to read deadlock graph sql server deadlock log sql server deadlock log file sql server deadlock priority sql server deadlock priority example sql server deadlock retry logic sql server deadlock scenarios sql server deadlock script sql server deadlock update statement sql server deadlock victim sql server deadlock victim chosen sql server detect deadlock sql server dirty read nolock sql server except clause sql server except example sql server except order by sql server exec output example sql server find blocking processes sql server free training sql server from scratch sql server get id of last inserted row sql server index impact on insert sql server information_schema sql server inner join sql server inner join remove duplicates sql server inner join table valued function sql server intersect example sql server intersect vs inner join sql server join table function with parameter sql server kill connections sql server kill query sql server kill spid sql server kill transaction sql server left outer join null sql server level ddl trigger sql server list blocking processes sql server logon trigger audit sql server logon trigger example SQL Server Management Studio sql server missing index impact sql server missing index improvement measure sql server non-repeatable read sql server outer apply example sql server outer join null sql server phantom read example sql server profiler deadlock analysis sql server profiler deadlock graphs sql server random seed sql server read committed snapshot example sql server read uncommitted example sql server read uncommitted isolation level sql server repeat spaces sql server retry after deadlock sql server retry on deadlock sql server select deadlock victim sql server set identity_insert on sql server set trigger order sql server snapshot isolation level sql server table changes tracking sql server the text for object is encrypted sql server trigger instead of vs after sql server trigger update another table sql server trigger update multiple columns sql server unique constraint column sql server view update multiple tables sql sort union results sql sorting sql square a number sql stored procedure encryption sql stored procedure parameter example sql stored procedure with parameters sql string length sql string replace example sql subqueries sql subquery and join sql subquery group by sql Subquery IN SELECT sql subquery join example sql subquery practice sql subquery returned more than 1 value error sql subquery tutorial sql subquery vs join sql subquery with aggregate function sql subquery with group by sql subquery with join sql subquery with multiple tables sql substring email domain sql substring query sql trace flag 1222 sql training for beginners sql training for beginners youtube sql training online sql transaction commit rollback example sql trigger deleted table sql trigger example sql trigger example after insert sql trigger in dbms sql trigger insert update and delete sql trigger inserted sql trigger on insert sql trigger update column sql trigger update compare old new values sql trigger update example sql trigger update for each row sql trigger update old value sql triggers and stored procedures tutorial sql triggers for beginners sql turn identity_insert on sql udf case statement sql union all remove duplicates sql union not returning all rows sql union order of results sql union results of two queries sql unique constraint column sql unique constraint vs primary key sql unique key vs primary key sql update data using view sql update rollback commit sql update trigger sql update trigger inserted deleted sql update trigger loop sql update view multiple tables sql use temp table in view sql view aggregate functions sql view and order by sql view best practices sql view default constraints sql view default value sql view join multiple tables sql view join two tables sql view order by doesn't work sql view query sql view update data sql view update multiple tables sql view usage sql view vs select sql view vs table sql virtual table sql where clause multiple conditions sql where clause order sql where like and not like sql while loop example insert sql while loop insert into table sql wildcard operators sqlcode for deadlock sqlserver repeatable read ssms create database statement status return stored stored proc vs inline sql stored procedure stored procedure basics in sql server stored procedure create temp table example stored procedure input parameter stored procedure insert update delete sql server stored procedure or query which is faster stored procedure security sql server stored procedure temp table scope stored procedure vs sql statement stored procedure with parameters in sql stored procedure youtube stored procedures String string functions sub query subqueries subquery subquery and correlated query SUBSTRING sys.tables sysobjects System t sql basics t sql before update trigger example t sql coalesce multiple columns t sql create new database t-sql t-sql convert ascii code to char t-sql create database t-sql default constraint example t-sql fundamentals t-sql scalar function in where clause table table check constraint table identity reset table level check constraint sql server table valued function join example table variable and temp table difference table variable in sql table variable in sql server table variable vs temp table tables temp temp table best practices temp tables in stored procedures temporary test data the lost update problem example third normal form 3nf example third normal form example third normal form in database with example time top n rows trace deadlocks in sql server 2008 trace sql server deadlock transaction acid properties in dbms transaction acid properties with example transaction concurrency problem transaction isolation level transaction isolation level sql server transactions transactions in sql server trigger in sql developer trigger insert sql server trigger sql developer trigger to update view trigger update another table triggers try catch blocks try catch deadlock sql server try catch deadlock sql server 2008 sql server try catch deadlock tsql tsql generate random data Union union 2 tables sql Union All union vs intersect sql server union vs union all union vs union all sql server unique column vs primary key unique constraint null sql server unique index unique key constraint unrepeatable read problem updatable ctes Update update conflict snapshot isolation level update on cte update through cte update using cte update view based on multiple tables update view multiple base tables updateable views upper Use user valued values View limitations view or function is not updatable view vs table variable view vs temp table views violation of primary key constraint what happens if there is a deadlock where why stored procedure is better than inline query wild cards with encryption xtype Year