A simple hack of a system stored procedure

Task: to show how simply to hack a system stored procedure in SQL Server 2005 and higher by means of DAC (Dedicated Administrator Connection) and resource database (the read-only mssqlsystemresource database)


WARNING: by hacking any part of SQL Server, e.g. a system stored procedure, you will lose the warranty from Microsoft. This blog post has the educational purpose only. For testing purposes, you should try it on the development machine only. I do not take any responsibility for any damages caused by this article.


Recommendations:

• make a backup of the mssqlsystemresource database
• if necessary, add “Full control” permissions for the user of “NT Service\MSSLQSERVER” (the account of SQL Server (MSSQLSERVER) service) on “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.ldf and mssqlsystemresource.mdf”
• run the cmd.exe as an administrator

The process of altering, i.e. hacking, e.g. of the system stored procedure sys.sp_who2 consists of the following steps:
1. Windows > Run => RUN A NEW CMD WINDOW

net stop mssqlserver
net start mssqlserver
cd C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
sqlcmd -S .\
sys.sp_who2
go
exit
exit @echo CLOSE THE CMD WINDOW

2. Windows > Run => RUN A NEW CMD WINDOW

net stop mssqlserver
cd C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
sqlservr -s mssqlserver -m
@echo DO NOT CLOSE THE CMD WINDOW

3. Windows > Run => RUN A NEW CMD WINDOW

cd C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
sqlcmd -S .\ -A
use mssqlsystemresource
go
alter database mssqlsystemresource set read_write
go
sp_helptext 'sys.sp_who2'
go
alter procedure sys.sp_who2 as select 'hacked procedure'
go
sp_helptext 'sys.sp_who2'
go
alter database mssqlsystemresource set read_only
go
exit
exit @echo CLOSE ALL RUNNING CMD WINDOWS

4. Windows > Run => RUN A NEW CMD WINDOW

net start mssqlserver
cd C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
sqlcmd -S .\
sys.sp_who2
go
exit
exit @echo CLOSE THE CMD WINDOW

Abbreviations:

• -A > dedicated admin connection
• -S > server
• -m > single user admin mode
• -s > name (alternate registry key name)
• .\ > localhost and default instance

Figure 1: SQLCMD result – the procedure sys.sp_who2 before hacking

Figure 2: SQLCMD result – the procedure sys.sp_who2 after hacking

Additional references:

• DAC (Dedicated Administrator Connection) in SQL Server 2008/2005 Express Edition
  by Marian Placko
• Resource Database
  by MSDN

Change Tracking – incremental data loading into DWH

Task: to create a SSIS package to incremental data loading by means of Change Tracking

The process of creating a SSIS solution consists of the following steps:
1. creating of a SSDT (SQL Server Data Tools) SSIS project
2. enabling of the ‘CHANGE TRACKING’ feature on a database and table level
3. creating of the ‘MySettings’ table and inserting a default value into the ‘LastVersion’ column
4. creating of the ‘Version_Get’ stored procedure
5. getting and setting of the ‘@LastVersion’ and ‘@CurrentVersion’ variables
6. creating of the FAKE DWH ‘BusinessEntity_DWH’ table
7. FAKE inserting into the ‘BusinessEntity’ table
8. incremental data loading into the fake DWH ‘BusinessEntity_DWH’ table (the core of ‘CHANGE TRACKING’)
9. saving of the ‘@CurrentVersion’ variable into the ‘LastVersion’ column
10. disabling of the ‘CHANGE TRACKING’ feature on a table and database level; deleting of created objects

Figure 1: Change Tracking – Control Flow

Figure 2: Change Tracking – Data Flow

Source code: SSIS Package (SQL Server 2012 – SSDT)
Note: do not forget to customize the ConnectionString in the Project.params setting

Additional references:

• Change data capture
  by Wikipedia
• Change Data Capture
  by MSDN
• Change Tracking
  by MSDN
• Configuring Change Tracking
  by MCTS EXAM 70-433
• Features Supported by the Editions of SQL Server 2012
  by MSDN
• Incremental Data Loading Using CDC
  by Mark Murphy
• Introduction to Change Data Capture (CDC) in SQL Server 2008
  by Pinal Dave
• Tuning the Performance of Change Data Capture in SQL Server 2008
  by TechNet

Data cleaning – selection of data from two data sources that contain duplicate data

Task: to create a SSIS package to delete duplicates by means of Fuzzy logic

The process of creating a SSIS solution consists of the following steps:
1. creating of a SSDT (SQL Server Data Tools) SSIS project
2. defining of connection to data sources
3. defining of connection to saving duplicate records
4. defining of connection to saving error messages
5. defining of connection to a database
6. creating of a table in a target database
7. creating of a Data Flow diagram
8. removing of exact duplicates
9. removing of “similar” duplicates by means of Fuzzy logic
10. saving of retrieved duplicates

Figure 1: Deleting duplicates by means of Fuzzy logic

Source code: SSIS Package (SQL Server 2012 – SSDT)
Note: do not forget to customize the Connection Managers in solution setting

Additional references:

• Features Supported by the Editions of SQL Server 2012
  by MSDN
• SQL SERVER – Why Do We Need Data Quality Services – Importance and Significance of Data Quality Services (DQS)
  by Pinal Dave

Query Optimizer – Table Scan, Index Scan/Seek and Query Hint OPTION (OPTIMIZE FOR ())

Task: to give a deeper look at the work of the query optimizer with the query hint OPTION (OPTIMIZE FOR ()) in SQL Server

Indices represent one of the best performance techniques. The operator seek is being used on large tables (the overhead by using B-Tree on small tables leads to poor performance). The operator scan is being used on small tables. An improper using of indices can have a negative impact on the performance.
The query optimizer usually selects the optimal execution plan with regard to statistical data. For this reason, the using of query hints is recommended only for advanced database developers or DBAs.

/* STEP 1 – BEGIN */
/* PREPARE – WITHOUT INDICES (BEGIN) */
USE [Test]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
        OBJECT_ID(N'[dbo].[QueryOptimizer]') AND type in (N'U'))
BEGIN
	DROP TABLE [dbo].[QueryOptimizer]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[QueryOptimizer](
	[TerritoryID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[CountryRegionCode] [varchar](3) NOT NULL
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

SET NOCOUNT ON
GO
/* PREPARE – WITHOUT INDICES (END) */


/* CLEAN (BEGIN) */
TRUNCATE TABLE dbo.QueryOptimizer
GO
/* CLEAN (END) */
/* STEP 1 – END */


/* TEST 1: WITHOUT INDICES – SMALL TABLE: SELECTIVITY (BEGIN) */
/* STEP 2 – BEGIN */
-- Execution Plan: OFF

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Northwest', 'US')
	SET @rows += 1
END
GO

DECLARE @rows INT = 1
WHILE @rows < 2 -- 1 row
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Canada', 'CA')
	SET @rows += 1
END
GO 10 -- 10 * 1 rows
/* STEP 2 – END */

/* STEP 3 – BEGIN */
-- Execution Plan: ON
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO

SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'US'
-- US: NONSELECTIVE VALUE, TABLE SCAN, 1000 of 1010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 4,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/

SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'CA'
-- CA: SELECTIVE VALUE, TABLE SCAN, 10 of 1010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 4,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/

SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO
/* STEP 3 – END */
/* TEST 1: WITHOUT INDICES – SMALL TABLE: SELECTIVITY (END) */


/* STEP 4 – BEGIN */
/* CLEAN (BEGIN) */
TRUNCATE TABLE dbo.QueryOptimizer
GO
/* CLEAN (END) */
/* STEP 4 – END */


/* TEST 2: WITHOUT INDICES – LARGE TABLE: SELECTIVITY (BEGIN) */
/* STEP 5 – BEGIN */
-- Execution Plan: OFF

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Northwest', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Northeast', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Central', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Southwest', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Southeast', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Southeast', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 2 -- 1 row
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Canada', 'CA')
	SET @rows += 1
END
GO 10 -- 10 * 1 rows
/* STEP 5 – END */

/* STEP 6 – BEGIN */
-- Execution Plan: ON
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO

SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'US'
-- US: NONSELECTIVE VALUE, TABLE SCAN, 60000 of 60010 rows
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 231,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
GO

SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'CA'
-- CA: SELECTIVE VALUE, TABLE SCAN, 10 of 60010 rows
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 231,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
GO

SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO
/* STEP 6 – END */
/* TEST 2: WITHOUT INDICES – LARGE TABLE: SELECTIVITY (END) */

Source code 1: Tests without using indices

Figure 1: SSMS result – TEST 1: WITHOUT INDICES – SMALL TABLE: SELECTIVITY (US: LOW / CA: HIGH)

Figure 1: SSMS result – TEST 2: WITHOUT INDICES – LARGE TABLE: SELECTIVITY (US: LOW / CA: HIGH)

/* STEP 1 – BEGIN */
/* PREPARE – WITH INDICES (BEGIN) */
USE [Test]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
        OBJECT_ID(N'[dbo].[QueryOptimizer]') AND type in (N'U'))
BEGIN
	DROP TABLE [dbo].[QueryOptimizer]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[QueryOptimizer](
	[TerritoryID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[CountryRegionCode] [varchar](3) NOT NULL,
 CONSTRAINT [PK_QueryOptimizer] PRIMARY KEY CLUSTERED 
(
	[TerritoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_QueryOptimizer_CountryRegionCode]
        ON [dbo].[QueryOptimizer]
(
	[CountryRegionCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

SET NOCOUNT ON
GO
/* PREPARE – WITH INDICES (END) */


/* CLEAN (BEGIN) */
TRUNCATE TABLE dbo.QueryOptimizer
GO
/* CLEAN (END) */
/* STEP 1 – END */


/* TEST 1: WITH INDICES – SMALL TABLE: SELECTIVITY (BEGIN) */
/* STEP 2 – BEGIN */
-- Execution Plan: OFF

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Northwest', 'US')
	SET @rows += 1
END
GO

DECLARE @rows INT = 1
WHILE @rows < 2 -- 1 row
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Canada', 'CA')
	SET @rows += 1
END
GO 10 -- 10 * 1 rows
/* STEP 2 – END */

/* STEP 3 – BEGIN */
-- Execution Plan: ON
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO

SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'US'
-- US: NONSELECTIVE VALUE, INDEX SCAN (Clustered), 1000 of 1010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 6,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/

SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'CA'
-- CA: SELECTIVE VALUE, INDEX SCAN (Clustered), 10 of 1010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 6,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/

SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO
/* STEP 3 – END */
/* TEST 1: WITH INDICES – SMALL TABLE: SELECTIVITY (END) */


/* STEP 4 – BEGIN */
/* CLEAN (BEGIN) */
TRUNCATE TABLE dbo.QueryOptimizer
GO
/* CLEAN (END) */
/* STEP 4 – END */


/* TEST 2: WITH INDICES – LARGE TABLE: SELECTIVITY (BEGIN) */
/* STEP 5 – BEGIN */
-- Execution Plan: OFF

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Northwest', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Northeast', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Central', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Southwest', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Southeast', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Southeast', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 2 -- 1 row
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Canada', 'CA')
	SET @rows += 1
END
GO 10 -- 10 * 1 rows
/* STEP 5 – END */

/* STEP 6 – BEGIN */
-- Execution Plan: ON
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO

SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'US'
-- US: NONSELECTIVE VALUE, INDEX SCAN (Clustered), 60000 of 60010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 223,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/

SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'CA'
-- CA: SELECTIVE VALUE, INDEX SEEK (NonClustered), 10 of 60010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 22,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/

SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO
/* STEP 6 – END */
/* TEST 2: WITH INDICES – LARGE TABLE: SELECTIVITY (END) */

Source code 2: Tests with using indices

Figure 2: SSMS result – TEST 1: WITH INDICES – SMALL TABLE: SELECTIVITY (US: LOW / CA: HIGH)

Figure 2: SSMS result – TEST 2: WITH INDICES – LARGE TABLE: SELECTIVITY (US: LOW / CA: HIGH)

/* STEP 1 – BEGIN */
/* PREPARE – WITH PRIMARY KEY INDEX ONLY (BEGIN) */
USE [Test]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
        OBJECT_ID(N'[dbo].[QueryOptimizer]') AND type in (N'U'))
BEGIN
	DROP TABLE [dbo].[QueryOptimizer]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[QueryOptimizer](
	[TerritoryID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[CountryRegionCode] [varchar](3) NOT NULL,
 CONSTRAINT [PK_QueryOptimizer] PRIMARY KEY CLUSTERED 
(
	[TerritoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

SET NOCOUNT ON
GO
/* PREPARE - WITH PRIMARY KEY INDEX ONLY (END) */


/* CLEAN (BEGIN) */
TRUNCATE TABLE dbo.QueryOptimizer
GO
/* CLEAN (END) */
/* STEP 1 – END */


/* TEST 1: WITH PRIMARY KEY INDEX ONLY – SMALL TABLE: SELECTIVITY (BEGIN) */
/* STEP 2 – BEGIN */
-- Execution Plan: OFF

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Northwest', 'US')
	SET @rows += 1
END
GO

DECLARE @rows INT = 1
WHILE @rows < 2 -- 1 row
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Canada', 'CA')
	SET @rows += 1
END
GO 10 -- 10 * 1 rows
/* STEP 2 – END */

/* STEP 3 – BEGIN */
-- Execution Plan: ON
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO

SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'US'
-- US: NONSELECTIVE VALUE, INDEX SCAN (Clustered), 1000 of 1010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 6,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/

SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'CA'
-- CA: SELECTIVE VALUE, INDEX SCAN (Clustered), 10 of 1010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 6,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/

SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO
/* STEP 3 – END */
/* TEST 1: WITH PRIMARY KEY INDEX ONLY – SMALL TABLE: SELECTIVITY (END) */


/* STEP 4 – BEGIN */
/* CLEAN (BEGIN) */
TRUNCATE TABLE dbo.QueryOptimizer
GO
/* CLEAN (END) */
/* STEP 4 – END */


/* TEST 2: WITH PRIMARY KEY INDEX ONLY – LARGE TABLE: SELECTIVITY (BEGIN) */
/* STEP 5 – BEGIN */
-- Execution Plan: OFF

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Northwest', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Northeast', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Central', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Southwest', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Southeast', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Southeast', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 2 -- 1 row
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Canada', 'CA')
	SET @rows += 1
END
GO 10 -- 10 * 1 rows
/* STEP 5 – END */

/* STEP 6 – BEGIN */
-- Execution Plan: ON
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO

SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'US'
-- US: NONSELECTIVE VALUE, INDEX SCAN (Clustered), 60000 of 60010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 223,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/

SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'CA'
-- CA: SELECTIVE VALUE, INDEX SCAN (Clustered), 10 of 60010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 223,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/

SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO
/* STEP 6 – END */
/* TEST 2: WITH PRIMARY KEY INDEX ONLY – LARGE TABLE: SELECTIVITY (END) */

Source code 3: Tests with primary key index only

Figure 3: SSMS result – TEST 1: WITH PRIMARY KEY INDEX ONLY – SMALL TABLE: SELECTIVITY (US: LOW / CA: HIGH)

Figure 3: SSMS result – TEST 2: WITH PRIMARY KEY INDEX ONLY – LARGE TABLE: SELECTIVITY (US: LOW / CA: HIGH)

/* STEP 1 – BEGIN */
/* PREPARE – WITH NONCLUSTERED INDEX ONLY (BEGIN) */
USE [Test]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
        OBJECT_ID(N'[dbo].[QueryOptimizer]') AND type in (N'U'))
BEGIN
	DROP TABLE [dbo].[QueryOptimizer]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[QueryOptimizer](
	[TerritoryID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[CountryRegionCode] [varchar](3) NOT NULL
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_QueryOptimizer_CountryRegionCode]
        ON [dbo].[QueryOptimizer]
(
	[CountryRegionCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

SET NOCOUNT ON
GO
/* PREPARE – WITH NONCLUSTERED INDEX ONLY (END) */


/* CLEAN (BEGIN) */
TRUNCATE TABLE dbo.QueryOptimizer
GO
/* CLEAN (END) */
/* STEP 1 – END */


/* TEST 1: WITH NONCLUSTERED INDEX ONLY – SMALL TABLE: SELECTIVITY (BEGIN) */
/* STEP 2 – BEGIN */
-- Execution Plan: OFF

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Northwest', 'US')
	SET @rows += 1
END
GO

DECLARE @rows INT = 1
WHILE @rows < 2 -- 1 row
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Canada', 'CA')
	SET @rows += 1
END
GO 10 -- 10 * 1 rows
/* STEP 2 – END */

/* STEP 3 – BEGIN */
-- Execution Plan: ON
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO

SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'US'
-- US: NONSELECTIVE VALUE, TABLE SCAN, 1000 of 1010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 4,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/

SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'CA'
-- CA: SELECTIVE VALUE, TABLE SCAN, 10 of 1010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 4,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/

SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO
/* STEP 3 – END */
/* TEST 1: WITH NONCLUSTERED INDEX ONLY – SMALL TABLE: SELECTIVITY (END) */


/* STEP 4 – BEGIN */
/* CLEAN (BEGIN) */
TRUNCATE TABLE dbo.QueryOptimizer
GO
/* CLEAN (END) */
/* STEP 4 – END */


/* TEST 2: WITH NONCLUSTERED INDEX ONLY – LARGE TABLE: SELECTIVITY (BEGIN) */
/* STEP 5 – BEGIN */
-- Execution Plan: OFF

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Northwest', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Northeast', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Central', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Southwest', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Southeast', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Southeast', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 2 -- 1 row
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Canada', 'CA')
	SET @rows += 1
END
GO 10 -- 10 * 1 rows
/* STEP 5 – END */

/* STEP 6 – BEGIN */
-- Execution Plan: ON
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO

SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'US'
-- US: NONSELECTIVE VALUE, TABLE SCAN, 60000 of 60010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 231,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/

SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'CA'
-- CA: SELECTIVE VALUE, INDEX SEEK (NonClustered), 10 of 60010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 12,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/

SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO
/* STEP 6 – END */
/* TEST 2: WITH NONCLUSTERED INDEX ONLY – LARGE TABLE: SELECTIVITY (END) */

Source code 4: Tests with non-clustered key index only

Figure 4: SSMS result – TEST 1: WITH NON-CLUSTERED INDEX ONLY – SMALL TABLE: SELECTIVITY (US: LOW / CA: HIGH)

Figure 4: SSMS result – TEST 2: WITH NON-CLUSTERED INDEX ONLY – LARGE TABLE: SELECTIVITY (US: LOW / CA: HIGH)

/* STEP 1 – BEGIN */
/* PREPARE – Query Hint: OPTION (OPTIMIZE FOR()) (BEGIN) */
USE [Test]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
        OBJECT_ID(N'[dbo].[QueryOptimizer]') AND type in (N'U'))
BEGIN
	DROP TABLE [dbo].[QueryOptimizer]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[QueryOptimizer](
	[TerritoryID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[CountryRegionCode] [varchar](3) NOT NULL,
 CONSTRAINT [PK_QueryOptimizer] PRIMARY KEY CLUSTERED 
(
	[TerritoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_QueryOptimizer_CountryRegionCode]
        ON [dbo].[QueryOptimizer]
(
	[CountryRegionCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

SET NOCOUNT ON
GO
/* PREPARE – Query Hint: OPTION (OPTIMIZE FOR()) (END) */


/* CLEAN (BEGIN) */
TRUNCATE TABLE dbo.QueryOptimizer
GO
/* CLEAN (END) */
/* STEP 1 – END */


/* TESTS: WITH Query Hint – OPTION (OPTIMIZE FOR())
   - LARGE TABLE: SELECTIVITY (BEGIN) */
/* STEP 2 – BEGIN */
-- Execution Plan: OFF

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Northwest', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Northeast', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Central', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Southwest', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Southeast', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Southeast', 'US')
	SET @rows += 1
END
GO 10 -- 10 * 1000 rows

DECLARE @rows INT = 1
WHILE @rows < 2 -- 1 row
BEGIN
	INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
	VALUES('Canada', 'CA')
	SET @rows += 1
END
GO 10 -- 10 * 1 rows
/* STEP 2 – END */

/* STEP 3 – BEGIN */
-- Execution Plan: ON
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO

DECLARE @WhereValue VARCHAR(50)
SET @WhereValue = 'US'
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = @WhereValue
-- US: NONSELECTIVE VALUE, INDEX SCAN (Clustered), 60000 of 60010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 223,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/

DECLARE @WhereValue VARCHAR(50)
SET @WhereValue = 'CA'
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = @WhereValue
-- CA: SELECTIVE VALUE, INDEX SCAN (Clustered), 10 of 60010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 223,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/

DECLARE @WhereValue VARCHAR(50)
SET @WhereValue = 'US'
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = @WhereValue
-- US: NONSELECTIVE VALUE, INDEX SCAN (Clustered), 60000 of 60010 rows
OPTION (OPTIMIZE FOR (@WhereValue = 'US'))
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 223,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/

DECLARE @WhereValue VARCHAR(50)
SET @WhereValue = 'CA'
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = @WhereValue
-- CA: SELECTIVE VALUE, INDEX SEEK (NonClustered), 10 of 60010 rows
OPTION (OPTIMIZE FOR (@WhereValue = 'CA'))
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 22,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/

SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO
/* STEP 3 – END */
/* TESTS: WITH Query Hint – OPTION (OPTIMIZE FOR())
   – LARGE TABLE: SELECTIVITY (END) */

Source code 5: Tests with using the query hint – OPTION (OPTIMIZE FOR())

Figure 5: SSMS result – TESTS: WITH Query Hint – OPTION (OPTIMIZE FOR()) – LARGE TABLE: SELECTIVITY (US: LOW / CA: HIGH)

The query optimizer does not know what will be the value of the variable @WhereValue at runtime and does not check statistics, before the query run. The query optimizer will always choose the operator scan.
If the value of the variable @WhereValue will most often be set to the ‘CA’, it would be smarter to optimize the query to use the seek operator instead the scan operator.
It is possible to be done by using of the query hint OPTION (OPTIMIZE FOR()).

Additional references:

Columnstore Index:
• SQL SERVER – Fundamentals of Columnstore Index
  by Grant Fritchey
• SQL SERVER – How to Ignore Columnstore Index Usage in Query
  by Pinal Dave
• SQL SERVER – Updating Data in A Columnstore Index
  by Pinal Dave

Execution Plan:

• Execution Plan Basics
  by Grant Fritchey
• SQL SERVER – Execution Plan – Estimated I/O Cost – Estimated CPU Cost – No Unit
  by Pinal Dave

Fillfactor:

• SQL SERVER – Fillfactor, Index and In-depth Look at Effect on Performance
  by Pinal Dave

Filtered Index:

• SQL SERVER – 2008 – Introduction to Filtered Index – Improve performance with Filtered Index
  by Pinal Dave

Fragmentation:

• SQL SERVER – Fragmentation – Detect Fragmentation and Eliminate Fragmentation
  by Pinal Dave
• SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance
  by Pinal Dave

Index:

• SQL SERVER – Index Seek vs. Index Scan – Difference and Usage – A Simple Note
  by Pinal Dave
• Targeted Index Performance Improvements
  by Pinal Dave

Indexed View:

• SQL SERVER – Indexed View always Use Index on Table
  by Pinal Dave
• SQL SERVER – Interesting Observation – Index on Index View Used in Similar Query
  by Pinal Dave

Performance Tuning:

• SQL Server Performance Tuning
  by Pinal Dave
• SQL SERVER – Introduction to SQL Server 2008 Profiler
  by Pinal Dave
• SQL SERVER – Performance Tuning – Part 1 of 2 – Getting Started and Configuration
  by Pinal Dave

Statistics:

• Importance of Statistics
  by Pinal Dave
• Statistics Used by the Query Optimizer in Microsoft SQL Server 2008
  by MSDN

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

SSAS basics – building my first cube

Task: to give a basic look at the building of my first data cube by means of SSDT by Microsoft Visual Studio

To build a data cube we will need to follow these few steps:

1. Creating a data source
To connect to this data source the service account is being used. Using the service account (the account that runs the SQL Server Analysis Server service) is common even in production. Make sure that the service account has privileges to read this data source.

Figure 1: Creating a data source – Configuration

2. Creating a data source view
Select FactInternetSales and click on the Add Related Tables button.

Figure 2a: Creating a data source view – Configuration

Figure 2b: Creating a data source view – Diagram of facts and dimensions

3. Building my first cube
Select the data source from the first step, then click on the Suggest button and at the end select measures and dimensions.

Figure 3a: Building my first cube

Figure 3b: Tweaking dimensions for user-friendly appearance

Any warnings appear after tweaking. We will ignore warnings for this basic example.

4. Deploying and querying the cube
At the last step, click on the Deploy Solution item on the Build menu and publish the cube.

Figure 4: Querying the cube

Double click on the cube and navigate to the Browser tab. For example, drag and drop measures such as ExtendedAmount and dimensions and hierarchies such as Fiscal Quarter – Month hierarchy under the Due Date dimension into the query pane. We will retrieve requested data.

Source code: SSAS Package (SQL Server 2008 R2 and BIDS; Adventure Works DW2008R2)
Note: do not forget to customize connection strings etc

Additional references:

• SQLAuthority News – Download Whitepaper – Choosing a Tabular or Multidimensional Modeling Experience in SQL Server 2012 Analysis Services
  by Pinal Dave
• SQL SERVER – 2005 – Analysis Services Query Performance Top 10 Best Practices
  by Pinal Dave
• SQL SERVER – Data Warehousing Interview Questions and Answers Complete List Download
  by Pinal Dave
• SQL SERVER – Guest Post – Architecting Data Warehouse – Niraj Bhatt
  by Niraj Bhatt
• SQL SERVER – Languages for BI – MDX, DMX, XMLA
  by Pinal Dave
• SQL SERVER – SSAS – Multidimensional Space Terms and Explanation
  by Pinal Dave

SSIS basics – import CSV to a SQL Server table

Task: to give a basic look at the import of a CSV file to a SQL Server table by means of SSDT by Microsoft Visual Studio

Sales per region,Africa,Asia,Europe
January,34,67,56
February,36,87,78
March,31,56,88
April,29,67,92
May,54,71,68
June,68,71,54
July,54,71,68
August,68,71,54
September,92,67,29
October,88,56,31
November,78,87,36
December,56,67,34

Source code 1: CSV data

USE [Test]
GO

CREATE TABLE [dbo].[SSIS_CSV_Import]
(
    [Month]      [varchar](9)    NOT NULL,
    [SaleAfrica] [numeric](2, 0) NOT NULL,
    [SaleAsia]   [numeric](2, 0) NOT NULL,
    [SaleEurope] [numeric](2, 0) NOT NULL
)
GO

Source code 2: Table structure

Figure 1: Flat File Connection Manager Editor – General

Figure 2: Flat File Connection Manager Editor – Columns

Figure 3: Flat File Connection Manager Editor – Advanced

Figure 4: SQL Destination Editor – Connection Manager

Figure 5: SQL Destination Editor – Mappings

Figure 6: SSIS Package - Data Flow

SELECT [Month], [SaleAfrica], [SaleAsia], [SaleEurope]
FROM   [dbo].[SSIS_CSV_Import]
GO
/* Result:
   Month       | Africa | Asia | Europe
   January       34       67	 56
   February      36       87     78
   March         31       56     88
   April         29       67     92
   May	         54       71     68
   June	         68       71     54
   July	         54       71     68
   August        68       71     54
   September     92       67     29
   October       88       56     31
   November      78       87     36
   December      56       67     34
*/

Source code 3: SQL query for result verification

Additional references:

• Comma-separated values
  by Wikipedia
• SQL SERVER – Import CSV into Database – Transferring File Content into a Database Table using CSVexpress
  by Pinal Dave
• SQL SERVER – Importing CSV File Into Database – SQL in Sixty Seconds #018 – Video
  by Pinal Dave
• SQL SERVER – Powershell – Importing CSV File Into Database – Video
  by Laerte Junior
• SQL SERVER – Running SSIS Package in Scheduled Job
  by Pinal Dave

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

Creating an automated database backup by means of SQL Server Agent Job

Task: to create a fully automatic database backup system by means of SQL Server Agent Job with an e-mail fails notification subsystem

EXEC [master].[dbo].[sp_addumpdevice]
     @devtype      = N'disk',
     @logicalname  = N'AdventureWorks2008R2',
     @physicalname = N'D:\SqlData\MSSQL\Backup\AdventureWorks2008R2.bak'
GO

Source code 1: SSMS -> Object Explorer -> [sql_server_instance] -> Server Objects -> Backup Devices -> AdventureWorks2008R2

EXEC [master].[dbo].[sp_configure] 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC [master].[dbo].[sp_configure] 'xp_cmdshell', 1
GO
RECONFIGURE
GO

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Backup]
AS
BEGIN
    DECLARE @Out INT
    
    -- NOTE: make sure the database is in Full Recovery Model and is online!
    ALTER DATABASE [AdventureWorks2008R2]
    SET RECOVERY FULL
    ALTER DATABASE [AdventureWorks2008R2]
    SET ONLINE
    -- NOTE: functional for a FileStream database as well!
    
    DBCC CHECKDB([AdventureWorks2008R2])
    
    -- Create a database backup
    EXEC @Out = [master]..[xp_cmdshell]
                N'D:\SqlData\MSSQL\Backup\BACKUP_FILO_BEGIN.bat'
    
    CHECKPOINT
    
    BACKUP LOG [AdventureWorks2008R2]
               TO DISK='NUL' --WITH TRUNCATE_ONLY
    BACKUP DATABASE [AdventureWorks2008R2]
                    TO [AdventureWorks2008R2] WITH COMPRESSION --WITH INIT
    
    -- Copy the backup database to a tape
    EXEC @Out = [master]..[xp_cmdshell]
                N'D:\SqlData\MSSQL\Backup\BACKUP_FILO_END.bat'
END
GO

Source code 2: SSMS -> Object Explorer -> [sql_server_instance] -> Databases -> System Databases -> master -> Programmability -> Stored Procedures -> dbo.Backup

REM ***Creating a Database Backup***

REM Path: "D:\SqlData\MSSQL\Backup\"
SET FILEPATH="D:\SqlData\MSSQL\Backup\"

SET FILENAME="AdventureWorks2008R2"

IF EXIST %FILEPATH%\%FILENAME%.BA4 DEL    %FILEPATH%\%FILENAME%.BA4
IF EXIST %FILEPATH%\%FILENAME%.BA3 RENAME %FILEPATH%\%FILENAME%.BA3 %FILENAME%.BA4
IF EXIST %FILEPATH%\%FILENAME%.BA2 RENAME %FILEPATH%\%FILENAME%.BA2 %FILENAME%.BA3
IF EXIST %FILEPATH%\%FILENAME%.BA1 RENAME %FILEPATH%\%FILENAME%.BA1 %FILENAME%.BA2
IF EXIST %FILEPATH%\%FILENAME%.BAK COPY   %FILEPATH%\%FILENAME%.BAK %FILEPATH%\%FILENAME%.BA1

SET FILENAME=
SET FILEPATH=

Source code 3: Creating a database backup – BACKUP_FILO_BEGIN.bat

REM ***Copying the Database Backup to a Tape***

REM Path: "D:\SqlData\MSSQL\Backup\"
SET FILEPATHSRC="D:\SqlData\MSSQL\Backup\"

REM Path: "\\[server_name]\SQL_BAK$"
SET FILEPATHDST="\\[server_name]\SQL_BAK$"

SET FILENAME="AdventureWorks2008R2"

COPY %FILEPATHSRC%\%FILENAME%.BAK %FILEPATHDST%\%FILENAME%.BAK

SET FILENAME=
SET FILEPATHSRC=
SET FILEPATHDST=

Source code 4: Copying the database backup to a tape – BACKUP_FILO_END.bat

USE [msdb]
GO

EXEC [msdb].[dbo].[sp_add_operator]
     @name                      = N'BACKUP',
     @enabled                   = 1,
     @weekday_pager_start_time  = 90000,
     @weekday_pager_end_time    = 180000,
     @saturday_pager_start_time = 90000,
     @saturday_pager_end_time   = 180000,
     @sunday_pager_start_time   = 90000,
     @sunday_pager_end_time     = 180000,
     @pager_days                = 0,
     @email_address             = N'test@example.eu',
     @category_name             = N'[Uncategorized]'
GO

Source code 5: SSMS -> Object Explorer -> [sql_server_instance] -> SQL Server Agent -> Operators -> BACKUP

USE [msdb]
GO

BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT  @ReturnCode = 0
    
    IF NOT EXISTS
    (
        SELECT [name]
        FROM   [msdb].[dbo].[syscategories]
        WHERE  [name]           = N'Database Maintenance'
               AND
               [category_class] = 1 -- Job
    )
    BEGIN
        EXEC @ReturnCode = [msdb].[dbo].[sp_add_category]
                           @class = N'JOB',
                           @type  = N'LOCAL',
                           @name  = N'Database Maintenance'
        IF (@@ERROR != 0 OR @ReturnCode != 0)
        BEGIN
            GOTO QuitWithRollback
        END
    END
    
    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode = [msdb].[dbo].[sp_add_job]
		       @job_name                   = N'BACKUP',
		       @enabled                    = 1,
		       @notify_level_eventlog      = 2,
		       @notify_level_email         = 2,
		       @notify_level_netsend       = 0,
		       @notify_level_page          = 0,
		       @delete_level               = 0,
		       @description                = N'Creating a database backup by means of SQL Server Agent Job',
		       @category_name              = N'Database Maintenance',
		       @owner_login_name           = N'NB0046\Marian Placko',
                       @notify_email_operator_name = N'BACKUP',
                       @job_id                     = @jobId OUTPUT
    IF (@@ERROR != 0 OR @ReturnCode != 0)
    BEGIN
        GOTO QuitWithRollback
    END
    EXEC @ReturnCode = [msdb].[dbo].[sp_add_jobstep]
		       @job_id               = @jobId,
		       @step_name            = N'BACKUP',
		       @step_id              = 1,
		       @cmdexec_success_code = 0,
		       @on_success_action    = 1,
		       @on_success_step_id   = 0,
		       @on_fail_action       = 2,
		       @on_fail_step_id      = 0,
		       @retry_attempts       = 0,
		       @retry_interval       = 0,
		       @os_run_priority      = 0,
		       @subsystem            = N'TSQL',
		       @command              = N'EXEC [dbo].[Backup]',
		       @database_name        = N'master',
		       @flags                = 0
    IF (@@ERROR != 0 OR @ReturnCode != 0)
    BEGIN
        GOTO QuitWithRollback
    END
    EXEC @ReturnCode = [msdb].[dbo].[sp_update_job]
                       @job_id        = @jobId,
                       @start_step_id = 1
    IF (@@ERROR != 0 OR @ReturnCode != 0)
    BEGIN
        GOTO QuitWithRollback
    END
    DECLARE @guid UNIQUEIDENTIFIER
    SET     @guid = NEWID()
    EXEC @ReturnCode = [msdb].[dbo].[sp_add_jobschedule]
		       @job_id                 = @jobId,
		       @name                   = N'BACKUP',
		       @enabled                = 1,
		       @freq_type              = 8,
		       @freq_interval          = 62,
		       @freq_subday_type       = 1,
		       @freq_subday_interval   = 0,
		       @freq_relative_interval = 0,
		       @freq_recurrence_factor = 1,
		       @active_start_date      = 20111230,
		       @active_end_date        = 99991231,
		       @active_start_time      = 200000,
		       @active_end_time        = 235959,
		       @schedule_uid           = @guid
    IF (@@ERROR != 0 OR @ReturnCode != 0)
    BEGIN
        GOTO QuitWithRollback
    END
    EXEC @ReturnCode = [msdb].[dbo].[sp_add_jobserver]
                       @job_id      = @jobId,
                       @server_name = N'(local)'
    IF (@@ERROR != 0 OR @ReturnCode != 0)
    BEGIN
        GOTO QuitWithRollback
    END
COMMIT TRANSACTION
    GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0)
    BEGIN
        ROLLBACK TRANSACTION
    END
EndSave:
GO

Source code 6: SSMS -> Object Explorer -> [sql_server_instance] -> SQL Server Agent -> Jobs -> BACKUP

-- Enable a Database Mail
[master].[dbo].[sp_configure] 'show advanced options', 1
GO
RECONFIGURE
GO
[master].[dbo].[sp_configure] 'Database Mail XPs', 1
GO
RECONFIGURE
GO

-- Set up an Account
 -- Port number: 25
 -- SMPT authentication: anonymous
EXECUTE [msdb].[dbo].[sysmail_add_account_sp]
        @account_name    = 'BACKUP',
        @description     = 'Creating a database backup by means of SQL Server Agent Job',
        @email_address   = 'test@example.eu',
        @display_name    = 'BACKUP',
        @replyto_address = 'test@example.eu',
        @mailserver_name = 'localhost' -- NOTE: SMTP server must be set up!
GO

-- Set up a Profile
EXECUTE [msdb].[dbo].[sysmail_add_profile_sp]
        @profile_name = 'BACKUP',
        @description  = 'Creating a database backup by means of SQL Server Agent Job'
GO

-- Associate the Profile with the Account
EXECUTE [msdb].[dbo].[sysmail_add_profileaccount_sp]
        @profile_name    = 'BACKUP',
        @account_name    = 'BACKUP',
        @sequence_number = 1
GO

-- Add NB0046\Marian Placko to the Database Mail Role
EXEC [msdb].[dbo].[sp_addrolemember]
     @rolename   = 'DatabaseMailUserRole',
     @membername = 'NB0046\Marian Placko'
GO

-- Grant access to the Profile to NB0046\Marian Placko
EXEC [msdb].[dbo].[sysmail_add_principalprofile_sp]
     @profile_name   = 'BACKUP',
     @principal_name = 'NB0046\Marian Placko',
     @is_default     = 0
GO

-- Send a test e-mail
EXEC [msdb].[dbo].[sp_send_dbmail]
     @profile_name='BACKUP',
     @recipients='test@example.eu',
     @subject='Test message',
     @body='This is the body of the test message.'

Source code 7: SSMS -> Object Explorer -> [sql_server_instance] -> Management -> Database Mail -> BACKUP

-- Enable SQL Server Agent to use the Database Mail
 -- NOTE: make sure to start SQL Server Agent!
 EXEC master.dbo.xp_ServiceControl 'QUERYSTATE','SQLServerAgent'
 -- Current Service State: Running.
 --EXEC master.dbo.xp_ServiceControl 'START', 'SQLServerAgent'
 GO

EXEC [msdb].[dbo].[sp_set_sqlagent_properties]
     @email_save_in_sent_folder = 1

EXEC [master].[dbo].[xp_instance_regwrite]
     N'HKEY_LOCAL_MACHINE',
     N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
     N'UseDatabaseMail',
     N'REG_DWORD',
     1

EXEC [master].[dbo].[xp_instance_regwrite]
     N'HKEY_LOCAL_MACHINE',
     N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
     N'DatabaseMailProfile',
     N'REG_SZ',
     N'BACKUP'
GO

-- Enable SQL Server Agent to set Fail-safe Operator
EXEC [master].[dbo].[sp_MSsetalertinfo]
     @failsafeoperator   = N'BACKUP',
     @notificationmethod = 1 -- E-mail
GO
 -- NOTE: make sure to restart SQL Server Agent!
 EXEC master.dbo.xp_ServiceControl 'STOP', 'SQLServerAgent'
 GO
 -- Message: Service Stopped.
 EXEC master.dbo.xp_ServiceControl 'START', 'SQLServerAgent'
 GO
 -- Message: Service Started.

Source code 8: SSMS -> Object Explorer -> [sql_server_instance] -> SQL Server Agent -> Properties -> Alert System

Additional references:

• Automatizovaná záloha všech databází na SQL Express
  by Michal Altair Valášek
• Backup Log with Truncate_Only
  by Brent Ozar
• Comprehensive Backup Script
  by Simon Facer
• Configuring SQL Server Agent Operators
  by Taylor Gerring
• Database Backups: Things You Need To Do Now
  by The Scary DBA
• Princip práce SQL Server Recovery Models
  by Marek Chmel
• SQL Backup and Restore Bundle
  by redgate
• SQL safe backup
  by idera
• SQL SERVER – 2005 – Start Stop Restart SQL Server From Command Prompt
  by Pinal Dave
• SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database
  by Pinal Dave
• SQL SERVER – 2008 – Introduction to New Feature of Backup Compression
  by Pinal Dave
• SQL SERVER – A Quick Script for Point in Time Recovery – Back Up and Restore
  by Pinal Dave
• SQL SERVER – Backup Timeline and Understanding of Database Restore Process in Full Recovery Model
  by Pinal Dave
• SQL SERVER – Effect of Compressed Backup Setting at Server Level on Database Backup
  by Pinal Dave
• SQL SERVER – Retrieve and Explore Database Backup without Restoring Database – Idera virtual database
  by Pinal Dave
• SQL SERVER – Shrinking Truncate Log File – Log Full
  by Pinal Dave
• truncate_only’ is not a recognized BACKUP option
  by MSDN
• Using Database Mail in SQL Server 2008
  by Jeff Hunter