Grouping Sets in SQL Server 2008

SQL Server 2008 introduces a new feature called GROUPING SETS for SQL Server Database Developers. When a GROUP BY clause is used with the GROUPING SETS feature in SQL Server 2008 it will help you generate a result set which will be equivalent to that generated by a UNION ALL of multiple simple transact SQL Group By statements. A Grouping Sets statement will generate a result set which is equivalent to a result set generated by the use of Group By, Rollup or Cube operations. It is easier to write transact SQL statements using the Grouping Sets clause, as it avoids the overhead of writing many queries and then using UNION ALL to get the desired results. In this article you will be see how to use the new Grouping Sets feature introduced in SQL Server 2008.

Advantages of Using Grouping Sets Clause  

• The Grouping Sets feature is really helpful when you want to generate a set of aggregate results and at the same time you want to group by varying columns
• It is much easier to maintain and provides better performance when compared to running different queries against the same data and then finally performing a UNION ALL to get the desired results
• It provides better performance as it is executes once against the data source
• It is much easier to program and use Grouping Sets than writing multiple select statements

Example of Grouping Sets Clause in SQL Server 2008
You need to follow the below steps to know more about the Grouping Sets feature which was introduced in SQL Server 2008.

Create GroupingSetsDemo Database
The initial step for the database developer will be to create GroupingSetsDemo database by executing the below TSQL Query:

Use master
GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'GroupingSetsDemo')
DROP DATABASE [GroupingSetsDemo]
GO

CREATE DATABASE GroupingSetsDemo
GO

Create CricketTeams Table in GroupingSetsDemo Database
The next step will be to create a new table named CricketTeams within the GroupingSetsDemo database:

USE GroupingSetsDemo
GO

/* Drop CricketTeams Table if already exists */
IF OBJECT_ID (N'dbo.CricketTeams', N'U') IS NOT NULL
    DROP TABLE dbo.CricketTeams;
GO

/* Create CricketTeams Table */
CREATE TABLE dbo.CricketTeams
(   
    CricketTeamID TINYINT NOT NULL PRIMARY KEY,
    CricketTeamShortCode NVARCHAR(30), 
    CricketTeamCountry NVARCHAR(30),
    CricketTeamContinent NVARCHAR(50)
)
GO

Populate the CricketTeams Table using the below Insert Statement
The next step will be to populate the CricketTeams Table which you have created earlier by executing the below mentioned TSQL query:

/* Populate the CricketTeams Table  */

INSERT INTO CricketTeams (CricketTeamID,CricketTeamShortCode,CricketTeamCountry,CricketTeamContinent)
VALUES (1, 'AUS', 'Australia', 'Australia')
GO

INSERT INTO CricketTeams (CricketTeamID,CricketTeamShortCode,CricketTeamCountry,CricketTeamContinent)
VALUES (2, 'IND', 'India', 'Asia')
GO

INSERT INTO CricketTeams (CricketTeamID,CricketTeamShortCode,CricketTeamCountry,CricketTeamContinent)
VALUES (3, 'PAK', 'Pakistan', 'Asia')
GO

INSERT INTO CricketTeams (CricketTeamID,CricketTeamShortCode,CricketTeamCountry,CricketTeamContinent)
VALUES (4, 'SRL', 'Srilanka', 'Asia')
GO

INSERT INTO CricketTeams (CricketTeamID,CricketTeamShortCode,CricketTeamCountry,CricketTeamContinent)
VALUES (5, 'SAF', 'South Africa', 'Africa')
GO

INSERT INTO CricketTeams (CricketTeamID,CricketTeamShortCode,CricketTeamCountry,CricketTeamContinent)
VALUES (6, 'ZIM',  'Zimbabwe', 'Africa')
GO