Archive for July, 2012

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


SSMS result – 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


SSMS result – 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


SSMS result – 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


SSMS result – 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:
Advertisements
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.


{3S} SQL Smart Security ''Protect your T-SQL know-how!''


Additional references: