Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

Grouping Records with SQL Server 2008

, 18 Jul 2012
Rate this:
Please Sign up or sign in to vote.
Grouping Sets is the new SQL Server 2008 Operator

Introduccion

A new feature that has the new version of SQL Server 2008, is the new GROUPING SETS operator, which already existed in other database engines such as Oracle, which allows to combine different clustering queries in a single query.

It's good to clarify that the GROUPING SETS operator is an extension of the standard clause GROUP BY. When not required all possible groupings that are generated using ROLLUP or CUBE operator (which existed in SQL Server 2005), use GROUPING SETS to specify only the groups you want. So, thanks to GROUPING SETS obtain the desired levels of grouping and also it returns the subtotal for each subset of grouping. 

Grouping Sets 

In short we could say that GROUPING SET is more comprehensive and generic ROLLUP or CUBE. Generally queries that use this type of operators are related to data analysis, reporting and everything related to the world Intelegencias Business. 

I remind you that this new operator is not doing magic and solve a problem that could not solve, but rather it simplifies and optimizes our inquiries, that's what matters it, but I want to clarify that without using this operator we could generate or find same results in SQL Server, but not the performance that brings. 

Using the code 

Let's try a query, as always will use the test database AdventureWorks SQL Server for this example:  

select costytd, Name, CountryRegionCode from Sales.SalesTerritory  

The first thing that comes to mind to solve the grouping is almost always make a query using UNION ALL with three different queries, one for each group, would be something like this:

select SUM(costytd) AS Total, Name, CountryRegionCode from Sales.SalesTerritory
group by Name, CountryRegionCode

union all
select SUM(costytd), null, CountryRegionCode from Sales.SalesTerritory
group by Name, CountryRegionCode

union all
select SUM(costytd), null, null from Sales.SalesTerritory
group by Name, CountryRegionCode 

But we could fix it using the GROUPING SET operator, would be something like this: 

select SUM(costytd) AS Total, Name, CountryRegionCode from Sales.SalesTerritory
groyp by
grouping sets
(
 (name, CountryRegionCode),
 (CountryRegionCode),
()
)
order by grouping(name), grouping(CountryRegionCode) 

Now as you can see in this last example, in addition to using the GROUPING SETS operator, I use the GROUPING function in the ORDER BY, this is to sort the result by Name and CountryRegionCode. 

This function (which already existed in SQL Server 2005), indicates whether a column expression specified in a GROUP BY list is added or not. GROUPING returns 1 to 0 for no added and added, in the result set. 

Points of Interest 

The advantage of using GROUPING SETS is only given to simplify queries syntactically, but also on issues of performance. 

I did some tests to monitor the use of resources (SET STATISTICS IO ON) with 90,000 records in my table and these were the results: 

Using UNION ALL: Table 'Sales.SalesTerritory'. Scan count 3, logical reads 1728, physical reads 6, read-ahead reads 590, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 

Using GROUPING SETS: Table 'Sales.SalesTerritory'. Scan count 1, logical reads 576, physical reads 6, read-ahead reads 590, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 

As you can see, becomes less GROUPING SETS resource use I / O. This is because using the new feature of SQL Server, the engine needs less data pages read because it makes the calculation of higher-level aggregation of lower level aggregations. 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Omar Frometa
CEO FV Tech
Dominican Republic Dominican Republic
MCTS - SQL Server 2008
Microsoft Certified Technology Specialist
 
CETEX - Exactus ERP
Exactus Technician Certificate
Follow on   Twitter

Comments and Discussions

 
SuggestionImages Are Too Small PinprotectorAspDotNetDev16-Jul-12 14:16 
GeneralRe: Images Are Too Small PinmemberTim Corey18-Jul-12 4:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 18 Jul 2012
Article Copyright 2012 by Omar Frometa
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid