Microsoft SQL Server 2008 introduces several enhancements & changes to how we group and aggregate relational result sets in TSQL. What follows is a simplistic listing and overview of the grouping/aggregate changes we are seeing in Microsoft SQL Server 2008 CTP5:
1. New GROUPING SETS Operator
2. ROLLUP & CUBE Operator Syntax Changes
3. New GROUPING_ID() Function
GROUP BY Operator Addition: GROUPING SETS
The first and most noted change in how we can perform relational grouping is the GROUPING SETS operator addition to the GROUP BY clause. This new operator allows you to perform several grouping set in one query. The older equivalent of this functionality is to perform several GROUP BY queries and then perform a UNION ALL between them. The GROUPING SETS operator supports concatenating column groupings and an optional grand total row. GROUPING SETS can be used in conjunction with the ROLLUP & CUBE operators (which we will use the new syntax described below for ROLLUP|CUBE). Below is some sample TSQL code to demonstrate this functionality:
*All sample code requires the AdventureWorks & AdventureWorks DW sample databases.
USE AdventureWorksDW;
GO
--------OLD METHOD, UNION ALL
SELECT NULL AS [ProductKey], OrderDateKey, DueDateKey, ShipDateKey
,SUM (SalesAmount) AS [Sales]
FROM dbo.FactInternetSales
GROUP BY OrderDateKey, DueDateKey, ShipDateKey
UNION ALL
SELECT ProductKey, NULL AS [OrderDateKey], DueDateKey, ShipDateKey
,SUM (SalesAmount) AS [Sales]
FROM dbo.FactInternetSales
GROUP BY ProductKey, DueDateKey, ShipDateKey
UNION ALL
SELECT ProductKey, OrderDateKey, NULL AS [DueDateKey], ShipDateKey
,SUM (SalesAmount) AS [Sales]
FROM dbo.FactInternetSales
GROUP BY ProductKey, OrderDateKey, ShipDateKey
UNION ALL
SELECT ProductKey, OrderDateKey, DueDateKey, NULL AS [ShipDateKey]
,SUM (SalesAmount) AS [Sales]
FROM dbo.FactInternetSales
GROUP BY ProductKey, OrderDateKey, DueDateKey
GO
--------NEW METHOD, GROUPING SETS
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
,SUM (SalesAmount) AS [Sales]
FROM dbo.FactInternetSales
GROUP BY GROUPING SETS(ProductKey, OrderDateKey, DueDateKey, ShipDateKey);
GO
--------NEW METHOD, GROUPING SETS w/Grand Total Row (record #1125 is the grand total row)
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
,SUM (SalesAmount) AS [Sales]
FROM dbo.FactInternetSales
GROUP BY GROUPING SETS(ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ());
GO
--------NEW METHOD, GROUPING SETS w/Composite Columns
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
,SUM (SalesAmount) AS [Sales]
FROM dbo.FactInternetSales
GROUP BY GROUPING SETS((ProductKey, OrderDateKey), DueDateKey, ShipDateKey);
GO
--------NEW METHOD, GROUPING SETS w/ROLLUP Composite Columns
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
,SUM (SalesAmount) AS [Sales]
FROM dbo.FactInternetSales
GROUP BY GROUPING SETS(ROLLUP(ProductKey, OrderDateKey), DueDateKey, ShipDateKey);
GO
--------NEW METHOD, GROUPING SETS w/CUBE Composite Columns
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
,SUM (SalesAmount) AS [Sales]
FROM dbo.FactInternetSales
GROUP BY GROUPING SETS(CUBE(ProductKey, OrderDateKey), DueDateKey, ShipDateKey);
GO
GROUP BY Operator Syntax Changes: ROLLUP & CUBE
Next up are the changes in the ROLLUP|CUBE operators' syntax. These operators produce aggregate groupings and are appended to the GROUP BY clause. Prior to SQL Server 2008 you would specify WITH ROLLUP|WITH CUBE. In SQL Server 2008, you will first designate ROLLUP|CUBE and then pass the grouped columns into these operators. Below is some sample TSQL code to demonstrate this functionality:
USE AdventureWorksDW;
GO
--------OLD METHOD, WITH ROLLUP
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
,SUM (SalesAmount) AS [Sales]
FROM dbo.FactInternetSales
GROUP BY ProductKey, OrderDateKey, DueDateKey, ShipDateKey WITH ROLLUP;
GO
--------NEW METHOD, ROLLUP()
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
,SUM (SalesAmount) AS [Sales]
FROM dbo.FactInternetSales
GROUP BY ROLLUP(ProductKey, OrderDateKey, DueDateKey, ShipDateKey);
GO
--------OLD METHOD, WITH CUBE
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
,SUM (SalesAmount) AS [Sales]
FROM dbo.FactInternetSales
GROUP BY ProductKey, OrderDateKey, DueDateKey, ShipDateKey WITH CUBE;
GO
--------NEW METHOD, CUBE()
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
,SUM (SalesAmount) AS [Sales]
FROM dbo.FactInternetSales
GROUP BY CUBE(ProductKey, OrderDateKey, DueDateKey, ShipDateKey);
GO
New Function: GROUPING_ID()
Last but not least, there is a new function, GROUPING_ID which returns the level of grouping for a particular column. This function is an enhancement from the older GROUPING() function. GROUPING() returns a 0 or a 1 to indicate if the column is aggregated or not. Below is some sample TSQL code to demonstrate this functionality:
USE AdventureWorks;
GO
SELECT
D.Name
,GROUPING_ID(D.Name, E.Title) AS [Name_Title_GroupID]
,GROUPING(D.Name) AS [Name_GroupAggregated?]
,GROUPING(E.Title) AS [Title_GroupAggregated?]
,COUNT(E.EmployeeID) AS N'Employee Count'
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeDepartmentHistory DH
ON E.EmployeeID = DH.EmployeeID
INNER JOIN HumanResources.Department D
ON D.DepartmentID = DH.DepartmentID
WHERE DH.EndDate IS NULL
AND D.DepartmentID IN (12,14)
GROUP BY ROLLUP(D.Name, E.Title);
