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 1: SQLCMD result – the procedure sys.sp_who2 before hacking


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

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


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

Change Tracking – Control Flow

Figure 1: Change Tracking – Control Flow


Change Tracking – Data 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:
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

Deleting duplicates by means of Fuzzy logic

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


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

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


SSMS result – TEST 2: WITHOUT INDICES – LARGE 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


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

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


SSMS result – TEST 2: WITH INDICES – LARGE 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


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

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


SSMS result – TEST 2: WITH PRIMARY KEY INDEX ONLY – LARGE 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


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

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


SSMS result – TEST 2: WITH NON-CLUSTERED INDEX ONLY – LARGE 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())


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

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



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

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

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

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

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

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

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


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


Additional references:

SSAS basics – building my first cube

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

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 2a: Creating a data source view – Configuration


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

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 3a: Building my first cube


Figure 3b: Tweaking dimensions for user-friendly appearance

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

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


Flat File Connection Manager Editor – General

Figure 1: Flat File Connection Manager Editor – General


Flat File Connection Manager Editor – Columns

Figure 2: Flat File Connection Manager Editor – Columns


Flat File Connection Manager Editor – Advanced

Figure 3: Flat File Connection Manager Editor – Advanced


SQL Destination Editor – Connection Manager

Figure 4: SQL Destination Editor – Connection Manager


SQL Destination Editor – Mappings

Figure 5: SQL Destination Editor – Mappings


SSIS Package – Data Flow

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:

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