T-SQL Grouping Set Showcase

Greetings all,

In the interim, whilst I juggle my time to get more in-depth posts rolled out (like the one surrounding Alexa skills), I back-tracked once again to look at overlooked features from the distant past of SQL 2008. I’ve chosen the topic of GROUPING SETS as the basis of this post; which basically boils down to a really nice way to return multiple levels of aggregation in a single result set.

A quick run through then – the following example illustrates a sample database with four tables; Staff, Perk, HolidayRequest and SalaryPayment. The HolidayRequest table has a computed column (based on ‘DateFrom’ and ‘DateTo’) that specifies how many days holiday have been requested (per request) and the SalaryPayment table has an ‘Amount’ column (salary per month). These represent our key aggregation fields. I haven’t actually referenced all columns in the samples provided; I just wanted to give the examples a little bit of extra padding (so they feel more complete).

The following T-SQL shows aggregations targeting total holiday days requested grouped by staff name, department and department and job title (see the GetHolidayRequestDaysSumInfo stored procedure in the full listing for further details):

-- Holiday request days (summed), grouped by staff name (two daves are cobbled together here)
SELECT 
s.FirstName + ' ' + s.LastName AS [StaffName]
, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
FROM dbo.Staff s
	INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
GROUP BY s.FirstName + ' ' + s.LastName;

-- Holiday request days (summed), grouped by department
SELECT 
s.Department
, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
FROM dbo.Staff s
	INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
GROUP BY Department;

-- Holiday request days (summed), grouped by department and job title
SELECT
s.Department
, s.JobTitle
, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
FROM dbo.Staff s
	INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
GROUP BY s.Department, s.JobTitle;

The results look like this:

Holiday Request Group Queries.

Holiday Request Group Queries.

With traditional GROUP BY statements you need to split aggregations, so you can only perform one per result set. This is where GROUPING SETS come in handy; here is a single statement that returns the results from the statements above, but in a single result set (again, see the GetHolidayRequestDaysSumInfoUsingGroupingSets for more information, within the full listing):

-- Holiday request days (summed), group sets included for staff name, department and department and job title
SELECT 
s.FirstName + ' ' + s.LastName AS [StaffName]
, s.Department
, s.JobTitle
, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
FROM dbo.Staff s
	INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
GROUP BY GROUPING SETS
(
	(s.FirstName + ' ' + s.LastName)
	, (s.Department)
	, (s.Department, s.JobTitle)
);

Again, here are the super fabulous results from this particular query:

Holiday Request Grouping Set Queries.

Holiday Request Grouping Set Queries.

Pretty neat! Just to nail the point home here are a few extra examples focusing on the summing of salary payments, grouped by various facets (individual statements, for starters, as shown in the GetSalarySumInfo procedure):

-- Salary sum info grouped by staff name (two daves are, of course, grouped by this)
SELECT 
s.FirstName + ' ' + s.LastName AS [StaffName]
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY s.FirstName + ' ' + s.LastName;

-- Salary sum info grouped by job title
SELECT 
s.JobTitle
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY s.JobTitle;

-- Salary sum info grouped by department
SELECT 
s.Department
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY s.Department

-- Salary sum info grouped by perk name
SELECT 
p.PerkName
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.Perk p ON s.Perk = p.Id
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY p.PerkName;

-- Salary sum info grouped by department and job title
SELECT
s.Department
, s.JobTitle
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY s.Department, s.JobTitle;

Results from our individual statements look a little like this:

Salary Group Queries.

Salary Group Queries.

Lastly, here’s the equivalent, single, query using GROUPING SETS (see GetSalarySumInfoUsingGroupingSets in the full listing):

-- Salary sum info grouped by staff name, department, job title, perk name and department/job title, in a single result set
SELECT 
s.FirstName + ' ' + s.LastName AS [StaffName]
, s.Department
, s.JobTitle
, p.PerkName
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.Perk p ON s.Perk = p.Id
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY GROUPING SETS
(
	(s.FirstName + ' ' + s.LastName)
	, (s.JobTitle)
	, (s.Department)
	, (p.PerkName)
	, (s.Department, s.JobTitle)
);
Salary Grouping Set Queries.

Salary Grouping Set Queries.

A great little language feature that I have largely overlooked in the past. I do have one or two reservations over performance, but a first inspection of the query plan for the ‘salary’ based example suggests that some marginal gains are on the table, using the stock setup I have (note the table spool, also):

Grouping Sets Performance.

Grouping Sets Performance.

Feels to me like something that deserves further investigation.

Here is the complete listing, if you wish to create the tables with the sample data referenced in this post (procedures, and all, bundled in). Thanks for tuning in!

/* 

	Lewis Grint - 18/03/2017

	GROUPING SETS WORKING SAMPLE 
	-------------------------------------------------
	1) This example uses four mock database tables (created internally by the script, no work required :op). Run this on a test database of your choice.
		- Staff.
		- Perk.
		- HolidayRequest.
		- SalaryPayment.
	2) Utility, mock data is also inserted by this script (table drop statements, if required, can be found just below this header).
	3) The sample data is then aggregated using GROUP BY via two traditionally structured stored procedures (GetHolidayRequestDaysSumInfo and GetSalarySumInfo). Data is returned (for each grouping) using a separate result set.
	4) Stored procedure variants are then presented that using GROUPING SETS - All GROUP BY variations are returned in a single result set (for illustration purposes only, not saying my grouping selections are actually that helpful in the real world!).

*/

/*
-- Drop table statements, as and when required
DROP TABLE dbo.HolidayRequest;
DROP TABLE dbo.SalaryPayment;
DROP TABLE dbo.Staff;
DROP TABLE dbo.Perk;

*/

-- Habitual setting of preferred QUOTED_IDENTIFIER/ANSI_NULL configuration (even if it's not important for this example)
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO

-- Utility variables for gathering up the correct StaffId, when required (and payment dates)
DECLARE
@StaffId							INT
, @FebPayDate						DATE = '2017-02-28'
, @JanPayDate						DATE = '2017-01-31'
, @DecPayDate						DATE = '2016-12-31';

-- 1) Create sample tables to illustrate how GROUPING SETS work

-- Perk table added for nothing more than extra kicks. A staff member can select a company perk (referenced from the Staff table)
IF (OBJECT_ID('dbo.Perk', 'U') IS NULL)
BEGIN

	CREATE TABLE dbo.Perk
	(
		Id							INT				PRIMARY KEY		IDENTITY(1, 1)	NOT NULL
		, PerkName					NVARCHAR(30)									NOT NULL
		, INDEX IX_Perk_PerkName	NONCLUSTERED (PerkName)					
	);

END;

-- NOTE: I won't be including more indexes from this point forward, to try and keep the example light(er)

-- Staff table, which for the sake of our example contains first name, last name, job title, department and a fk to 'perks'
IF (OBJECT_ID('dbo.Staff', 'U') IS NULL)
BEGIN

	CREATE TABLE dbo.Staff
	(
		Id							INT				PRIMARY KEY		IDENTITY(1, 1)	NOT NULL
		, FirstName					NVARCHAR(50)									NOT NULL
		, LastName					NVARCHAR(50)									NOT NULL
		, JobTitle					NVARCHAR(50)									NOT NULL
		, Department				NVARCHAR(50)									NOT NULL
		, Perk						INT												NOT NULL
		, CONSTRAINT FK_Staff_Perk	FOREIGN KEY (Perk) REFERENCES dbo.Perk (Id)
	);

END;

-- SalaryPayment table, which contains multiple rows for each staff member (one for each salary payment)
IF (OBJECT_ID('dbo.SalaryPayment', 'U') IS NULL)
BEGIN

	CREATE TABLE dbo.SalaryPayment
	(
		Id							INT				PRIMARY KEY		IDENTITY(1, 1)	NOT NULL
		, StaffId					INT												NOT NULL
		, PaymentDate				DATE											NOT NULL
		, Amount					MONEY
		, CONSTRAINT FK_SalaryPayment_Staff			FOREIGN KEY (StaffId) REFERENCES dbo.Staff (Id)
	);

END;

-- HolidayRequest table, which contains multiple rows for each staff member (one for each holiday request)
IF (OBJECT_ID('dbo.HolidayRequest', 'U') IS NULL)
BEGIN

	CREATE TABLE dbo.HolidayRequest
	(
		Id							INT				PRIMARY KEY		IDENTITY(1, 1)	NOT NULL
		, StaffId					INT												NOT NULL
		, DateFrom					DATE											NOT NULL
		, DateTo					DATE											NOT NULL
		-- Final column is computed based on the difference between the start and end date (requested), in days. Simple, but fits for the purposes of the example (obviously, doesn't take into account weekends, bank holiday, etc.)
		, NumberOfDaysRequested		AS (DATEDIFF(DAY, DateFrom, DateTo) + 1)
		, CONSTRAINT FK_HolidayRequest_Staff		FOREIGN KEY (StaffId) REFERENCES dbo.Staff (Id)
	);

END;

--2) Insert test data into each table

-- Perk table test data
IF NOT EXISTS
(
	SELECT *
	FROM dbo.Perk p
)
BEGIN

	INSERT INTO dbo.Perk
	(
		PerkName
	)
	VALUES 
	(
		'Free ice cream'
	)
	,
	(
		'Free parking'
	)
	,
	(
		'Beer on arrival'
	)
	,
	(
		'Pat on the back'
	);

END;

-- Staff table test data (including perk info)
IF NOT EXISTS
(
	SELECT *
	FROM dbo.Staff s
)
BEGIN

	INSERT INTO dbo.Staff
	(
		FirstName
		, LastName
		, JobTitle
		, Department
		, Perk	
	)
	VALUES
	(
		'Steve'
		, 'Stevenson'
		, 'Head Honcho'
		, 'Ivory Tower'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Free ice cream'
		)
	)
	,
	(
		'Marie'
		, 'Pritchard'
		, 'Team Manager'
		, 'Ivory Tower'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Free parking'
		)
	)
	,
	(
		'Judy'
		, 'Dench'
		, 'Team Manager'
		, 'Island Retreat'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Free ice cream'
		)
	)
	,
	(
		'Dave'
		, 'Dodger'
		, 'Chief Work Dodger'
		, 'Store Cupboard'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Beer on arrival'
		)
	)
	-- There's another Dave Dodger at the company, but he is actually a pretty useful chap
	,
	(
		'Dave'
		, 'Dodger'
		, 'Hard Worker'
		, 'Store Cupboard'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Pat on the back'
		)
	)
	,
	(
		'Bob'
		, 'Boots'
		, 'Handle Cranker'
		, 'Main Office'
		,
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Pat on the back'
		)
	)
	,
	(
		'Janet'
		, 'Timms'
		, 'Handle Cranker'
		, 'Main Office'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Pat on the back'
		)
	);

END;

-- SalaryPayment table test data
IF NOT EXISTS
(
	SELECT *
	FROM dbo.SalaryPayment sp
)
BEGIN
	
	-- Steve Stevenson | Head Honcho
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Steve'
		AND s.LastName = 'Stevenson'
		AND s.JobTitle = 'Head Honcho'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 5580.50
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 5240.50
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 5580.50
	);

	-- Marie Pritchard | Team Manager
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Marie'
		AND s.LastName = 'Pritchard'
		AND s.JobTitle = 'Team Manager'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2500.75
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2425.15
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2425.15
	);

	-- Judy Dench | Team Manager
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Judy'
		AND s.LastName = 'Dench'
		AND s.JobTitle = 'Team Manager'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2495.75
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2400.15
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2400.15
	);

	-- Dave Dodger | Chief Work Dodger
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Dave'
		AND s.LastName = 'Dodger'
		AND s.JobTitle = 'Chief Work Dodger'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2122.90
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2105.20
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2105.20
	);

	-- Dave Dodger | Hard Worker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Dave'
		AND s.LastName = 'Dodger'
		AND s.JobTitle = 'Hard Worker'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2115.50
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2100.50
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2100.50
	);

	-- Bob Boots | Handle Cranker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Bob'
		AND s.LastName = 'Boots'
		AND s.JobTitle = 'Handle Cranker'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2100.00
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2039.50
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2039.50
	);

	-- Janet Timms | Handle Cranker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Janet'
		AND s.LastName = 'Timms'
		AND s.JobTitle = 'Handle Cranker'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2100.00
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2039.50
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2039.50
	);
	
END;

-- HolidayRequest table test data
IF NOT EXISTS
(
	SELECT *
	FROM dbo.HolidayRequest hr
)
BEGIN

	-- For ease, everyone has three holiday requests
	
	-- Steve Stevenson | Head Honcho
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Steve'
		AND s.LastName = 'Stevenson'
		AND s.JobTitle = 'Head Honcho'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-01-30'
		, '2017-02-03'
	)
	,
	(
		@StaffId
		, '2017-05-22'
		, '2017-05-26'
	)
	,
	(
		@StaffId
		, '2017-07-24'
		, '2017-07-28'
	);

	-- Marie Pritchard | Team Manager
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Marie'
		AND s.LastName = 'Pritchard'
		AND s.JobTitle = 'Team Manager'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-03-23'
		, '2017-03-24'
	)
	,
	(
		@StaffId
		, '2017-06-12'
		, '2017-06-16'
	)
	,
	(
		@StaffId
		, '2017-08-14'
		, '2017-08-14'
	);

	-- Judy Dench | Team Manager
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Judy'
		AND s.LastName = 'Dench'
		AND s.JobTitle = 'Team Manager'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-03-24'
		, '2017-03-25'
	)
	,
	(
		@StaffId
		, '2017-06-12'
		, '2017-06-15'
	)
	,
	(
		@StaffId
		, '2017-08-15'
		, '2017-08-16'
	);

	-- Dave Dodger | Chief Work Dodger
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Dave'
		AND s.LastName = 'Dodger'
		AND s.JobTitle = 'Chief Work Dodger'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-03-20'
		, '2017-03-24'
	)
	,
	(
		@StaffId
		, '2017-03-27'
		, '2017-03-31'
	)
	,
	(
		@StaffId
		, '2017-04-03'
		, '2017-04-07'
	);

	-- Dave Dodger | Hard Worker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Dave'
		AND s.LastName = 'Dodger'
		AND s.JobTitle = 'Hard Worker'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-03-31'
		, '2017-03-31'
	)
	,
	(
		@StaffId
		, '2017-07-31'
		, '2017-07-31'
	)
	,
	(
		@StaffId
		, '2017-09-25'
		, '2017-09-25'
	);

	-- Bob Boots | Handle Cranker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Bob'
		AND s.LastName = 'Boots'
		AND s.JobTitle = 'Handle Cranker'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-05-15'
		, '2017-05-19'
	)
	,
	(
		@StaffId
		, '2017-07-10'
		, '2017-07-12'
	)
	,
	(
		@StaffId
		, '2017-10-11'
		, '2017-10-13'
	);

	-- Janet Timms | Handle Cranker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Janet'
		AND s.LastName = 'Timms'
		AND s.JobTitle = 'Handle Cranker'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-04-10'
		, '2017-04-11'
	)
	,
	(
		@StaffId
		, '2017-06-21'
		, '2017-06-23'
	)
	,
	(
		@StaffId
		, '2017-11-13'
		, '2017-11-17'
	);

END;
GO

-- Create utility stored procedures, just because :op

-- Basic example using the HolidayRequest table
IF (OBJECT_ID('dbo.GetHolidayRequestDaysSumInfo', 'P') IS NOT NULL)
BEGIN

	DROP PROCEDURE dbo.GetHolidayRequestDaysSumInfo;

END;
GO

CREATE PROCEDURE dbo.GetHolidayRequestDaysSumInfo
AS
BEGIN

	-- Holiday request days (summed), grouped by staff name (two daves are cobbled together here)
	SELECT 
	s.FirstName + ' ' + s.LastName AS [StaffName]
	, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
	FROM dbo.Staff s
		INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
	GROUP BY s.FirstName + ' ' + s.LastName;

	-- Holiday request days (summed), grouped by department
	SELECT 
	s.Department
	, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
	FROM dbo.Staff s
		INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
	GROUP BY Department;

	-- Holiday request days (summed), grouped by department and job title
	SELECT
	s.Department
	, s.JobTitle
	, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
	FROM dbo.Staff s
		INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
	GROUP BY s.Department, s.JobTitle;

END;
GO

IF (OBJECT_ID('dbo.GetHolidayRequestDaysSumInfoUsingGroupingSets', 'P') IS NOT NULL)
BEGIN

	DROP PROCEDURE dbo.GetHolidayRequestDaysSumInfoUsingGroupingSets;

END;
GO

CREATE PROCEDURE dbo.GetHolidayRequestDaysSumInfoUsingGroupingSets
AS
BEGIN

	-- Holiday request days (summed), group sets included for staff name, department and department and job title
	SELECT 
	s.FirstName + ' ' + s.LastName AS [StaffName]
	, s.Department
	, s.JobTitle
	, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
	FROM dbo.Staff s
		INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
	GROUP BY GROUPING SETS
	(
		(s.FirstName + ' ' + s.LastName)
		, (s.Department)
		, (s.Department, s.JobTitle)
	);

END;
GO

-- A couple more stored prodecures, focusing on the SalaryPayment table this time
IF (OBJECT_ID('dbo.GetSalarySumInfo', 'P') IS NOT NULL)
BEGIN

	DROP PROCEDURE dbo.GetSalarySumInfo;

END;
GO

CREATE PROCEDURE dbo.GetSalarySumInfo
AS
BEGIN

	-- Salary sum info grouped by staff name (two daves are, of course, grouped by this)
	SELECT 
	s.FirstName + ' ' + s.LastName AS [StaffName]
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY s.FirstName + ' ' + s.LastName;

	-- Salary sum info grouped by job title
	SELECT 
	s.JobTitle
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY s.JobTitle;

	-- Salary sum info grouped by department
	SELECT 
	s.Department
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY s.Department

	-- Salary sum info grouped by perk name
	SELECT 
	p.PerkName
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.Perk p ON s.Perk = p.Id
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY p.PerkName;

	-- Salary sum info grouped by department and job title
	SELECT
	s.Department
	, s.JobTitle
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY s.Department, s.JobTitle;

END;
GO

IF (OBJECT_ID('dbo.GetSalarySumInfoUsingGroupingSets', 'P') IS NOT NULL)
BEGIN

	DROP PROCEDURE dbo.GetSalarySumInfoUsingGroupingSets;

END;
GO

CREATE PROCEDURE dbo.GetSalarySumInfoUsingGroupingSets
AS
BEGIN

	-- Salary sum info grouped by staff name, department, job title, perk name and department/job title, in a single result set
	SELECT 
	s.FirstName + ' ' + s.LastName AS [StaffName]
	, s.Department
	, s.JobTitle
	, p.PerkName
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.Perk p ON s.Perk = p.Id
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY GROUPING SETS
	(
		(s.FirstName + ' ' + s.LastName)
		, (s.JobTitle)
		, (s.Department)
		, (p.PerkName)
		, (s.Department, s.JobTitle)
	);

END;
GO

-- Execute stored procedures to inspect the results

-- Holiday aggregation results (grouping sets stored procedure variant returns all data in a single result set)
EXEC dbo.GetHolidayRequestDaysSumInfo;
EXEC dbo.GetHolidayRequestDaysSumInfoUsingGroupingSets;

-- Salary aggregation results (grouping sets stored procedure variant returns all data in a single result set)
EXEC dbo.GetSalarySumInfo;
EXEC dbo.GetSalarySumInfoUsingGroupingSets;

SQL Server Management Studio…Customising those themes!

bear-brush-ssms-cust-themes-post

Howdy all,

I am a bit of a customisation nut; pretty much with any software package I pick up I have the uncontrollable urge to dig straight into the options and get on the hunt for how to ‘theme it up’!

The one that probably upsets me the most is poor old SQL Server Management Studio (SSMS). SSMS has long lagged behind, considering sharing the Visual Studio shell, on the inclusion of a dark theme. I ended up going on a scrummage around as it seems pretty incredible that this isn’t available yet. This is what I managed to dig up:

SQL Server Management Studio Dark/Black Theme

Back in April of 2016, this was the news at the time:

“We currently have the dark theme disabled as we need to do a lot more UI enhancements across all the SSMS dialogs and wizards to make this work properly. We will be working on this and true high DPI support in the coming months.”

The options on offer at the moment, by default, are a snooze fest at best:

Image showing standard colour themes in SSMS.

SSMS Standard Colour Themes.

By way of example, here is the light theme doing its thing:

Image showing the SSMS Light Theme.

SSMS Light Theme.

Alas, it sounds like we might be waiting a while longer for true dark theme support but…if you simply what to customise the code window you can (as in Visual Studio) work some magic in the interim, as follows (yes, the other dockable portions of the UI do not change, but it’s a start).

As a first step, you need to go in search of themes or, more specifically, custom made ‘.vssettings’ files. This is the resource I have used in the past and in this example:

Studio Styles

There are a tonne of pretty decent themes on this site. Here are some that immediately interested me, on first glance (as I’m a dark theme psycho):

  1. Son of Obsidian
  2. Smooth
  3. IR Black
  4. Visual Blend
  5. EaseEye
  6. Electric Energy
  7. Sublime Text 2 – New Edition
  8. Colorful Darkness

If anyone has a good recommendation for other places to go and seek out themes please leave a comment below.

Once you have downloaded any .vssettings files you are interested in (for SQL Server 2016 I have been using the VS 2015 files, without issues thus far), place them in a location that is easily accessible. Now, you need to fly on over to SSMS where the process of importing a .vssettings file is incredibly easy.

Start by navigating to Tools > Import and Export Settings, as below:

Image showing the Import and Export settings on the Tools menu in SSMS.

Import and Export Settings.

Next up, check the option entitled ‘Import selected environment settings’ from the following dialog, and click Next:

Image showing a dialog in SSMS allowing importing of custom themes.

Import Selected Environment Settings.

Next up, you can save your current settings to a .vssettings file, if you choose. This is a pretty solid bet if you have imported a new theme and have made customisations to it yourself, as you will want an easy way to rollback should your eyes be offended by whatever you import next 😉 ! If you like living on the edge there is always the ‘No, just import new settings, overwriting my current settings’ option. Pick the option that makes you feel good, then click Next:

Image showing how to save the current settings in SSMS.

Save Current Theme.

Here comes the meat in the customisation sandwich; browse to the .vssettings file location (where you have stashed one or more settings files) and pick one of your choosing, then click Next:

Image showing the selection of a new settings file.

Select New Settings File.

Check the dialog that pops up next for any warnings and, if you are happy, proceed by clicking Finish.

Image showing the confirm and finish SSMS theme dialog.

Confirm and Finish.

Finally, you should get a confirmation that the import process has completed successfully (or not, but I haven’t seen an outright failure yet!):

Image showing a theme import complete dialog in SSMS.

SSMS Theme Import Complete.

You should, all being well, see an immediate update to the theming in the code window, as shown:

Image showing the SSMS smooth theme in action.

SSMS Smooth Theme.

That’s all there is to it! Again, if anyone has a good resource for themes please let me know, I’m always on the lookout for the cool and unusual.

As an added bonus, here are a few ‘live action’ shots from the bear cave as I was constructing this post:

Thanks for reading, catch you next time!

Coding Snippets in SSMS (Microsoft SQL Server)

Hi all,

Continuing on with the idea of coding snippets, here’s a follow up showing the (very similar) procedure for creating snippets in SQL Server Management Studio (SSMS).

As before, you’ll need a file with the ‘.snippet’ extension to begin, which contains a snippet in an XML format. A basic example is shown below for reference, encapsulating some code that can quickly search a Stored Procedure definition for a particular search term:

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<_locDefinition xmlns="urn:locstudio">
    <_locDefault _loc="locNone" />
    <_locTag _loc="locData">Title</_locTag>
    <_locTag _loc="locData">Description</_locTag>
    <_locTag _loc="locData">Author</_locTag>
    <_locTag _loc="locData">ToolTip</_locTag>
</_locDefinition>
    <CodeSnippet Format="1.0.0">
		<Header>
		<Title>Sys.procedures SQL Check</Title>
			<Shortcut></Shortcut>
		<Description>Creates SQL to check sys.procedures for a literal string.</Description>
		<Author>Lewis Grint</Author>
		<SnippetTypes>
			<SnippetType>Expansion</SnippetType>
		</SnippetTypes>
		</Header>
		<Snippet>
			<Declarations>
				<Literal>
					<ID>SearchTerm</ID>
					<ToolTip>The term to search for.</ToolTip>
					<Default>SearchTerm</Default>
				</Literal>
			</Declarations>
			<Code Language="SQL">
				<![CDATA[--Check SP content for a literal string
SELECT 
name
FROM sys.procedures 
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%$SearchTerm$%';
	]]>
			</Code>
		</Snippet>
    </CodeSnippet>
</CodeSnippets>

As you can see, this file takes the familiar ‘.snippet’ format, supporting Snippet, Code and Declarations elements. These include the code snippet itself, and placeholder definitions, as before. You will also find the Header element, where pertinent information about the snippet can be stored, such as author and description.

Easily recognisable territory so far, I’ll think you’ll agree.

Within SSMS, much as in Visual Studio, navigate to Tools > Code Snippets Manager (Ctrl + K, Ctrl + B) to get underway:

Image showing how to access the Code Snippets Manager.

Access Code Snippets Manager.

Again, pretty much plain sailing from here on in. You’ll have the same options as in Visual Studio to import a snippet file (just use the Import button):

Image showing the Code Snippets Manager before Import.

Code Snippets Manager before Import.

Navigate to your snippet file and select it. You’ll now have the opportunity to map the snippet to a nominated folder. In this instance, we’ll use the My Code Snippets folder. Click Finish to complete the process.

Image showing the MSSQL Server Code Snippets Manager Import Dialog.

SSMS Code Snippets Manager Import Dialog.

You should then be able to see your snippet under the nominated folder:

Image showing the Code Snippets Manager after Import.

Code Snippets Manager after Import.

That’s it, you’ve imported the snippet! You can now open a new query and use Ctrl + K, Ctrl + X to launch the Code Snippet Picker, whereby you can use the arrow keys to move between folder and snippets:

Image showing a Snippet before being expanded.

Snippet before Expansion.

Hit Enter to select your snippet; you should then see the following:

Image showing a Snippet after being expanded.

Snippet after Expansion.

Then you can start using your code, utilising tab to cycle through any placeholders. Another trick, that I’ve used, is to Add an entire folder of snippets using the Code Snippets Manager (instead of using Import). Using a ‘tactically’ named folder, you can gain faster access to your snippets:

Shows the results of adding a Custom Snippets Folder.

Adding a Custom Snippets Folder.

This type of snippet is an Expansion snippet. You can write other kinds, such as the Surrounds With snippet, an example of which can be found here on MSDN, illustrating a try/catch construct:

Surrounds With Snippet Example

I hope this has been helpful. Please comment below if you have any thoughts!

Cheerio!

T-SQL MERGE…Completely overlooked!

Good evening all,

Umm…how an earth did I miss this language feature? Where have I been since the release of SQL Server 2008? This really did just pass me by. I’m in the position whereby I don’t often run into this situation; where a T-SQL MERGE statement is directly applicable (i.e. table synchronisation), but still, I was a little embarrassed. Perhaps I have seen this at some point and it’s just been pushed out of my noodle at the expense of some other piece of information. So, red-faced (at least I am), let’s jump right in.

What is it?

In simple terms, this provides a compound way to perform an INSERT, UPDATE or DELETE on a target table, based on a source table. It’s as easy as that; we’ve negated the need for existence checks and other wrapping code/constructs, including the multiple INSERT, UPDATE and DELETE clauses of course. Performance is mentioned on the appropriate MSDN page. However, in my limited testing, I have not yet seen any major issues; just keep this thought on your mental radar anyway.

More information can be found here, so dive in and have a read (which details performance tips, so keep this in mind): T-SQL MERGE

MERGE Example

The following script rigs up some test data, in two example ‘target’ and ‘source’ tables, in an effort to demonstrate the synchronisations capabilities of the MERGE statement (apologies for the colour coding issues, I am using the correct language type but it seems to be taking exception to some of the content!).

/* 
	Very basic MERGE example
	-----------------------------

	Imagine these tables exist across databases, etc. and require synchronisation (or, as per my recent usage, I built up a derived table using CTE's to provide a source data table to compare against the target table to drive modifications)
	NOTE: Tables created for illustration only; not representative of how to structure this in practice of course
*/

-- TARGET TABLE
IF OBJECT_ID('dbo.TARGET_PERSON_TABLE') IS NULL
BEGIN

	CREATE TABLE dbo.TARGET_PERSON_TABLE
	(
		TARGET_PERSON_ID				INT		PRIMARY KEY
		, TARGET_FORENAME				NVARCHAR(255)
		, TARGET_SURNAME				NVARCHAR(255)
		, TARGET_AGE					INT			
	);

END;

-- SOURCE TABLE
IF OBJECT_ID('dbo.SOURCE_PERSON_TABLE') IS NULL
BEGIN

	CREATE TABLE dbo.SOURCE_PERSON_TABLE
	(
		SOURCE_PERSON_ID				INT		PRIMARY KEY
		, SOURCE_FORENAME				NVARCHAR(255)
		, SOURCE_SURNAME				NVARCHAR(255)
		, SOURCE_AGE					INT			
	);

END;

--Arrange some test data into each table (target and source, for illustration)
INSERT INTO dbo.TARGET_PERSON_TABLE
(
	TARGET_PERSON_ID
	, TARGET_FORENAME
	, TARGET_SURNAME
	, TARGET_AGE
)
VALUES
(
	1
	, 'Dave'
	, 'Jones'
	, 32
)
,
(
	2
	, 'Moira'
	, 'Stevens'
	, 27
)
,
(
	3
	, 'Larry'
	, 'Bodsworth'
	, 48
);

INSERT INTO dbo.SOURCE_PERSON_TABLE
(
	SOURCE_PERSON_ID
	, SOURCE_FORENAME
	, SOURCE_SURNAME
	, SOURCE_AGE
)
VALUES
(
	2
	, 'Mandy'
	, 'Stevens'
	, 32
)
,
(
	3
	, 'Larry'
	, 'Rodsworth'
	, 50
)
,
(
	4
	, 'Sandy'
	, 'Ennis'
	, 29
)
,
(
	5
	, 'Wendy'
	, 'Wainwright'
	, 40
);

-- Inspect the target/source table data prior to the MERGE operation
SELECT 
tpt.TARGET_PERSON_ID
, tpt.TARGET_FORENAME
, tpt.TARGET_SURNAME
, tpt.TARGET_AGE
FROM dbo.TARGET_PERSON_TABLE tpt;

SELECT
spt.SOURCE_PERSON_ID
, spt.SOURCE_FORENAME
, spt.SOURCE_SURNAME
, spt.SOURCE_AGE
FROM dbo.SOURCE_PERSON_TABLE spt;

-- Synchronise the target table with the source table, performing matching and INSERT, UPDATE and DELETE operations as required
MERGE dbo.TARGET_PERSON_TABLE tpt
USING SOURCE_PERSON_TABLE spt ON tpt.TARGET_PERSON_ID = spt.SOURCE_PERSON_ID 
	-- If a row is 'matched' (based on the above 'ON' statement) then simply update the target Person
	WHEN MATCHED 
	THEN UPDATE 
		SET 
		tpt.TARGET_FORENAME = spt.SOURCE_FORENAME
		, tpt.TARGET_SURNAME = spt.SOURCE_SURNAME
		, tpt.TARGET_AGE = spt.SOURCE_AGE
	-- If a row is 'not matched' (based on the above 'ON' statement) then do an insert of a new Person into the target table, based on the source table
	WHEN NOT MATCHED
		THEN INSERT
		(
			TARGET_PERSON_ID
			, TARGET_FORENAME
			, TARGET_SURNAME
			, TARGET_AGE
		)
		VALUES
		(
			SOURCE_PERSON_ID
			, SOURCE_FORENAME
			, SOURCE_SURNAME
			, SOURCE_AGE
		)
	-- Lastly, if a the target table contains a row not matched by the source table then remove the target table row entirely
	WHEN NOT MATCHED BY SOURCE 
		THEN DELETE;

/*
	Inspect the target/source table data post MERGE operation (values should be synchronised between correctly between the two tables)

	Expected Results
	----------------------
	1) Person ID 1, Dave Jones, should be removed from the target table (DELETE)
	2) Person ID 2, Forename of 'Moira' should be updated to 'Mandy' and Age should be updated from 27 to 32 (UPDATE)
	3) Person ID 3, Surname of 'Bodsworth' should be updated to 'Rodsworth' and Age should be updated from 48 to 50 (UPDATE)
	4) Person ID 4, Sandy Ennis, should be added to the target table (INSERT)
	5) Person ID 5, Wendy Wainwright, should be added to the target table (INSERT)

*/

SELECT 
tpt.TARGET_PERSON_ID
, tpt.TARGET_FORENAME
, tpt.TARGET_SURNAME
, tpt.TARGET_AGE
FROM dbo.TARGET_PERSON_TABLE tpt;

SELECT
spt.SOURCE_PERSON_ID
, spt.SOURCE_FORENAME
, spt.SOURCE_SURNAME
, spt.SOURCE_AGE
FROM dbo.SOURCE_PERSON_TABLE spt;

-- Clear down the tables post operation, for ease of re-running and re-testing
TRUNCATE TABLE dbo.TARGET_PERSON_TABLE;
TRUNCATE TABLE dbo.SOURCE_PERSON_TABLE;

Here is the state of the target and source tables prior to the MERGE statement being executed:

Illustrates the target and source tables state after the merge process

Target and Source Tables Post Merge.

After execution, you can see the effects of the synchronisation between the target and source tables:

Illustrates the target and source tables state before the merge process

Target and Source Tables Prior to Merging.

If this was useful to you then that’s superb…my guess is that a fair few of you will be shaking your heads wondering which hole in the sand I’ve had my head planted in for the last few years. Well, I call it how I see it and I’m pretty sure I missed the memo, so for you others out there that may have missed this, enjoy!

I’m still playing around with a F#/FitBit related piece at the moment, and I really want it to be a good quality post, so hang on in there as it’s coming soon.

Cheery bye!

Developer Testing Hints and Tips

Howdy happy campers.

I want to discuss a piece, somewhat divergent from the topic of physical coding, although still a facet of development that is close to my heart (and easy to overlook in many respects when constantly mashing keys and churning out code); developer testing. More specifically, I want to provide a set of guidelines that ‘may’ (insert disclaimer) help with the process and provide some food for thought.

This is in no way a definitive guide or best practice for that matter; more just a personal take on what I find works for me and the guts of a generally beneficial ‘templated’ approach to follow.

I would love to invite discussion on this one (or just get a take on what works for you), so please do hit me up on twitter or add a comment below, I’d love to hear from you.

My Process

As with any process, ground work and preparation can be vital for achieving a good result. To this end, I invariably start my developer testing on a given work item with a template document that looks like this:

Illustration of how to structure you Developer Testing.

Developer Testing Helper Document Structure.

What goes into your document will largely depend on what technologies you are using of course. For instance, you may never have a database centric element to the development you perform, rendering the ‘Database Upgrade’ section null and void ‘in all cases’. Ultimately, add and remove sections as you see fit but do strive for consistency. I myself test a mixture of items that may or may not include T-SQL elements. However, I choose to include the ‘Database Upgrade’ section in this case on every occasion, preferring to note that ‘there were no T-SQL’ related parts to the item, even just to mark it as ‘N/A’ (for my own sanity and for easy recollection later down the line, without the need to scan a lengthy list of changes elsewhere in the notes). Basically, my OCD kicks in and I start to wonder why I haven’t included a section that I ‘always’ include, leading to paranoia that I’ve missed something!

Each section (other than Notes), which is probably self-explanatory, can result in a PASS, QUERY or PASS-BACK state. Section state obviously knocks on and influences the result recorded against the ‘Developer Testing Summary’ header. PASS denotes an ‘A-Okay’ state, good to rock and roll! QUERY gives you the opportunity to mark a section with ‘discussion points’ or things you would like to check, without necessarily marking it off as incorrect (I tend to do this a lot, as I love to talk!). PASS-BACK is used in the circumstance whereby an error can be replicated/reproduced consistently or a logic problem definitely flies in the face of the ‘Acceptance Criteria’ for the story. In the circumstances whereby things such as coding standards have been contradicted I tend to use a mixture of QUERY/PASS-BACK, depending on the notes the developer has provided (it could be a flat PASS, of course, as there are always occasions where the rules need to be broken!).

So, section by section, let’s go over what we have and why…

Notes

It’s incredibly tempting to start diving into code, comparing files, trying to make sense of what the hell is going on but…I may get in trouble here, I’m going to tell you to stop right here. It’s so easy, and I’ve done it (probably) hundreds of times, to get eye deep in code, wasting large pots of time, before the basic question of ‘what are we doing and why’ has been answered. This is where this section comes in.

Use this area of your notes to compile a few short paragraphs (or bullet points, whatever you prefer) on the following:

  • Read over the developers notes and, after discovering if any changes have occurred to the underlying requirements for the story, start to create…
  • Your own summary of the ‘Acceptance Criteria’ for this particular story (or item, whatever term floats your boat. I’m going to use both interchangeably to alleviate bombarding you with the same term too much!).
  • Then, list any other pertinent information surrounding how the developer has coded the item (e.g. decisions that have shaped how the story has turned out). For example, did they place code into a different service than was originally expected because of ‘x’ reason, or did some logic end up in a different layer in the technology stack than conceived originally.
  • Lastly, note any of your initial thoughts, concerns or things you intend to check/look for based on this initial scoop of information.

The core reason I do this is to try to solidify my expectations, begin thinking about a test plan (yes, I like to always perform (rudimentary at the bare minimum) application testing, this isn’t just down to QA in my mind!) and to try to mitigate the chances of any massive surprises. Surprises, although they will always eventually happen one way or another, will lead to more confusion and increase the chances of things slipping through the net. You’ll be able to, by just following this exercise or a similar routine, cross-reference your expectations with the code changes you see and more easily be able to pick up errors, incorrect logic or unrequired alterations. This will limit the chances that something will slip past your mental filter as an ‘I guess that’s correct’ or ‘perhaps that class needed to be changed also, ok’ moment (don’t lie, we’ve all had them 😉 !).

Cool, we’ve formed in our own minds what this item is for, how it’s been developed and what, as a baseline, we are expecting to see. Let’s test (and along the way, discuss a few more tactics).

Database Upgrade

Some of what I’ll discuss here is formed around how my personal development role operates, so feel free to modify this approach to your needs. Again, if you don’t deal in the realm of database development at all pass go and collect £200, you’ve bypassed this step; congratulations!

The essence of this section surrounds you being able to state that new Stored Procedures, Functions, Views, Triggers, etc. can be ‘created’ without error on a database in a suitable ‘versioned’ state. Also, can ad-hoc data scripts, that are part of the development item, be run without error?

Some other considerations…

  • Are object creation scripts/ad-hoc scripts expected to be re-runnable? If yes, then specifically test and note this down here.
  • If you are in an environment whereby this kind of testing needs to be performed on multiple databases then mark this down here also (splitting notes down into sections against each target database/environment, whatever is applicable).
  • We work with a ‘versioned’ database so I make an effort to state which version I am on at the start of the testing run for reference.

An example of what this section may look like is illustrated below for reference:

Illustration of how to structure the Database Upgrade Developer Testing Document Section.

Developer Testing Database Upgrade Section Example.

A QUERY/PASS-BACK at this stage will bubble up and alter the status listed for the entire developer testing process. An additional note here; depending on how many queries/issues you find (and the length of the testing notes in general), you may want to copy the core query/error text to the top of the notes for easy review by the developer later (this applies to all of the following sections in fact).

Code Review

Moving on to the main filling of your developer testing sandwich, the actual code review! Obviously, you’ll be reviewing files here and looking at scripts, new files or amended code but definitely take a second or two out (unless your setup has automated builds/continuous integration, or some other clever solution, to tell you this) to make sure the code compiles before proceeding (and make the relevant note). A simple step but one easily forgotten, meaning you can get to the end of a code review before realising parts of the code don’t compile, eek!

I tend to, from a structural and sanity point of view (clarity is key), split my testing notes here into sections based on technology (i.e. T-SQL, C#, JavaScript, etc), or, at least, make some effort to order up a single list of files by file type. I tend to, for C# changes, group code files by the related project (given that projects should represent a logical grouping of types, hence allowing you to dice up changes by functional area, i.e. common extensions, data access helpers, etc.).

The point that you should take away from this, however, is that a little bit of thought and structuring at this phase will make your life easier; especially as a number of code files rack up.

If you’re looking for a small sample on how this section could look, after being fleshed out, then here you go:

Illustration of how to structure the Code Review Developer Testing Document Section.

Developer Testing Code Review Section Example.

However, what about the code review procedure itself I hear you cry! What follows next shouldn’t be taken as an exhaustive list, or correct in every given situation for that matter; more just suggestions as to what I’ve found helpful over time (mental kit bag):

  • For C# (and other object-orientated languages that support this concept), ensure that null values are correctly handled. Whether this is by capturing nulls on call to a given method and throwing an ArgumentNullException, or by doing a ‘not equal to null’ check (!= null) around code that would otherwise fail.
  • Strings can be tricky buggers, especially in case-sensitive environments! In most cases, comparisons should be performed taking case-sensitivity out of the equation (another case-by-case situation of course). I’d keep an eye out, again for C#, for the correct use of String.ToUpperInvariant, String.ToLowerInvariant and String.Equals. For String.Equals, use an overload containing a StringComparison enumeration type, for case/culture-insensitive options.
  • Keep an eye out for instances of checks being performed against strings being null or an empty string (either one or the other only). This can quickly lead to chaos, switch out for a null, empty or whitespace check (e.g. String.IsNullOrWhiteSpace).
  • Empty try/catch handlers are evil. Kill any you find.
  • Check up for instances whereby a class consists of all static members, but the class is not marked as static.
  • Train the eye to look for casting operations; you’ll always catch a few where the casting operation ‘could’ throw exceptions and should, therefore, be subject to more careful handling.
  • Big bugbear in the realm of coding; if a method requires scrolling to get through it’s a significant indication right off the bat that it is a prime candidate for refactoring. Unless there is a good reason, or it is clearly performing one logical function, consider having a conversation about breaking the method down.
  • Look for missed opportunities to rational code using inheritance. The most common one I see (and forget myself) is the abstraction of code to base classes and then using virtual methods/overrides in subclasses. Hawk-eye for types that should be abstract.
  • A simple one, but something that could easily slap you in the face if you’re not careful. When ‘language switching’, in a DT sense, take a second to make a mental note that you should be changing mind-sets (i.e. syntax is changing, get your game-face on!). For example, you stare into the abyss of C# for long enough (seeing ‘!= null’) you may, on switching to T-SQL, not notice a ‘!= NULL’ that should have been an ‘IS NOT NULL’. Those trees can be damn hard to find in the woods, after all!
  • Watch out for expensive operations, whereby values should be obtained once, ideally, then cached. It can be easy to let code skip by that repeatedly calls a database, for instance, to the detriment of performance (or possible errors, depending on the nature of the functionality called).
  • I love, love, loooovvvveeeee comments! Probably (ok, to the levels of being a little OCD about it!) too much. As far as C# code goes, I prefer (but don’t fail on this basis alone) XML Comments for C# and like to see comments on bulkier pieces of T-SQL. If there is a sizeable piece of code, whereby its function stretches beyond ‘trivial’, I like to see at least a short statement stating intent (what the developer is expecting the code to do is key by the way…as discussed next).
  • Where you have comments, link the intent in these comments back to what the code is actually doing; then trail it back to the items ‘Acceptance Criteria’ where appropriate. I have been rescued (as a developer, submitting my work for DT) countless times by those performing DT on my code, just by someone relaying to me that ‘what I thought my code was doing’ (based on my comments) doesn’t tie up to the actual functionality being offered up. This has led to me, of course, face-palming myself but being relieved that the gap in my intent, when checked off against my actual code, had been picked up by somebody in time to catch it before QA (or deployment, gulp!). State intent, then reap the rewards when mistakes you make are more rapidly picked up for rectification.
  • Be sure to look for the use of language constructs/keywords or syntactic-sugar that is not permissible on your baseline, minimum supported environment (i.e. older versions of SQL Server or .NET), if what you work on has this concept of course. This is sure to be something that will get picked up by QA causing bounce backs, or by your consumers later on if you’re not careful!
  • Keep a look out for code that could (or should) be shared or has been placed in a project/location that does not make logical sense. At a bare minimum, picking up on this sooner rather than later will keep your code base tidier, allow for ample opportunities to put great code in places to be leveraged as much as possible. In other cases, asking these kinds of questions can expose flaws and issues with the way a solution has been architected, which occasionally will steer you clear of tight spots later down the line.
  • Where shared code has been changed, look for instances whereby other applications/areas of the code base could be broken as a result of the changes. Recompile code to check for this as required. I had a bite on the bum by this recently :-?.
  • Keep up to date with any coding standards documents that should be adhered to and make sure the guidelines are followed (within reason of course; you’ll always find a scenario whereby a rule can, and should, be broken).
  • Really do consider writing and using Unit Tests wherever possible. They are a useful facet in the grand scheme of things (I believe at least) and they do carry weight when pitched up against visually checking code and application testing in general.
  • Last little nuggets, which I see from time to time. Look for objects constantly being created inside loops, heavy amounts of string concatenation not using the correct constructs (e.g. a StringBuilder in C#) or missed opportunities to create sub Stored Procedures in T-SQL (sectioning off code to gain performance boosts and obtain better execution plans). In fact, for T-SQL it can be a useful exercise to check the performance of non-trivial pieces of code yourself by changing how it’s structured, whilst obtaining the same results of course. You may or may not be able to increase performance along the way, but you’ll have far better comprehension of the code by the end regardless.

Hopefully, this little snapshot from my bag o’ tricks is enough to get you started, or get the brain-juices flowing. Let me know what you think of these suggestions anyway; I’d really appreciate the opportunity to collate others general thoughts and get a collective consensus going.

Application Testing

Here is where I will defer the giving of advice to my beloved QA counterparts on this beautiful planet; this, of course, isn’t my area of expertise. My only opinion here is (developers will possibly hate me for stating it) that developers ‘should’ always perform application testing alongside a code review. In fact, I’m a keen advocate for developers being involved in performing QA on the odd occasion. I personally like doing this, provided I have a trusty QA on hand to assist me (thankfully, I work with the best one around ;-), so no worries there). The simple reasons for this are:

  • One way or the other, acquisition of Product Knowledge is going to be invaluable to you. It’s just as valuable to start using your products in anger as it is to analyse code for hours on end. The side-note here is that this is part of your overall ‘worth’ as a developer, so don’t neglect it.
  • At this stage, you get to think as the customer might. Ideas and thoughts you have at this stage, which direct more development or changes to the product, will be amongst some of the best (and most rewarding when it comes to getting that warm and fuzzy feeling!).
  • Urm…it’s embarrassing to say ‘oh yeah, that codes great, thumbs up!’ for it then to explode in someone else’s face on the first press of a button! Easily avoided by following the process through from end to end, no matter what.

Ok, I’ll have a go at channelling one QA thought. Ok, I got it, here’s one from a mysterious and wise QA guru:

Mysterious and wise guru here… a friendly reminder to developers…never, ever, test your items using only one record! The reason? Well, I’ll test it with more than one record and break it instantly!

If anyone doing QA reads this feel free to feed us your arcane knowledge…God knows we need it! I would advise you keep the original item requirements in mind here of course, whilst testing; securing any process variants in your thoughts that could potentially throw carefully laid plans to waste (e.g. what if we go back and forth from screens x and y between completing process z, or we save the same form information twice, etc.). Your knowledge of the code can help at this stage so use the opportunity whilst you have it.

Before I forgot, an example of this could look like this:

Illustration of how to structure the Application Testing Developer Testing Document Section.

Developer Testing Application Testing Section Example.

Code Review/Application Testing – The Most Important Point…

Do it!!! If you’re not sure (as I am still on a regular basis) then ask the question and run the risk of looking like an idiot! Be a spanner, who cares at the end of the day. I dread to think of how many developers have stared at code and, ultimately, let stuff slide because they refused to pipe up and just say they weren’t sure or ‘didn’t get it’. At the end of the day, it’s better to ask questions and if there turns out to be no issues, or it’s a simple misunderstanding, then no harm, no foul. On a good number of occasions I query things to later realise that I missed a line of code meaning it does work as intended, or there’s some process that had slipped my mind…it hasn’t got me sacked (ahem, yet!). So my advice is just to open up and have a natter at the end of day, it’ll be worth the ratio of ‘idiot’ to ‘bug-saving’ moments, trust me :-).

Admin

As with any process, there will always be (and if there isn’t for you then let me know where you work because it’s awesome!) a certain amount of ‘red tape’. Use this last section to keep track of whether any procedural bits and bobs have been handled. For example, I’m expected to cover the creation of a Release Note (as part of the practices I follow) for any item I work on, so it should be marked down in this section as to whether I’ve completed it or not. It could end up just being a very simple section, like the following:

Illustration of how to structure the Admin Developer Testing Document Section.

Developer Testing Admin Section Example.

I hope this has been helpful and informative; or, at least, got the mind going to start thinking about this process. Again, as mentioned above, I would love to hear your thoughts so please do get in touch either here or via social media.

Cheers all, keep smacking keys and producing coding loveliness in the meantime 🙂

A Little FOR XML PATH Nugget

A very small post this one, covering a little nugget that I’d almost forgot until it came up trumps again this week; TSQL FOR XML PATH can be a nice solution for concatenation of strings across rows (in a given column).

It’s fairly common to have the need to concatenate column based data, as the following example illustrates:

--Standard concatenation of column values (comma separated, produces multiple rows)
SELECT 
FORENAME 
+ ', ' 
+ SURNAME AS [FULL_NAME]
FROM dbo.tblTEST t
WHERE t.ID < 4;

However, don’t forget that working through and concatenating row based data, in a particular column, can be achieved simply using the FOR XML PATH construct, just like this:

--Concatenation of row values for a particular column (imagine we wanted comma separated forenames for example) - Provides a single column as structured
SELECT
CONVERT                                     --Conversion required to an NVARCHAR(VALUE) (MAX depending on string size) - The result will be XML when using FOR XML PATH initially
(
    NVARCHAR(100)
    ,
    (
        SELECT
        t.FORENAME + 
        CASE
            WHEN t.ID < 3                    --Don't add a comma after the last value (just for illustration purposes)
            THEN ', '
            ELSE ''
        END
        FROM dbo.tblTEST t
        WHERE t.ID < 4
        FOR XML PATH('')					 --Specify FOR XML PATH using an empty string (we don't want a wrapping element when concatenating strings)
    )
) AS [COMMA_SEPARATED_FORENAMES];

Interesting use of FOR XML PATH that’s well worth keeping in mind, it can come in dead handy. Apologies for the Short and sweet post; it’s the order of the day! I’ve managed to pick up the dreaded lurgy so I’m dosed up on medication and drinking a tonne of coffee! Here’s hoping that this post makes sense when I read it later on.

Until the next time, bye for now!

SQL – More Expansive Object Searching

Evening all,

I thought this would be worth sharing, in its current form. It’s a small piece of SQL (this would be encapsulated in a Stored Procedure) that allows you to search through a host of objects encompassing Stored Procedures, Functions, Views, Triggers and even Indexes for a specific search term (i.e. an object name).

I’ve utilised a CTE here (just for readability more than anything; a small sampling of actual Execution Plans suggest this isn’t hurting performance to structure the code in this way) to pull through the full object definition, for any matches, where types easily support it. Indexes are the tricky ones to deal with here; from what I’ve gathered the definition would need to be manually constructed; hence, I’ve omitted generating a definition for now.

This is essentially a bit of an extension of some rougher and readier code put together in my SQL Debugging Tips – Part Two post:

The code is as follows (would be appropriately wrapped in a Stored Procedure):

SET NOCOUNT ON;

--1) Validation - Ensure that a valid Search Term has been supplied (cannot be NULL or an empty string)
IF (COALESCE(@SEARCH_TERM, '') = '')
BEGIN
	 
	  RAISERROR('@SEARCH_TERM cannot be an empty string or NULL. Please specify a valid value (procedure: YOUR_PROCEDURE_NAME).', 16, 1);
	  RETURN;       --Input invalid, simply return

END;

--Enable a 'fuzzy' match
SET
@SEARCH_TERM = '%' + @SEARCH_TERM + '%';

--CTE that is used to 'bundle' up all of the various objects being search (for the given Search Term)
WITH COMBINED_OBJECTS
(
	  OBJ_ID
	  , OBJ_NAME
	  , OBJ_TYPE
	  , OBJ_DEFINITION
)
AS
(
	  --Search Procedures, Functions, Triggers and Views for the Search Term (in the actual definition of the object)
	  SELECT
	  sm.object_id AS [OBJ_ID]
	  , so.name AS [OBJ_NAME]
	  , so.type_desc AS [OBJ_TYPE]
	  , sm.definition AS [OBJ_DEFINITION]
	  FROM sys.sql_modules sm
			 INNER JOIN sys.objects so ON sm.object_id = so.object_id
	  WHERE sm.definition LIKE @SEARCH_TERM
	  UNION ALL
	  --Search for the Search Term in the name of an index
	  SELECT
	  si.object_id AS [OBJ_ID]
	  , si.name AS [OBJ_NAME]
	  , 'INDEX - ' + si.type_desc COLLATE DATABASE_DEFAULT AS [OBJ_TYPE]		                                                         --Negate collation issues with concatenation														
	  , NULL AS [OBJ_DEFINITION]
	  FROM sys.indexes si
	  WHERE si.name LIKE @SEARCH_TERM
	  UNION ALL
	  --Search for the Search Term in the physical column names that comprise an index definition
	  SELECT
	  si.object_id AS [OBJ_ID]
	  , sc.name + ' (' + si.name COLLATE DATABASE_DEFAULT + ' - ' + si.type_desc COLLATE DATABASE_DEFAULT + ')' AS [OBJ_NAME]            --Negate collation issues with concatenation
	  , 'INDEX_COLUMN' AS [OBJ_TYPE]
	  , NULL AS [OBJ_DEFINITION]
	  FROM sys.indexes si
			 INNER JOIN sys.index_columns sic ON
			 (
				   si.object_id = sic.object_id
				   AND si.index_id = sic.index_id
			 )
			 INNER JOIN sys.columns sc ON
			 (
				   sic.object_id = sc.object_id
				   AND sic.column_id = sc.column_id
			 )
	  WHERE sc.name LIKE @SEARCH_TERM
)
--Return the results to the caller (can be expanded as needed)
SELECT
co.OBJ_ID
, co.OBJ_NAME
, co.OBJ_TYPE
, co.OBJ_DEFINITION
FROM COMBINED_OBJECTS co
ORDER BY co.OBJ_TYPE, co.OBJ_NAME;      --Do a little bit of ordering to make the results easier to digest                                                                                                                                                 

With Indexes, I’ve allowed the ability for a developer to search within the name of the Index and search for hits linked to the columns which comprise the Index.

This is all based on a ‘fuzzy’ search (aka using Wild Cards) – @SEARCH_TERM would be an input parameter of the Stored Procedure of type NVARCHAR. A slight spin on a basic check on sys.procedures and a little more expansive.

Just a small post to get this off my chest.

Cheers!