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

Parallel query processing in SQL Server

Task: to give a basic look at the subsystem for parallel query processing in SQL Server

/* Testing laptop: ProBook 6540b, Intel Core i5 */

USE [AdventureWorks2012]
GO

SELECT [cpu_count]                     AS 'Logical CPU Count',
       [hyperthread_ratio]             AS 'Hyperthread Ratio',
       [cpu_count]/[hyperthread_ratio] AS 'Physical CPU Count'
FROM   [sys].[dm_os_sys_info]
GO
/* Result:
   Logical CPU Count | Hyperthread Ratio | Physical CPU Count
   4                   4	           1
*/

Source code 1: System configuration

SELECT [name], [value]
FROM   [sys].[configurations]
WHERE  [name] = 'cost threshold for parallelism'
       OR
       [name] = 'max degree of parallelism'
GO
/* Result:
   name                           | value
   cost threshold for parallelism   5 => default value, i.e. 5 seconds
   max degree of parallelism        0 => default value, i.e. parallelism
                                         with an automatic count of
                                         processors
*/


SELECT   *
FROM     [Sales].[SalesOrderDetail]        AS [sod]
         INNER JOIN [Production].[Product] AS [p]
         ON [sod].[ProductID] = [p].[ProductID]
ORDER BY [Style]
GO

Source code 2: Parallel query processing providing that the cost threshold parameter equals to 5

Figure 1: Execution plan for parallel query processing regarding the cost threshold parameter

EXEC sp_configure 'show advanced option', '1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure "max degree of parallelism", 0
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure "cost threshold for parallelism", 10
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced option', '0'
RECONFIGURE WITH OVERRIDE
GO

SELECT [name], [value]
FROM   [sys].[configurations]
WHERE  [name] = 'cost threshold for parallelism'
       OR
       [name] = 'max degree of parallelism'
GO
/* Result:
   name                           | value
   cost threshold for parallelism   10 => custom value i.e., 10 seconds
   max degree of parallelism        0  => default value i.e., parallelism
                                          with an automatic count of
                                          processors
*/


SELECT   *
FROM     [Sales].[SalesOrderDetail]        AS [sod]
         INNER JOIN [Production].[Product] AS [p]
         ON [sod].[ProductID] = [p].[ProductID]
ORDER BY [Style]
GO


EXEC sp_configure 'show advanced option', '1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure "max degree of parallelism", 0
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure "cost threshold for parallelism", 5
RECONFIGURE WITH OVERRIDE
GO

SELECT [name], [value]
FROM   [sys].[configurations]
WHERE  [name] = 'cost threshold for parallelism'
       OR
       [name] = 'max degree of parallelism'
GO
/* Result:
   name                           | value
   cost threshold for parallelism   5 => default value i.e., 5 seconds
   max degree of parallelism        0 => default value i.e., parallelism
                                         with an automatic count of
                                         processors
*/

Source code 3: Serial query processing providing that the cost threshold parameter equals to 10

Figure 2: Execution plan for serial query processing regarding the cost threshold parameter

SELECT   *
FROM     [Sales].[SalesOrderDetail]
ORDER BY [ProductID]

SELECT   *
FROM     [Sales].[SalesOrderDetail]
ORDER BY [ProductID]
OPTION   (MAXDOP 1) -- Note: query hint for one processor
GO

Source code 4: Execution plan – costs comparison for parallel and serial with OPTION (MAXDOP 1) query processing

Figure 3: Execution plan - cost 40% for parallel vs. 60% for serial with OPTION (MAXDOP 1) query processing

Figure 4: Setting of physical and logical CPU count simulation

SELECT [cpu_count]                     AS 'Logical CPU Count',
       [hyperthread_ratio]             AS 'Hyperthread Ratio',
       [cpu_count]/[hyperthread_ratio] AS 'Physical CPU Count'
FROM   [sys].[dm_os_sys_info]
GO
/* Result:
   Logical CPU Count | Hyperthread Ratio | Physical CPU Count
   16                  4	           4
*/

Source code 5: Simulating 4 physical and 16 logical CPU count

Note: Express Edition of SQL Server does not support simulating physical CPU count

General recommendations:

• OLTP: on Pure OLTP system, where the transactions are smaller and queries are not long but very quick usually, set the “Maximum Degree of Parallelism” to 1 (one).
• Data-warehousing / Reporting server: as queries will be running for long time, it is advised to set the “Maximum Degree of Parallelism” to 0 (zero).
• Mixed System (OLTP & OLAP): the right balance has to be found. To set the “Maximum Degree of Parallelism” to 2, which means the query still uses parallelism but only on 2 CPUs. However, to keep the “Cost Threshold for Parallelism” very high.

Additional references:

• Execute T-SQL in Parallel
  by Kevin Kline
• Execute T-SQL Scripts in Parallel
  by James Ma
• Flash Tip: Simulating Parallel Plans
  by Microsoft TechNet
• How SQL Server 2005 Express Edition determines the CPU count and uses the CPUs during processing
  by Microsoft Support
• Parallel Data Warehouse
  by Microsoft SQL Server
• Parallel execution in SSIS
  by James Serra
• Parallel Query Processing
  by Microsoft MSDN
• SQL SERVER – CXPACKET – Parallelism – Usual Solution – Wait Type – Day 6 of 28
  by Pinal Dave
• SQL SERVER – Partition Parallelism Support in expressor 3.6
  by Pinal Dave
• SQL SERVER – Using MAXDOP 1 for Single Processor Query – SQL in Sixty Seconds #008 – Video
  by Pinal Dave
• SQL Server 2012’s Information on Parallel Thread Usage
  by Joe Sack
• SQLAuthority News – Download Whitepaper – Understanding and Controlling Parallel Query Processing in SQL Server
  by Pinal Dave

Concurrent Behaviour at Transaction Isolation Levels

Task: to determine the behaviour of particular transaction isolation levels with respect to concurrent SQL query …SELECT TOP(1) […] + 1…
Solution: see Figure 2

USE [Test_IsolationLevels]
GO

Source code 1: Use database

CREATE TABLE [dbo].[IsoSelTopOnePlusOne](
 [Identity] [int] IDENTITY(1,1) NOT NULL,
 [TopOnePlusOne] [int] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

Source code 2: Create table

-- 0: (NOTE: default isolation level)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
---- 1:
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
---- 2:
--SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
---- 3:
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
---- 4:
--ALTER DATABASE [Test_IsolationLevels] SET ALLOW_SNAPSHOT_ISOLATION ON
--SET TRANSACTION ISOLATION LEVEL SNAPSHOT
---- 5: (NOTE: set up failed)
--ALTER DATABASE [Test_IsolationLevels] SET READ_COMMITTED_SNAPSHOT ON
GO

DBCC USEROPTIONS
GO

Source code 3: Set up and select user options

Figure 1: SSMS result of DBCC USEROPTIONS

CREATE PROCEDURE [dbo].[IsoSelTopOnePlusOne_Ins]
AS
BEGIN
 -- 0: (NOTE: default isolation level)
 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 ---- 1:
 --SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 ---- 2:
 --SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
 ---- 3:
 --SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
 ---- 4:
 --SET TRANSACTION ISOLATION LEVEL SNAPSHOT

 BEGIN TRANSACTION IsoSelTop1PlusOne 

  DECLARE @Top1PlusOne INT
  SET @Top1PlusOne =
  (
   SELECT TOP(1) [TopOnePlusOne] + 1
   FROM [dbo].[IsoSelTopOnePlusOne]
   ORDER BY [TopOnePlusOne] DESC
  )
  IF
  (
   @Top1PlusOne
  ) IS NULL
  BEGIN
   SET @Top1PlusOne = 0
  END

  INSERT INTO [dbo].[IsoSelTopOnePlusOne]
  (
   [TopOnePlusOne],
   [ModifiedDate]
  )
  VALUES
  (
   @Top1PlusOne,
   GETDATE()
  )

  WAITFOR DELAY '00:00:10'

 COMMIT TRANSACTION IsoSelTop1PlusOne
END
GO

Source code 4: Create procedure

EXEC [dbo].[IsoSelTopOnePlusOne_Ins]
GO

Source code 5: Execute procedure

Figure 2: SSMS result of concurrent behaviour at the transaction isolation levels

Measuring results:

[Identity]	Isolation Level	[TopOnePlusOne]	match/mismatch
1-2	ISOLATION LEVEL READ COMMITTED	0, 1	mismatch
3-4	ISOLATION LEVEL READ UNCOMMITTED	2, 3	mismatch
5-6	ISOLATION LEVEL REPEATABLE READ	4, 5	mismatch
7-8	ISOLATION LEVEL SERIALIZABLE	6, 7	mismatch
9-10	ISOLATION LEVEL SNAPSHOT	8, 8	match

The measurement above included 2 concurrent SQL queries (two SSMS windows). The second query started up approximately in half of the first SQL query.

Additional references:

• DBCC USEROPTIONS
  by MSDN
• Isolation (database systems)
  by Wikipedia
• Isolation Levels in the Database Engine
  by MSDN
• SET TRANSACTION ISOLATION LEVEL
  by MSDN
• SQL SERVER – Applying NOLOCK Hint at Query Level – NOLOCK for whole Transaction
  by Pinal Dave
• SQL SERVER – Concurrency Basics – Guest Post by Vinod Kumar
  by Vinod Kumar
• SQL SERVER – Concurrancy Problems and their Relationship with Isolation Level
  by Pinal Dave
• SQL SERVER – Simple Example of Snapshot Isolation – Reduce the Blocking Transactions
  by Pinal Dave

Other references:

• SQL Server: Locking basics
  by Vinod Kumar
• SQL SERVER – Locking and Blocking – Important Aspect of Database and Effect on Performance – Quiz – Puzzle – 5 of 31
  by Pinal Dave
• SQLServer Quiz 2012 – ANSI ISOLATIONS levels are very important part of any database
  by Beyond Relational

Parsing T-SQL

Task: syntax parsing and checking db object existence in SQL query
Solution: see Source code No. 4


Source code No. 1: Syntax parsing of SQL

DECLARE @SqlQuery NVARCHAR(4000)
SET @SqlQuery = 'SELECT [ID] FRON [dbo].[Parsing_T-SQL]'
EXEC ('SET FMTONLY ON ' + @SqlQuery)

Figure 1: SSMS SQL query result – syntax parsing of SQL

Source code No. 2: Table existence check

DECLARE @SqlQuery NVARCHAR(4000)
SET @SqlQuery = 'SELECT [ID] FROM [dbo].[Parsing_T-SQL]'
EXEC ('SET FMTONLY ON ' + @SqlQuery)

Figure 2: SSMS SQL query result – table existence check

Notes:

• syntax parsing and checking db object existence in SQL query is valid equally for all commands of DML set (it means: SELECT/INSERT/UPDATE/DELETE)
• functionally identical for EXEC and EXEC sp_executesql
• option SET NOEXEC ON and SET PARSEONLY ON executed syntax parsing only but not db object existence check (db object existence check may be carried out by means of SYSOBJECTS, SYSCOLUMNS etc.)
• the structure of BEGIN TRAN-ROLLBACK TRAN is not suitable because not all operations may be rollbacked (e.g. DROP DATABASE)

Source code No. 3: Syntax parsing and checking the existence in SQL query with user’s parameter (i.e. input)

DECLARE @SqlQuery NVARCHAR(4000)
SET @SqlQuery = 'UPDATE [dbo].[Parsing_T-SQL] SET [Value] = ''Test'' WHERE [ID] = @ID'
EXEC ('SET FMTONLY ON ' + @SqlQuery)

Figure 3: SSMS SQL query result – syntax parsing and checking the existence in SQL query with user’s parameter (input)

Note: PARSE is OK but for EXECUTE see Figure 3

Solution: replacement @ID = 1 (see Source code No. 4)

Source code No. 4: Syntax parsing and checking the existence in SQL query with user’s parameter (i.e. input) and replacement @ID = 1

DECLARE @SqlQuery NVARCHAR(4000)
SET @SqlQuery = 'UPDATE [dbo].[Parsing_T-SQL] SET [Value] = ''Test'' WHERE [ID] = 1'
EXEC ('SET FMTONLY ON ' + @SqlQuery)

Additional reference:

• SqlWrite
  by Luke Breuer

System Function RAND() within UDF

Task: the system function RAND() use in the user defined function (UDF)
Solution: the system function RAND() is not possible to be used in the user defined function (see Figure 1 to 3). However, this function is possible to be implemented without problems into the stored procedure (see Figure 4 and 6), or to be used by means of a database object “view” ( see Figure 5 to 8 )

Figure 1: Definition of the Table-valued function

Figure 2: Result of SQL query (of the Table-valued and Scalar-valued function)

Figure 3: Definition of the Scalar-valued function

Figure 4: Definition of the stored procedure

Figure 5: definition of the view

Figure 6: Result of SQL query (of the stored procedure and view)

Figure 7: Definition of SQL query using the view

Note: command GO 5 provides the execution of programming batch 5 times

Figure 8: Result of SQL query using the view

Figure 9: Definition of SQL query with the pre-defined initialisation value

Figure 10: Result of SQL query with the pre-defined initialisation value

Note: when entering the initialisation value, an identical result of random value will always be generated

Additional reference:

• Generating Random Numbers in SQL Server Without Collisions
  by Aaron Bertrand

SysObjects

sys.columns

Task: listing of the name and nullability of individual columns from the given table
Solution:

SELECT [name], [is_nullable]
FROM [sys].[columns]
WHERE [object_id] = OBJECT_ID('[dbo].[Test_SysColumns]')

Figure 1: Definition of the table

Figure 2: Result of SQL query



sys.parameters

Task: listing of the I/O parameters from the given stored procedure
Solution:

SELECT [name], [is_output]
FROM [sys].[parameters]
WHERE [object_id] = OBJECT_ID('[dbo].[Test_SelInputParameters]')

Figure 3: Definition of the stored procedure

Figure 4: Result of SQL query



INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE & INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Task: listing of the name for a primary key (or primary keys) from the given table
Solution: see Figure 6

Figure 5: Definition of the table (see Figure 1)

Figure 6: Definition of SQL query

Figure 7: Result of SQL query



ColumnsDefinition (INFORMATION_SCHEMA.COLUMNS)

Task: listing of the columns name, pre-defined value (suitably adjusted), nullability, data type and detailed information for data type from the given scheme and table. The objective is to provide this information for, e.g. validation logic at the application level of client application
Solution: see Figure 9

Figure 8: Definition of the table

Figure 9: Definition of the stored procedure

Figure 10: Definition of the user defined scalar function

Figure 11: Result of the stored procedure