Grouping records by means of GROUPING SETS operator in GROUP BY clause

Task: to give a basic look at the grouping of rows in SQL Server

Whenever any aggregation function is used, the GROUP BY clause is required. SQL Server 2005 brought the GROUP BY operators like CUBE and ROLLUP, respectively. In addition, SQL Server 2008 brought the GROUP BY operator GROUPING SETS. The GROUPING SETS operator aggregates only specified groups instead of full sets of aggregations by means of CUBE or ROLLUP operators. As an alternative solution for grouping rows in terms of GROUPING SETS operator, one of SET operators was used before SQL Server 2008, the UNION concretely.

USE AdventureWorks2012
GO

SELECT  Name,
        CountryRegionCode,
        SalesLastYear
FROM    Sales.SalesTerritory
GO

Source code 1: Basic example skeleton

Figure 1: SSMS result – Basic example skeleton

USE AdventureWorks2012
GO

SET STATISTICS IO ON
GO

SELECT   Name,
         CountryRegionCode,
         SUM(SalesLastYear) AS TotalOfSalesLastYear
FROM     Sales.SalesTerritory
GROUP BY Name,
         CountryRegionCode

UNION ALL

SELECT   Name,
         NULL,
         SUM(SalesLastYear) AS TotalOfSalesLastYear
FROM     Sales.SalesTerritory
GROUP BY Name

UNION ALL

SELECT   NULL,
         CountryRegionCode,
         SUM(SalesLastYear) AS TotalOfSalesLastYear
FROM     Sales.SalesTerritory
GROUP BY CountryRegionCode

UNION ALL

SELECT   NULL,
         NULL,
         SUM(SalesLastYear) AS TotalOfSalesLastYear
FROM     Sales.SalesTerritory
GO

SET STATISTICS IO OFF
GO

Source code 2: Grouping by means of UNION operator before the GROUPING SETS operator in SQL Server 2008

Figure 2: SSMS result – Grouping by means of UNION operator before the GROUPING SETS operator in SQL Server 2008

USE AdventureWorks2012
GO

SET STATISTICS IO ON
GO

SELECT   Name,
         CountryRegionCode,
         SUM(SalesLastYear) AS TotalOfSalesLastYear
FROM     Sales.SalesTerritory
GROUP BY
         GROUPING SETS
         (
             (Name, CountryRegionCode),
             (Name),
             (CountryRegionCode),
             ()
         )
ORDER BY GROUPING(Name),
         GROUPING(CountryRegionCode)
GO

SET STATISTICS IO OFF
GO

Source code 3: Grouping by means of GROUPING SETS operator in SQL Server 2008

Figure 3: SSMS result – Grouping by means of GROUPING SETS operator in SQL Server 2008

USE AdventureWorks2012
GO

SET STATISTICS IO ON
GO

SELECT   Name,
         CountryRegionCode,
         SUM(SalesLastYear) AS TotalOfSalesLastYear
FROM     Sales.SalesTerritory
GROUP BY
         CUBE (Name, CountryRegionCode)
ORDER BY GROUPING(Name),
         GROUPING(CountryRegionCode)
GO

SET STATISTICS IO OFF
GO

Source code 4: Grouping by means of CUBE operator in SQL Server 2005

Figure 4: SSMS result – Grouping by means of CUBE operator in SQL Server 2005

The result of IO resources consumption using the UNION ALL operator
by returning 27 rows:
Table 'SalesTerritory'. Scan count 4, logical reads 8,
                        physical reads 0, read-ahead reads 0,
                        lob logical reads 0, lob physical reads 0,
                        lob read-ahead reads 0.

The result of IO resources consumption using the GROUPING SETS operator
by returning 27 rows:
Table 'SalesTerritory'. Scan count 2, logical reads 44,
                        physical reads 0, read-ahead reads 0,
                        lob logical reads 0, lob physical reads 0,
                        lob read-ahead reads 0.

The result of IO resources consumption using the CUBE operator
by returning 27 rows:
Table 'SalesTerritory'. Scan count 2, logical reads 44,
                        physical reads 0, read-ahead reads 0,
                        lob logical reads 0, lob physical reads 0,
                        lob read-ahead reads 0.

Source code 5: Results comparison of IO resources consumption across particular SQL Server operators

Additional references:

• Generating SubTotals using GROUPING
  by Sunil Chandurkar
• GROUP BY (Transact-SQL)
  by MSDN
• UNION (Transact-SQL)
  by MSDN
• Stairway to T-SQL DML Level 8: Using the ROLLUP, CUBE and GROUPING SET operator in a GROUP BY Clause
  by Gregory Larsen

How to build SSMS add-ins for SSMS 2005/EE, 2008-R2 and 2012

Task: to give an advanced look at the building of SSMS add-ins for SSMS 2005/EE, 2008-R2 and 2012 by means of C# language and Visual Studio as IDE

The creation of SSMS add-ins is the black art. Why? Because it is not or very weakly supported by Microsoft itself.
Microsoft has documented official position on SSMS add-ins and extensibility in the Denali BOL, which is also copied below:

SQL Server Management Studio is built upon the Visual Studio Isolated Shell, which inherently supports extensibility (add-ins/plug-ins). It is possible to tap into the Visual Studio extensibility services to surface custom capabilities within SQL Server Management Studio; however, such extensibility is not supported.

There are some users and third parties that have developed extensions to SQL Server Management Studio. While we do not discourage this, keep in mind that such extensibility is not supported, so there may be issues with backward/forward compatibility. Microsoft does not publish documentation for extending SQL Server Management Studio. There are, however, community blogs and sample code that you may be able to leverage.

Microsoft does not support SQL Server Management Studio installations with SQL Server Management Studio extensions present, so if you have installed SQL Server Management Studio extensions, you may want to remove them before calling Microsoft Customer Support about a SQL Server Management Studio issue.

Microsoft simply does not want to support backward compatibility of add-in API between different SSMS versions. There were already two breaking releases. It was an update from SSMS 2005 to SSMS 2008 and from SSMS 2008 to SSMS 2012, not to mention a small jump from SSMS 2008 to SSMS 2008R2. The current version of SSMS 2012 leads to a standard DTE environment of Visual Studio (source: Solutions Crew GmbH).
It seems that this step will bring to developers transparency to the development of SSMS add-ins.

On the web, there are some tutorials, but these tutorials are focused on a specific version of SSMS only and do not reflect the differences between particular SSMS versions. The below link gives you a complex source code that will help you in the building of SSMS add-ins across different SSMS versions.

Feel free to take the SOURCE CODE as a SSMS add-ins skeleton of {3S} SQL Smart Security SSMS Add-In from the CodePlex repository for the building of your SSMS add-ins.

Additional references:

• Building a SQL Server Management Studio Add-in
  by Jonathan Sayce
• Building a SSMS Add-in; The Agony and Ecstasy
  by Mark Pryce-Maher
• Extend Functionality in SQL Server 2005 Management Studio with Add-ins
  by Ameet Phadnis
• Extend Functionality in SQL Server 2005 Management Studio with Add-ins
  by Jasinski Technical WiKi
• How To : Create SQL Server Management Studio Addin
  by Shai Raiten
• The Black Art of Writing a SQL Server Management Studio 2005 Add-In
  by Joseph Cooney
• SQL 2008 R2 Breaks SSMS Addins
  by Jonathan Kehayias
• SQL Server Management Studio Add-In for Denali\SQL2012 (SSMS Addin)
  by TSQLTidy
• SSMS Addins
  by iucon