Execution Plans via T-SQL – Super Simple

I recently had to do a very quick ‘rip and tear’ (high fives for the ‘Doom’ reference!) of stored execution plans for a bit of exploratory work I was involved with. It seems like a simple, shareable piece so here it is for you to grab, use and modify as and when you need it.

The backbone of this query relies on the following System Dynamic Management Views:

  1. sys.dm_exec_cached_plans – returns a row for each cached query plan. This is used to infer the object type linked to the cached plan along with details such as the amount of times this plan has been reused.
  2. sys.dm_exec_query_plan – the primary function of this view is to obtain the XML query plan content.
  3. sys.dm_exec_sql_text – used here to get the physical SQL tied to the cached execution plan.
  4. sys.dm_exec_query_stats – acts as a gateway to performance metrics for a given query plan.

You’ll notice that some of the views are configured as table-valued functions that consume a plan handle, the common examples use a CROSS APPLY to link entities and I’ve stuck with this approach. Other than that, the physical SQL is fairly straightforward; some inline comments have been added for clarity:

SELECT
COALESCE(OBJECT_NAME(st.objectid, st.[dbid]), '[No Object]') AS [ObjectName]
, cp.objtype AS [ObjectType]
, cp.usecounts AS [TimesExecuted]
, DB_NAME(qp.[dbid]) AS [DatabaseName]
, cp.size_in_bytes AS [SizeInBytes]
, cp.cacheobjtype AS [CacheObjectType]
, st.[text] AS [PhysicalQuery]
, qp.query_plan AS [QueryPlanXml]
, qs.last_execution_time AS [LastRan]
, qs.last_elapsed_time AS [LastRunElapsedTime]
FROM sys.dm_exec_cached_plans cp
	CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
	CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
	-- Extra join added for optional execution stats (like the last 'run' time and execution times)
	LEFT OUTER JOIN sys.dm_exec_query_stats qs ON cp.plan_handle = qs.plan_handle
-- Omit this is you want results for all databases contained on an instance
WHERE DB_NAME(qp.[dbid]) = 'JsonForTestDatabase'
-- Place the most 'used' first within the results set
ORDER BY cp.usecounts DESC;

I’ve gathered a small subset of the returnable columns to show the object name, type, query text (along with the XML query plan) as well as some simple usage information. I’m filtering the returned data by database name and placing the most used query plans first in the returned results; feel free to modify as needed.

You should retrieve output in the following vein:

Querying Cached Plans.

Querying Cached Plans.

Simply clicking content in the QueryPlanXml column will display the execution plan in the usual, expected format:

Execution Plan Content.

Execution Plan Content.

All pretty easy to get to grips with. Happy coding!

SQL FOR JSON Tour

I’ve worked on numerous occasions with the various flavours of the FOR XML clause within SQL Server. Taking that a step further, this seemed like as good a time as ever to check out how to work with the FOR JSON clause.

This clause can, simply put, be used to boil down data retrieved via a SELECT statement into formatted JSON, with a custom structure as required – simples. So, how do we go about this?

To illustrate how this all strings together I’ve scripted together a couple of examples that you can lift and test yourself, as desired. Coming up with examples can be the hardest thing sometimes; in an effort to avoid falling into the classic ‘Person’ table example (Car for C# and so on and so forth ๐Ÿ˜‰ !) I’ve gone with two ‘super imaginative’ (well, maybe not; but give me some credit here!) fictitious relational data setups! Firstly, I use the concept of ‘Robot’ and ‘RobotDanceMoves’ for a look at one-to-one relationships (which gets extended a little later one). Next up, I use the idea of ‘Houses’ to representing a more complex example. Houses have ‘Rooms’ which in turn contain ‘RoomObjects’, this gives us some opportunities to explore one-to-many relationships and how to structure the SQL to get the desired JSON output.

Our test table structures look like this:

Robot Table Structure.

Robot Table Structure.

House Table Structure.

House Table Structure.

We’ll start with utilising the Robot and RobotDanceMoves tables and then use the other tables in the last couple of examples to show a more complex, nested setup.

To get us out of the gate, our first piece of SQL uses FOR JSON PATH in its simplest form, we’re enforcing a one-to-one relationship via the INNER JOIN clause by specifying ‘FavouriteMove = 1’.

SQL:

-- Produce a very simple, flat representation of a Robot and their favourite dance move
SELECT 
r.Id
, r.[Name]
, r.Active
, rdm.[Name] AS [FavouriteDanceMove]
FROM dbo.Robots r
	INNER JOIN dbo.RobotDanceMoves rdm ON 
	(
		r.Id = rdm.RobotId
		AND rdm.FavouriteMove = 1
	)
FOR JSON PATH;

JSON output:

[
    {
        "Id": 1,
        "Name": "Barry",
        "Active": true,
        "FavouriteDanceMove": "Moonwalk"
    },
    {
        "Id": 2,
        "Name": "Steve",
        "Active": false,
        "FavouriteDanceMove": "Thunder Clap"
    },
    {
        "Id": 3,
        "Name": "Dave",
        "Active": true,
        "FavouriteDanceMove": "Moonwalk"
    },
    {
        "Id": 4,
        "Name": "Zoe",
        "Active": true,
        "FavouriteDanceMove": "The Robot"
    },
    {
        "Id": 5,
        "Name": "Claire",
        "Active": true,
        "FavouriteDanceMove": "The Robot"
    },
    {
        "Id": 6,
        "Name": "Tracey",
        "Active": false,
        "FavouriteDanceMove": "Moonwalk"
    }
]

It is possible to compartmentalise properties further by using column aliases directly to create a key and nested object. Here the ‘Name’ and the ‘Active’ flag for a Robot is nested within ‘RobotCoreDetails’ and the ‘Name’ of a Robots favourite dance move is nested within ‘RobotDanceMove’ as ‘FavouriteDanceMove’.

SQL:

-- Illustration of how the JSON can be manipulated and formatted further by altering column aliases
SELECT 
r.Id AS [Id]
, r.[Name] AS [RobotCoreDetails.Name]
, r.Active AS [RobotCoreDetails.Active]
, rdm.[Name] AS [RobotDanceMove.FavouriteDanceMove]
FROM dbo.Robots r
	INNER JOIN dbo.RobotDanceMoves rdm ON 
	(
		r.Id = rdm.RobotId
		AND rdm.FavouriteMove = 1
	)
FOR JSON PATH;

JSON output:

[
    {
        "Id": 1,
        "RobotCoreDetails": {
            "Name": "Barry",
            "Active": true
        },
        "RobotDanceMove": {
            "FavouriteDanceMove": "Moonwalk"
        }
    },
    {
        "Id": 2,
        "RobotCoreDetails": {
            "Name": "Steve",
            "Active": false
        },
        "RobotDanceMove": {
            "FavouriteDanceMove": "Thunder Clap"
        }
    },
    {
        "Id": 3,
        "RobotCoreDetails": {
            "Name": "Dave",
            "Active": true
        },
        "RobotDanceMove": {
            "FavouriteDanceMove": "Moonwalk"
        }
    },
    {
        "Id": 4,
        "RobotCoreDetails": {
            "Name": "Zoe",
            "Active": true
        },
        "RobotDanceMove": {
            "FavouriteDanceMove": "The Robot"
        }
    },
    {
        "Id": 5,
        "RobotCoreDetails": {
            "Name": "Claire",
            "Active": true
        },
        "RobotDanceMove": {
            "FavouriteDanceMove": "The Robot"
        }
    },
    {
        "Id": 6,
        "RobotCoreDetails": {
            "Name": "Tracey",
            "Active": false
        },
        "RobotDanceMove": {
            "FavouriteDanceMove": "Moonwalk"
        }
    }
]

As per the FOR XML clause, it is possible to apply a ‘ROOT’ clause. Here, this is used as a wrapper to Robot objects.

SQL:

-- Illustration of adding a ROOT clause to wrap the produced JSON content
SELECT 
r.Id AS [Id]
, r.[Name] AS [RobotCoreDetails.Name]
, r.Active AS [RobotCoreDetails.Active]
, rdm.[Name] AS [RobotDanceMove.FavouriteDanceMove]
FROM dbo.Robots r
	INNER JOIN dbo.RobotDanceMoves rdm ON 
	(
		r.Id = rdm.RobotId
		AND rdm.FavouriteMove = 1
	)
FOR JSON PATH, ROOT('Robots');

JSON output:

{
    "Robots": [
        {
            "Id": 1,
            "RobotCoreDetails": {
                "Name": "Barry",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Moonwalk"
            }
        },
        {
            "Id": 2,
            "RobotCoreDetails": {
                "Name": "Steve",
                "Active": false
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Thunder Clap"
            }
        },
        {
            "Id": 3,
            "RobotCoreDetails": {
                "Name": "Dave",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Moonwalk"
            }
        },
        {
            "Id": 4,
            "RobotCoreDetails": {
                "Name": "Zoe",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "The Robot"
            }
        },
        {
            "Id": 5,
            "RobotCoreDetails": {
                "Name": "Claire",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "The Robot"
            }
        },
        {
            "Id": 6,
            "RobotCoreDetails": {
                "Name": "Tracey",
                "Active": false
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Moonwalk"
            }
        }
    ]
}

In this next example, we are moving forward into a one-to-many relationship by removing ‘FavouriteMove = 1’ from the INNER JOIN (I’ve left the alias to say ‘FavouriteDanceMove’ at the moment, but this gets tidied up in a subsequent example). You may be tempted to structure the SQL in this way when getting started but the outcome, as shown below, results in a duplication of the Robot object – not a singular Robot supporting multiple dance moves.

SQL:

-- Represents the incorrect way to produce correctly nested results, on this occassion
SELECT 
r.Id AS [Id]
, r.[Name] AS [RobotCoreDetails.Name]
, r.Active AS [RobotCoreDetails.Active]
, rdm.[Name] AS [RobotDanceMove.FavouriteDanceMove]
FROM dbo.Robots r
	INNER JOIN dbo.RobotDanceMoves rdm ON r.Id = rdm.RobotId
FOR JSON PATH, ROOT('Robots');

JSON output:

{
    "Robots": [
        {
            "Id": 1,
            "RobotCoreDetails": {
                "Name": "Barry",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Moonwalk"
            }
        },
        {
            "Id": 1,
            "RobotCoreDetails": {
                "Name": "Barry",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "The Robot"
            }
        },
        {
            "Id": 1,
            "RobotCoreDetails": {
                "Name": "Barry",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Thunder Clap"
            }
        },
        {
            "Id": 2,
            "RobotCoreDetails": {
                "Name": "Steve",
                "Active": false
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "The Robot"
            }
        },
        {
            "Id": 2,
            "RobotCoreDetails": {
                "Name": "Steve",
                "Active": false
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Moonwalk"
            }
        },
        {
            "Id": 2,
            "RobotCoreDetails": {
                "Name": "Steve",
                "Active": false
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Thunder Clap"
            }
        },
        {
            "Id": 3,
            "RobotCoreDetails": {
                "Name": "Dave",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Moonwalk"
            }
        },
        {
            "Id": 3,
            "RobotCoreDetails": {
                "Name": "Dave",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "The Robot"
            }
        },
        {
            "Id": 3,
            "RobotCoreDetails": {
                "Name": "Dave",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Thunder Clap"
            }
        },
        {
            "Id": 4,
            "RobotCoreDetails": {
                "Name": "Zoe",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "The Robot"
            }
        },
        {
            "Id": 4,
            "RobotCoreDetails": {
                "Name": "Zoe",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Moonwalk"
            }
        },
        {
            "Id": 4,
            "RobotCoreDetails": {
                "Name": "Zoe",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Thunder Clap"
            }
        },
        {
            "Id": 5,
            "RobotCoreDetails": {
                "Name": "Claire",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Thunder Clap"
            }
        },
        {
            "Id": 5,
            "RobotCoreDetails": {
                "Name": "Claire",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Moonwalk"
            }
        },
        {
            "Id": 5,
            "RobotCoreDetails": {
                "Name": "Claire",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "The Robot"
            }
        },
        {
            "Id": 6,
            "RobotCoreDetails": {
                "Name": "Tracey",
                "Active": false
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Moonwalk"
            }
        },
        {
            "Id": 6,
            "RobotCoreDetails": {
                "Name": "Tracey",
                "Active": false
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Thunder Clap"
            }
        },
        {
            "Id": 6,
            "RobotCoreDetails": {
                "Name": "Tracey",
                "Active": false
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "The Robot"
            }
        }
    ]
}

This modification correctly provides a single Robot with multiple dance moves. The way to achieve this is by using a subquery SELECT which is directly linked to the outer SELECT (see the ‘WHERE rdm.RobotId = r.Id’ clause). All SELECT statements require a FOR JSON PATH clause to be present. Some additional formatting has been put in place here to output ‘Yep’ or ‘Nope’ to denote if this particular dance move is the Robots favourite.

SQL:

-- Correctly nested results, illustration one
SELECT 
r.Id AS [Id]
, r.[Name] AS [RobotCoreDetails.Name]
, r.Active AS [RobotCoreDetails.Active] 
,
(
	SELECT
	rdm.[Name]
	, 
	-- Use of CASE statement just to show that normal manipulation of data is possible, as expected
	CASE
		WHEN rdm.FavouriteMove = 1
		THEN 'Yep'
		ELSE 'Nope'
	END AS [FavouriteMove]
	FROM dbo.RobotDanceMoves rdm
	WHERE rdm.RobotId = r.Id
	FOR JSON PATH
) AS [RobotDanceMoves]
FROM dbo.Robots r
FOR JSON PATH, ROOT('Robots');

JSON output:

{
    "Robots": [
        {
            "Id": 1,
            "RobotCoreDetails": {
                "Name": "Barry",
                "Active": true
            },
            "RobotDanceMoves": [
                {
                    "Name": "Moonwalk",
                    "FavouriteMove": "Yep"
                },
                {
                    "Name": "The Robot",
                    "FavouriteMove": "Nope"
                },
                {
                    "Name": "Thunder Clap",
                    "FavouriteMove": "Nope"
                }
            ]
        },
        {
            "Id": 2,
            "RobotCoreDetails": {
                "Name": "Steve",
                "Active": false
            },
            "RobotDanceMoves": [
                {
                    "Name": "The Robot",
                    "FavouriteMove": "Nope"
                },
                {
                    "Name": "Moonwalk",
                    "FavouriteMove": "Nope"
                },
                {
                    "Name": "Thunder Clap",
                    "FavouriteMove": "Yep"
                }
            ]
        },
        {
            "Id": 3,
            "RobotCoreDetails": {
                "Name": "Dave",
                "Active": true
            },
            "RobotDanceMoves": [
                {
                    "Name": "Moonwalk",
                    "FavouriteMove": "Yep"
                },
                {
                    "Name": "The Robot",
                    "FavouriteMove": "Nope"
                },
                {
                    "Name": "Thunder Clap",
                    "FavouriteMove": "Nope"
                }
            ]
        },
        {
            "Id": 4,
            "RobotCoreDetails": {
                "Name": "Zoe",
                "Active": true
            },
            "RobotDanceMoves": [
                {
                    "Name": "The Robot",
                    "FavouriteMove": "Yep"
                },
                {
                    "Name": "Moonwalk",
                    "FavouriteMove": "Nope"
                },
                {
                    "Name": "Thunder Clap",
                    "FavouriteMove": "Nope"
                }
            ]
        },
        {
            "Id": 5,
            "RobotCoreDetails": {
                "Name": "Claire",
                "Active": true
            },
            "RobotDanceMoves": [
                {
                    "Name": "Thunder Clap",
                    "FavouriteMove": "Nope"
                },
                {
                    "Name": "Moonwalk",
                    "FavouriteMove": "Nope"
                },
                {
                    "Name": "The Robot",
                    "FavouriteMove": "Yep"
                }
            ]
        },
        {
            "Id": 6,
            "RobotCoreDetails": {
                "Name": "Tracey",
                "Active": false
            },
            "RobotDanceMoves": [
                {
                    "Name": "Moonwalk",
                    "FavouriteMove": "Yep"
                },
                {
                    "Name": "Thunder Clap",
                    "FavouriteMove": "Nope"
                },
                {
                    "Name": "The Robot",
                    "FavouriteMove": "Nope"
                }
            ]
        }
    ]
}

Finally, here we have altered the dance move output so that it sits inside a ‘Move’ object. It’s really interesting how aliasing name parts, separated by a period, are directly involved in managing how the content is pushed into nested objects.

SQL:

-- Correctly nested results, illustration two (RobotDanceMoves abstracted into 'Move' using aliases)
SELECT 
r.Id AS [Id]
, r.[Name] AS [RobotCoreDetails.Name]
, r.Active AS [RobotCoreDetails.Active] 
,
(
	SELECT
	rdm.[Name] AS [Move.Name]
	, 
	-- Use of CASE statement just to show that normal manipulation of data is possible, as expected
	CASE
		WHEN rdm.FavouriteMove = 1
		THEN 'Yep'
		ELSE 'Nope'
	END AS [Move.Favourite]
	FROM dbo.RobotDanceMoves rdm
	WHERE rdm.RobotId = r.Id
	FOR JSON PATH
) AS [RobotDanceMoves]
FROM dbo.Robots r
FOR JSON PATH, ROOT('Robots');

JSON output:

{
    "Robots": [
        {
            "Id": 1,
            "RobotCoreDetails": {
                "Name": "Barry",
                "Active": true
            },
            "RobotDanceMoves": [
                {
                    "Move": {
                        "Name": "Moonwalk",
                        "Favourite": "Yep"
                    }
                },
                {
                    "Move": {
                        "Name": "The Robot",
                        "Favourite": "Nope"
                    }
                },
                {
                    "Move": {
                        "Name": "Thunder Clap",
                        "Favourite": "Nope"
                    }
                }
            ]
        },
        {
            "Id": 2,
            "RobotCoreDetails": {
                "Name": "Steve",
                "Active": false
            },
            "RobotDanceMoves": [
                {
                    "Move": {
                        "Name": "The Robot",
                        "Favourite": "Nope"
                    }
                },
                {
                    "Move": {
                        "Name": "Moonwalk",
                        "Favourite": "Nope"
                    }
                },
                {
                    "Move": {
                        "Name": "Thunder Clap",
                        "Favourite": "Yep"
                    }
                }
            ]
        },
        {
            "Id": 3,
            "RobotCoreDetails": {
                "Name": "Dave",
                "Active": true
            },
            "RobotDanceMoves": [
                {
                    "Move": {
                        "Name": "Moonwalk",
                        "Favourite": "Yep"
                    }
                },
                {
                    "Move": {
                        "Name": "The Robot",
                        "Favourite": "Nope"
                    }
                },
                {
                    "Move": {
                        "Name": "Thunder Clap",
                        "Favourite": "Nope"
                    }
                }
            ]
        },
        {
            "Id": 4,
            "RobotCoreDetails": {
                "Name": "Zoe",
                "Active": true
            },
            "RobotDanceMoves": [
                {
                    "Move": {
                        "Name": "The Robot",
                        "Favourite": "Yep"
                    }
                },
                {
                    "Move": {
                        "Name": "Moonwalk",
                        "Favourite": "Nope"
                    }
                },
                {
                    "Move": {
                        "Name": "Thunder Clap",
                        "Favourite": "Nope"
                    }
                }
            ]
        },
        {
            "Id": 5,
            "RobotCoreDetails": {
                "Name": "Claire",
                "Active": true
            },
            "RobotDanceMoves": [
                {
                    "Move": {
                        "Name": "Thunder Clap",
                        "Favourite": "Nope"
                    }
                },
                {
                    "Move": {
                        "Name": "Moonwalk",
                        "Favourite": "Nope"
                    }
                },
                {
                    "Move": {
                        "Name": "The Robot",
                        "Favourite": "Yep"
                    }
                }
            ]
        },
        {
            "Id": 6,
            "RobotCoreDetails": {
                "Name": "Tracey",
                "Active": false
            },
            "RobotDanceMoves": [
                {
                    "Move": {
                        "Name": "Moonwalk",
                        "Favourite": "Yep"
                    }
                },
                {
                    "Move": {
                        "Name": "Thunder Clap",
                        "Favourite": "Nope"
                    }
                },
                {
                    "Move": {
                        "Name": "The Robot",
                        "Favourite": "Nope"
                    }
                }
            ]
        }
    ]
}

In an effort to show a more complex example the following SQL produces nested content illustrating the relationship between ‘Houses’, ‘Rooms’ and ‘RoomObjects’. This uses the same principle of aliasing subqueries and linking inner/outer queries as before.

SQL:

-- Illustration of producing a query that handles one-to-many relationships gracefully
SELECT 
h.Id AS [Id]
, h.[Name] AS [Name]
,
(
	SELECT
	r.Id AS [Id]
	, r.[Name] AS [Name]
	,
	(
		SELECT
		ro.Id AS [Id]
		, ro.[Name] AS [Name]
		FROM dbo.RoomObjects ro
		WHERE ro.RoomId = r.Id
		FOR JSON PATH
	) AS [RoomObjects]
	FROM dbo.Rooms r
	WHERE r.HouseId = h.Id
	FOR JSON PATH
) AS [Rooms]
FROM dbo.Houses h
FOR JSON PATH, ROOT('Houses');

JSON output:

{
    "Houses": [
        {
            "Id": 1,
            "Name": "House One",
            "Rooms": [
                {
                    "Id": 1,
                    "Name": "Lounge",
                    "RoomObjects": [
                        {
                            "Id": 1,
                            "Name": "Lamp"
                        },
                        {
                            "Id": 2,
                            "Name": "Sofa"
                        }
                    ]
                },
                {
                    "Id": 2,
                    "Name": "Kitchen",
                    "RoomObjects": [
                        {
                            "Id": 3,
                            "Name": "Knife"
                        },
                        {
                            "Id": 4,
                            "Name": "Kettle"
                        }
                    ]
                }
            ]
        },
        {
            "Id": 2,
            "Name": "House Two",
            "Rooms": [
                {
                    "Id": 3,
                    "Name": "Lounge Diner",
                    "RoomObjects": [
                        {
                            "Id": 5,
                            "Name": "Coffee Table"
                        },
                        {
                            "Id": 6,
                            "Name": "Armchair"
                        }
                    ]
                },
                {
                    "Id": 4,
                    "Name": "Kitchen Utility",
                    "RoomObjects": [
                        {
                            "Id": 7,
                            "Name": "Coffee Machine"
                        },
                        {
                            "Id": 8,
                            "Name": "Microwave"
                        }
                    ]
                }
            ]
        }
    ]
}

To really improve the nesting of the output it is possible to combine and align column and subquery aliases to get a nice, clean structure.

SQL:

-- Example extended with some more aliases to format the structure further 
-- (full nesting of House -> Rooms -> Room -> RoomObjects -> Object)
SELECT 
h.Id AS [House.Id]
, h.[Name] AS [House.Name]
,
(
	SELECT
	r.Id AS [Room.Id]
	, r.[Name] AS [Room.Name]
	,
	(
		SELECT
		ro.Id AS [Object.Id]
		, ro.[Name] AS [Object.Name]
		FROM dbo.RoomObjects ro
		WHERE ro.RoomId = r.Id
		FOR JSON PATH
	) AS [Room.RoomObjects]
	FROM dbo.Rooms r
	WHERE r.HouseId = h.Id
	FOR JSON PATH
) AS [House.Rooms]
FROM dbo.Houses h
FOR JSON PATH, ROOT('Houses');

JSON output:

{
    "Houses": [
        {
            "House": {
                "Id": 1,
                "Name": "House One",
                "Rooms": [
                    {
                        "Room": {
                            "Id": 1,
                            "Name": "Lounge",
                            "RoomObjects": [
                                {
                                    "Object": {
                                        "Id": 1,
                                        "Name": "Lamp"
                                    }
                                },
                                {
                                    "Object": {
                                        "Id": 2,
                                        "Name": "Sofa"
                                    }
                                }
                            ]
                        }
                    },
                    {
                        "Room": {
                            "Id": 2,
                            "Name": "Kitchen",
                            "RoomObjects": [
                                {
                                    "Object": {
                                        "Id": 3,
                                        "Name": "Knife"
                                    }
                                },
                                {
                                    "Object": {
                                        "Id": 4,
                                        "Name": "Kettle"
                                    }
                                }
                            ]
                        }
                    }
                ]
            }
        },
        {
            "House": {
                "Id": 2,
                "Name": "House Two",
                "Rooms": [
                    {
                        "Room": {
                            "Id": 3,
                            "Name": "Lounge Diner",
                            "RoomObjects": [
                                {
                                    "Object": {
                                        "Id": 5,
                                        "Name": "Coffee Table"
                                    }
                                },
                                {
                                    "Object": {
                                        "Id": 6,
                                        "Name": "Armchair"
                                    }
                                }
                            ]
                        }
                    },
                    {
                        "Room": {
                            "Id": 4,
                            "Name": "Kitchen Utility",
                            "RoomObjects": [
                                {
                                    "Object": {
                                        "Id": 7,
                                        "Name": "Coffee Machine"
                                    }
                                },
                                {
                                    "Object": {
                                        "Id": 8,
                                        "Name": "Microwave"
                                    }
                                }
                            ]
                        }
                    }
                ]
            }
        }
    ]
}

The full code sample is available here (creation of the tables, test data and some example stored procedures are clubbed together for you to play with as you want):

/*

	FOR JSON PATH - Full code example

*/

PRINT ('FOR JSON PATH - Full code example' + CHAR(13) + '--------------------------------------------');

-- 1) Create the 'JsonForTestDatabase', if required
IF (DB_ID('JsonForTestDatabase') IS NULL)
BEGIN

	PRINT ('Creating the ''JsonForTestDatabase'' sample database.');

	CREATE DATABASE JsonForTestDatabase;

END;
GO

-- 2) Explicitly switch the session to 'point' at the JsonForTestDatabase
USE [JsonForTestDatabase]
GO

-- 3) Create the test tables for example purposes (Robot/House tables)
IF(OBJECT_ID('Robots', 'U') IS NULL)
BEGIN

	PRINT ('Creating the ''Robots'' table.');

	CREATE TABLE dbo.Robots
	(
		Id					BIGINT			NOT NULL	PRIMARY KEY		IDENTITY(1, 1)
		, [Name]			NVARCHAR(20)	NOT NULL
		, Active			BIT				NOT NULL	DEFAULT(1)
	);

END;

IF(OBJECT_ID('RobotDanceMoves', 'U') IS NULL)
BEGIN

	PRINT ('Creating the ''RobotDanceMoves'' table.');

	CREATE TABLE dbo.RobotDanceMoves
	(
		Id					BIGINT			NOT NULL	PRIMARY KEY		IDENTITY(1, 1)
		, RobotId			BIGINT			FOREIGN KEY (RobotId) REFERENCES dbo.Robots(Id)
		, [Name]			NVARCHAR(20)	NOT NULL
		, FavouriteMove		BIT				NOT NULL	DEFAULT(0)
	);

END;

IF(OBJECT_ID('Houses', 'U') IS NULL)
BEGIN

	PRINT ('Creating the ''Houses'' table.');

	CREATE TABLE dbo.Houses
	(
		Id					BIGINT			PRIMARY KEY		IDENTITY(1, 1)
		, [Name]			NVARCHAR(20)
	);

END;

IF(OBJECT_ID('Rooms', 'U') IS NULL)
BEGIN

	PRINT ('Creating the ''Rooms'' table.');

	CREATE TABLE dbo.Rooms 
	( 
		Id					BIGINT			PRIMARY KEY		IDENTITY(1, 1)
		, HouseId			BIGINT			FOREIGN KEY (HouseId) REFERENCES dbo.Houses(Id)
		, [Name]			NVARCHAR(20)
	);

END;

IF(OBJECT_ID('RoomObjects', 'U') IS NULL)
BEGIN

	PRINT ('Creating the ''RoomObjects'' table.');

	CREATE TABLE dbo.RoomObjects 
	( 
		Id					BIGINT			PRIMARY KEY		IDENTITY(1, 1)
		, RoomId			BIGINT			FOREIGN KEY (RoomId) REFERENCES dbo.Rooms(Id)
		, [Name]			NVARCHAR(20)
	);

END;

-- 4) Drop/recreate the Utility SPs
IF(OBJECT_ID('GetRobotsWithFavouriteDanceMoveJson', 'P') IS NOT NULL)
BEGIN
	
	PRINT ('Dropping the ''GetRobotsWithFavouriteDanceMoveJson'' stored procedure.');

	DROP PROCEDURE dbo.GetRobotsWithFavouriteDanceMoveJson;

END;

PRINT ('Creating the ''GetRobotsWithFavouriteDanceMoveJson'' stored procedure.');
GO

CREATE PROCEDURE dbo.GetRobotsWithFavouriteDanceMoveJson
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Produce a very simple, flat representation of a Robot and their favourite dance move
	SELECT 
	r.Id
	, r.[Name]
	, r.Active
	, rdm.[Name] AS [FavouriteDanceMove]
	FROM dbo.Robots r
		INNER JOIN dbo.RobotDanceMoves rdm ON 
		(
			r.Id = rdm.RobotId
			AND rdm.FavouriteMove = 1
		)
	FOR JSON PATH;

	-- Return execution status of success
	RETURN 0;

END;
GO

IF(OBJECT_ID('GetFormattedRobotsWithFavouriteDanceMoveJson', 'P') IS NOT NULL)
BEGIN
	
	PRINT ('Dropping the ''GetFormattedRobotsWithFavouriteDanceMoveJson'' stored procedure.');

	DROP PROCEDURE dbo.GetFormattedRobotsWithFavouriteDanceMoveJson;

END;
	
PRINT ('Creating the ''GetFormattedRobotsWithFavouriteDanceMoveJson'' stored procedure.');
GO

CREATE PROCEDURE dbo.GetFormattedRobotsWithFavouriteDanceMoveJson
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Illustration of how the JSON can be manipulated and formatted further by altering column aliases
	SELECT 
	r.Id AS [Id]
	, r.[Name] AS [RobotCoreDetails.Name]
	, r.Active AS [RobotCoreDetails.Active]
	, rdm.[Name] AS [RobotDanceMove.FavouriteDanceMove]
	FROM dbo.Robots r
		INNER JOIN dbo.RobotDanceMoves rdm ON 
		(
			r.Id = rdm.RobotId
			AND rdm.FavouriteMove = 1
		)
	FOR JSON PATH;

	-- Return execution status of success
	RETURN 0;

END;
GO

IF(OBJECT_ID('GetRobotsWithFavouriteDanceMoveAndRootJson', 'P') IS NOT NULL)
BEGIN

	PRINT ('Dropping the ''GetRobotsWithFavouriteDanceMoveAndRootJson'' stored procedure.');
	
	DROP PROCEDURE dbo.GetRobotsWithFavouriteDanceMoveAndRootJson;

END;

PRINT ('Creating the ''GetRobotsWithFavouriteDanceMoveAndRootJson'' stored procedure.');
GO

CREATE PROCEDURE dbo.GetRobotsWithFavouriteDanceMoveAndRootJson
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Illustration of adding a ROOT clause to wrap the produced JSON content
	SELECT 
	r.Id AS [Id]
	, r.[Name] AS [RobotCoreDetails.Name]
	, r.Active AS [RobotCoreDetails.Active]
	, rdm.[Name] AS [RobotDanceMove.FavouriteDanceMove]
	FROM dbo.Robots r
		INNER JOIN dbo.RobotDanceMoves rdm ON 
		(
			r.Id = rdm.RobotId
			AND rdm.FavouriteMove = 1
		)
	FOR JSON PATH, ROOT('Robots');

	-- Return execution status of success
	RETURN 0;

END;
GO

IF(OBJECT_ID('GetFullRobotDetailAsJson', 'P') IS NOT NULL)
BEGIN

	PRINT ('Dropping the ''GetFullRobotDetailAsJson'' stored procedure.');
	
	DROP PROCEDURE dbo.GetFullRobotDetailAsJson;

END;

PRINT ('Creating the ''GetFullRobotDetailAsJson'' stored procedure.');
GO

CREATE PROCEDURE dbo.GetFullRobotDetailAsJson
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;

	/*
	-- Represents the incorrect way to produce correctly nested results, on this occassion
	SELECT 
	r.Id AS [Id]
	, r.[Name] AS [RobotCoreDetails.Name]
	, r.Active AS [RobotCoreDetails.Active]
	, rdm.[Name] AS [RobotDanceMove.FavouriteDanceMove]
	FROM dbo.Robots r
		INNER JOIN dbo.RobotDanceMoves rdm ON r.Id = rdm.RobotId
	FOR JSON PATH, ROOT('Robots');

	-- Correctly nested results, illustration one
	SELECT 
	r.Id AS [Id]
	, r.[Name] AS [RobotCoreDetails.Name]
	, r.Active AS [RobotCoreDetails.Active] 
	,
	(
		SELECT
		rdm.[Name]
		, 
		-- Use of CASE statement just to show that normal manipulation of data is possible, as expected
		CASE
			WHEN rdm.FavouriteMove = 1
			THEN 'Yep'
			ELSE 'Nope'
		END AS [FavouriteMove]
		FROM dbo.RobotDanceMoves rdm
		WHERE rdm.RobotId = r.Id
		FOR JSON PATH
	) AS [RobotDanceMoves]
	FROM dbo.Robots r
	FOR JSON PATH, ROOT('Robots');
	*/

	-- Correctly nested results, illustration two (RobotDanceMoves abstracted into 'Move' using aliases)
	SELECT 
	r.Id AS [Id]
	, r.[Name] AS [RobotCoreDetails.Name]
	, r.Active AS [RobotCoreDetails.Active] 
	,
	(
		SELECT
		rdm.[Name] AS [Move.Name]
		, 
		-- Use of CASE statement just to show that normal manipulation of data is possible, as expected
		CASE
			WHEN rdm.FavouriteMove = 1
			THEN 'Yep'
			ELSE 'Nope'
		END AS [Move.Favourite]
		FROM dbo.RobotDanceMoves rdm
		WHERE rdm.RobotId = r.Id
		FOR JSON PATH
	) AS [RobotDanceMoves]
	FROM dbo.Robots r
	FOR JSON PATH, ROOT('Robots');

	-- Return execution status of success
	RETURN 0;

END;
GO

IF(OBJECT_ID('GetHouseDetailAsJson', 'P') IS NOT NULL)
BEGIN

	PRINT ('Dropping the ''GetHouseDetailAsJson'' stored procedure.');
	
	DROP PROCEDURE dbo.GetHouseDetailAsJson;

END;

PRINT ('Creating the ''GetHouseDetailAsJson'' stored procedure.');
GO

CREATE PROCEDURE dbo.GetHouseDetailAsJson
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;

	/*
	-- Illustration of producing a query that handles one-to-many relationships gracefully
	SELECT 
	h.Id AS [Id]
	, h.[Name] AS [Name]
	,
	(
		SELECT
		r.Id AS [Id]
		, r.[Name] AS [Name]
		,
		(
			SELECT
			ro.Id AS [Id]
			, ro.[Name] AS [Name]
			FROM dbo.RoomObjects ro
			WHERE ro.RoomId = r.Id
			FOR JSON PATH
		) AS [RoomObjects]
		FROM dbo.Rooms r
		WHERE r.HouseId = h.Id
		FOR JSON PATH
	) AS [Rooms]
	FROM dbo.Houses h
	FOR JSON PATH, ROOT('Houses');
	*/

	-- Example extended with some more aliases to format the structure further 
	-- (full nesting of House -> Rooms -> Room -> RoomObjects -> Object)
	SELECT 
	h.Id AS [House.Id]
	, h.[Name] AS [House.Name]
	,
	(
		SELECT
		r.Id AS [Room.Id]
		, r.[Name] AS [Room.Name]
		,
		(
			SELECT
			ro.Id AS [Object.Id]
			, ro.[Name] AS [Object.Name]
			FROM dbo.RoomObjects ro
			WHERE ro.RoomId = r.Id
			FOR JSON PATH
		) AS [Room.RoomObjects]
		FROM dbo.Rooms r
		WHERE r.HouseId = h.Id
		FOR JSON PATH
	) AS [House.Rooms]
	FROM dbo.Houses h
	FOR JSON PATH, ROOT('Houses');

	-- Return execution status of success
	RETURN 0;

END;
GO

-- 5) Insert sample data into the test tables...part 1, robots...
IF NOT EXISTS
(
	SELECT 1
	FROM dbo.Robots
)
BEGIN

	PRINT ('Adding dbo.Robot sample data.');

	INSERT INTO dbo.Robots
	(
		[Name]
		, Active
	)
	VALUES
	('Barry', 1)
	, ('Steve', 0)
	, ('Dave', 1)
	, ('Zoe', 1)
	, ('Claire', 1)
	, ('Tracey', 0);

END;

IF NOT EXISTS
(
	SELECT 1
	FROM dbo.RobotDanceMoves
)
BEGIN

	PRINT ('Adding dbo.RobotDanceMoves sample data.');
	
	--If RobotDanceMoves have been inserted yet the produce a mock sample of randomised data where Robots receive
	--all three dance moves but a 'favourite' on the fly
	INSERT INTO dbo.RobotDanceMoves
	(
		 RobotId
		 , [Name]
		 , FavouriteMove
	)
	SELECT 
	r.Id
	, rm.DanceMove
	, rm.FavouriteMove
	FROM dbo.Robots r
		CROSS APPLY 
		( 
			SELECT 
			TOP 3 
			am.DanceMove
			, 
			CASE 
				WHEN ROW_NUMBER() OVER (ORDER BY NEWID()) = 3
				THEN CAST(1 AS BIT)
				ELSE CAST(0 AS BIT)
			END AS [FavouriteMove]
			FROM
			(
				SELECT
				'Moonwalk' AS [DanceMove]
				UNION
				SELECT
				'Thunder Clap'
				UNION
				SELECT
				'The Robot'
			) AS am
			WHERE r.Id = r.Id
			ORDER BY NEWID()
		) AS rm;

END;

--...Part two, houses (very fixed, just as an illustration)
IF NOT EXISTS
(
	SELECT 1
	FROM dbo.Houses
)
BEGIN

	PRINT ('Adding dbo.Houses sample data.');

	INSERT INTO dbo.Houses
	(
		[Name]
	)
	VALUES
	('House One')
	, ('House Two');

END;

IF NOT EXISTS
(
	SELECT 1
	FROM dbo.Rooms
)
BEGIN

	PRINT ('Adding dbo.Rooms sample data.');

	INSERT INTO dbo.Rooms
	(
		HouseId
		, [Name]
	)
	VALUES
	(1, 'Lounge')
	, (1, 'Kitchen')
	, (2, 'Lounge Diner')
	, (2, 'Kitchen Utility');

END;

IF NOT EXISTS
(
	SELECT 1
	FROM dbo.RoomObjects
)
BEGIN

	PRINT ('Adding dbo.RoomObjects sample data.');

	INSERT INTO dbo.RoomObjects
	(
		RoomId
		, [Name]
	)
	VALUES
	(1, 'Lamp')
	, (1, 'Sofa')
	, (2, 'Knife')
	, (2, 'Kettle')
	, (3, 'Coffee Table')
	, (3, 'Armchair')
	, (4, 'Coffee Machine')
	, (4, 'Microwave');

END;
GO

--6) Execute all of the sample stored procedures
PRINT (CHAR(13) + 'Processing complete...running example stored procedures...');

EXEC dbo.GetRobotsWithFavouriteDanceMoveJson;
EXEC dbo.GetFormattedRobotsWithFavouriteDanceMoveJson;
EXEC dbo.GetRobotsWithFavouriteDanceMoveAndRootJson;
EXEC dbo.GetFullRobotDetailAsJson;
EXEC dbo.GetHouseDetailAsJson;

I hope there are some valuable and useful ideas in this post and, until the next time, happy coding.

SQL Identity Column Monitoring Script

I recently found myself in a position whereby I needed to know how the identity columns within a given database where configured. The information I was hunting for was as follows:

  1. Identity column name and table the column belonged to.
  2. The column Data Type
  3. The last seed value used.
  4. The difference between the maximum value allowed for the Data Type and the current seed value.

I wanted something that gave a rough, first pass account of this detail that I could script in under ten minutes (so not a complete solution by any account). I thought I’d share the result, that can be taken and expanded upon as needed. The core part of this script relies on the sys.identity_columns view, which contains the essential detail behind an identity column such as the seed value, increment and last seed value used. As this view uses sys.columns as its backbone, you also get all of the standard column metadata included like the column name, information on which table the column relates to and the column Data Type to boot; bonus!

You’ll notice that the following code assumes a seed ‘increment_value’ of 1, but this is an enhancement that can be easily made. Here is the current SQL in its raw form:

-- A first stab at some operational monitoring. Grab all of the columns that support 'seeding' and, based on type, look at the last seed value recorded against
-- the maximum supported value (could expand this, use it for reporting, etc.)
SELECT 
st.[name] AS [TableName]
, ic.[Name] AS [SeedColumnName]
, stype.system_type_id AS [DataTypeId]
, stype.[name] AS [DataTypeName]
, 
CASE
	WHEN stype.system_type_id = 56
	THEN '2147483647'
	WHEN stype.system_type_id = 127
	THEN '9223372036854775807'
	ELSE 'Unknown'
END AS [MaximumAllowedPositiveValue]
, COALESCE(CONVERT(NVARCHAR(20), ic.last_value), 'Last Value Unset') AS [LastSeedValue]
, 
CASE
	WHEN COALESCE(CONVERT(NVARCHAR(20), ic.last_value), '') = ''
	THEN 'Cannot Calculate'
	WHEN stype.system_type_id = 56
	THEN CONVERT(NVARCHAR(20), (2147483647 - CAST(ic.last_value AS INT)))
	WHEN stype.system_type_id = 127
	THEN CONVERT(NVARCHAR(20), (9223372036854775807 - CAST(ic.last_value AS BIGINT)))
	ELSE 'Unknown'
END AS [CurrentDifferenceBetweenSeedAndMax]
FROM sys.identity_columns ic
	INNER JOIN sys.tables st ON ic.object_id = st.object_id	
	INNER JOIN sys.types stype ON ic.system_type_id = stype.system_type_id
ORDER by st.[Name];

You should get output a little something like this:

Identity Columns Script Example Output.

Identity Columns Script Example Output.

The ‘last_value’ column is of type sql_variant so note the use of casting where appropriate. I’ve observed that there is a possibility of the ‘last_value’ being NULL, so I’ve attempted to cater for this using a case statement (more robust code is most likely needed here). The use of NVARCHAR(20) for conversion is built with INT/BIGINT in mind, this can be adjusted for identity values that use Data Types outside of this range.

I hope this comes in handy and/or forms the basis for a more complete solution in the future, I’ll tinker with this in due course no doubt as I need to expand it.

Here’s me signing off until the next time.

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!