Posts Tagged ‘T-SQL’

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

Parallel query processing in SQL Server

Posted: June 24, 2012 in MSSQL
Tags:
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


Execution plan for parallel query processing regarding the cost threshold parameter

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


Execution plan for serial query processing regarding the cost threshold parameter

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


Execution plan - cost 40% for parallel vs. 60% for 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


Setting of physical and logical CPU count simulation

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:
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

DBCC USEROPTIONS

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

SELECT TABLE [dbo].[IsoSelTopOnePlusOne]

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:

Other references:

Parsing T-SQL

Posted: December 15, 2010 in MSSQL
Tags:

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
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
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)
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:

System Function RAND() within UDF

Posted: September 15, 2010 in MSSQL
Tags:

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 1: Definition of the Table-valued function

Figure 2: Result of SQL query (of the Table-valued and Scalar-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 3: Definition of the Scalar-valued function

Figure 4: Definition of the stored procedure
Figure 4: Definition of the stored procedure

Figure 5: definition of the view
Figure 5: Definition of the view

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

Figure 7: Definition of SQL query using the 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 8: Result of SQL query using the view

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

Figure 10: Result 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:

SysObjects

Posted: August 15, 2010 in MSSQL
Tags:

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 1: Definition of the table

Figure 2: Result of SQL query
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 3: Definition of the stored procedure

Figure 4: Result of SQL query
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 6: Definition of SQL query

Figure 7: Result 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 8: Definition of the table

Figure 9: Definition of the stored procedure
Figure 9: Definition of the stored procedure

Figure 10: Definition of the user defined scalar function
Figure 10: Definition of the user defined scalar function

Figure 11: Result of the stored procedure
Figure 11: Result of the stored procedure