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.

A Couple of Hours with Azure Maps

I’m having a random ‘pick a Channel 9 video and blog from there’ session; the subject of the day is Azure Maps and the inspiration came in the form of this video.

The plan is to see what I can achieve in an hour or two, so here’s my quick rundown to wet your whistle. Firstly, a quick resource list to get you going which gives an idea of the product itself as well as details on pricing and the core API:

  1. Azure Maps
  2. Pricing
  3. Quick Starts

I’ll be (partly) following this quick start guide, but I may break away a bit and engage ‘rebel’ mode as that’s my style. πŸ˜›

Within the Azure Portal start by creating a new resource, searching using the keyword ‘Maps’; nice and simple for starters. Click ‘create’ as shown below:

Creating a Maps Resource.

Creating a Maps Resource.

For our next course of yumminess, simply fill in the mandatory fields specifying a Name, selecting a Subscription, an existing Resource Group (or creating a new one, which I did here for some clean separation) and finally selecting a Resource Group location that makes sense for you. I’ve opted to pin this resource to my dashboard for easy access later.

Create a Maps Account.

Create a Maps Account.

Once created, like many resources, we then just need to obtain the access key by going to ‘YOUR_MAP_RESOURCE’ in the Azure Portal > Settings > Keys. The sample application referenced on the demo resources page is doing a wonderful 404 trick at the time of writing, so I’ll see what I can put together as a basic sample myself, as I have the key in tow.

At this point I engaged ‘full nosiness mode’ and poking around further lead me to some step-by-step samples; this looks like a good starting template. Using this template to generate my own code example (and throwing in some ES6 concepts for good measure) I came up with this lightweight, ‘one-shot’ HTML page in VS Code (I really need to use VS Code more as it’s going great guns now and is getting excellent traction in the development community from what I can gather):

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, user-scalable=no" />
    <title>Azure Map Test</title>
    <link rel="stylesheet" href="https://atlas.microsoft.com/sdk/css/atlas.min.css?api-version=1.0" type="text/css" />
    <script src="https://atlas.microsoft.com/sdk/js/atlas.min.js?api-version=1.0"></script>
    <style>
        html,
        body {
            width: 100%;
            height: 100%;
            padding: 0;
            margin: 0;
        }

        #mapContainer {
            width: 100%;
            height: 100%;
        }
    </style>
</head>
<body>    
    <div id="mapContainer"></div>
    <script>
        // Encapsulation class that is a holding container for search parameters
        class SearchOptions {
            constructor(subscriptionKey, searchTerm, startLatitude, startLongitude, searchRadius ) {
                this.subscriptionKey = subscriptionKey;
                this.searchTerm = searchTerm;
                this.startLatitude = startLatitude;
                this.startLongitude = startLongitude;
                this.searchRadius = searchRadius;
            }
            // Utility function for generating a search url based on the class properties
            generateSearchUrl() {
                return `https://atlas.microsoft.com/search/fuzzy/json?api-version=1.0&query=${ this.searchTerm }&subscription-key=${ this.subscriptionKey }&lat=${ this.startLatitude }&lon=${ this.startLongitude }&radius=${ this.searchRadius }`;
            }
        }

        // Function for generating a map (using the mapContainer element reference provided and subscription key)
        function getMap(mapContainer, subscriptionKey) {
            return new atlas.Map(mapContainer, {
                "subscription-key": subscriptionKey
            });
        }

        // Function for preparing the pin layer on the targeted map using the provided layer name
        function prepareMapPins(map, searchLayerName, pinType) {
            map.addPins([], {
                name: searchLayerName,
                cluster: false,
                icon: pinType
            });
        }

        // Function that processes the data from 'fetch' and adds pins (POIs) the the map using the returned json data
        function processMapData(data, searchLayerName, map, cameraPadding) {
            if (data != null && data.results != null && data.results.length > 0) {
                // Initialise a searchPins array and limit the returned json data to those that are marked as POIs
                let searchPins = [],
                    poiResults = data.results.filter((result) => { return result.type === "POI" }) || [];

                // Extract features from the returned data and add it to the searchPins array (this contains location-based information)
                searchPins = poiResults.map((poiResult) => {
                    let poiPosition = [poiResult.position.lon, poiResult.position.lat];

                    return new atlas.data.Feature(new atlas.data.Point(poiPosition), {
                        name: poiResult.poi.name,
                        address: poiResult.address.freeformAddress,
                        position: poiResult.position.lat + ", " + poiResult.position.lon
                    });
                });

                // Add POIs discovered to the appropriate search layer
                map.addPins(searchPins, {
                    name: searchLayerName
                });

                // Set the map camera to be fixed on the 'searchPins'
                let lons = searchPins.map((pin) => pin.geometry.coordinates[0] ),
                    lats = searchPins.map((pin) => pin.geometry.coordinates[1] ),
                    swLon = Math.min.apply(null, lons),
                    swLat = Math.min.apply(null, lats),
                    neLon = Math.max.apply(null, lons),
                    neLat = Math.max.apply(null, lats);

                map.setCameraBounds({
                    bounds: [swLon, swLat, neLon, neLat],
                    padding: cameraPadding
                });             
            }
        }

        // Function that is triggered on 'mouseover' of a pin element to display extra information
        function createMouseOverPopUps(e, popup, map) {
            let popupContentElement = document.createElement("div");
            popupContentElement.style.padding = "5px";

            let popupNameElement = document.createElement("div");
            popupNameElement.innerText = e.features[0].properties.name;
            popupContentElement.appendChild(popupNameElement);

            let popupAddressElement = document.createElement("div");
            popupAddressElement.innerText = e.features[0].properties.address;
            popupContentElement.appendChild(popupAddressElement);

            let popupPositionElement = document.createElement("div");
            popupPositionElement.innerText = e.features[0].properties.name;
            popupContentElement.appendChild(popupPositionElement);

            popup.setPopupOptions({
                position: e.features[0].geometry.coordinates,
                content: popupContentElement
            });

            popup.open(map);
        }

        // Function to actually create the map
        function createMap() {
            // Alter the query parameters here for testing, add a subscription key, search term (e.g. 'hairdressers' or 'pubs'), 
            // the latitude/longitude to begin the search from and the radius to search (in metres)
            const subscriptionKey = "INSERT_SUBSCRIPTION_KEY_HERE",
                  searchTerm = 'pubs',
                  startLatitude = '52.630181',
                  startLongitude = '1.297415',
                  searchRadius = 1000,
                  // The 'search layer' that will contain the discovered 'pins' and will tie to mouse over pop-ups
                  searchLayerName = "search-results",
                  // Use this to switch out the pin type on render (https://docs.microsoft.com/en-us/javascript/api/azure-maps-javascript/pinproperties?view=azure-iot-typescript-latest)
                  pinType = "pin-red",
                  // Had issues when searching a small radius and having this value too high (overlapping pins???) - but adjust as necessary
                  cameraPadding = 1;

            // Encapsulate the search constants into a utility class which containts a function for calculating a 
            // search url. Also, generate a map/popup object pre-search to get us started
            let options = new SearchOptions(subscriptionKey, searchTerm, startLatitude, startLongitude, searchRadius),
                popup = new atlas.Popup();
                map = getMap('mapContainer', subscriptionKey);

            // Initialise the pin layer for the targeted map
            prepareMapPins(map, searchLayerName, pinType);

            // Use fetch to call the generated search URL and process the response to add data points (POIs in this case) to the map
            fetch(options.generateSearchUrl())
                .then(response => response.json())
                .then(data => processMapData(data, searchLayerName, map, cameraPadding));

            // Add a popup to the map which will display some basic information about a search result on hover over a pin
            map.addEventListener("mouseover", searchLayerName, (e) => {
                createMouseOverPopUps(e, popup, map);
            });
        }

        // Create the sample map!
        createMap();
    </script>
</body>
</html>

I’ve added inline comments to try and explain the core workings of the objects on show. In essence, you just need to:

  1. Ensure the atlas.min.css style sheet is in scope.
  2. Ensure the atlas.min.js script is in scope.
  3. Create a div with a selector (using an id in this instance) so it can be targeted.
  4. Call atlas.Map specifying the container (div you previous created) you want to render the map within, along with a valid subscription key.

In this example, I create a SearchOptions class that acts a way of encapsulating configurable parts of a search and provides a way of generating a dynamic search URL using a template string (template literal). The createMap function is called first and creates a SearchOptions instance up front, this function is where you can modify search parameters as you see fit. When using this sample code be sure to switch out ‘INSERT_SUBSCRIPTION_KEY_HERE’ for a valid subscription key. You can specify a latitude/longitude as a linchpin for the search, a search radius in metres and a search term to target specific points of interest (POIs).

Along with a SearchOptions object, a ‘popup’ utility object (to handle how popups are rendered when a map pin is ‘moused over’) and the physical map is created, using the getMap function. This is where atlas.Map is called, for reference.

To render pins on the map for POIs a named ‘layer’ must be created against the map object in scope. This is handled via a call to prepareMapPins. There is some ability to customise how a rendered pin looks so see the URL listed against the pinType constant, in the sample code, for more details.

I use ‘fetch’ to call the API with a generated URL/embedded query and then process the returned JSON data using the processMapData function. This is where the physical pins for POIs are added. Each POI discovered has a latitude/longitude, which is extracted in the form of an atlas.data.Feature (for each POI discovered). These are added to the map via a call to the addPins function, specifying the search layer to attach the pin to (so I’ve inferred here that you can indeed have multiple layers rendering different information which is awesome).

Some calculations are then performed to generate values to set the ‘camera’ location so that it focuses in on the area of the discovered POIs. All in all, it is actually pretty simple and is easy to get fully up and running within the first hour or so.

Lastly, a small mouseover event listener is added to provide a popup (using the previously created popup utility object) for each pin. The createMouseOverPopUps function takes care of this little monkey for us.

The only difficulty I had was that large padding values on the camera didn’t seem to play ball when using a small search radius, it took me a while to figure out that results were a) inaccurate when doing this and b) pins were overlapping and looked as if they were missing, so this is something to watch out for! Not sure why this knocked on to the pin locations, as it appears to be a camera setting. I’ve left this as 1, but a value of around 5 appeared to work fine.

So….what does it look like I hear you ask. Here’s the first results set for pubs which, for anyone who knows me, is most likely not a surprise! πŸ˜‰

Brewdog Location.

Brewdog Location.

The accuracy here, being from Norwich is….a little mixed. The location of Brewdog is near enough and another pin for Gonzos is on the mark, although the returned metadata lists this as ‘Havanas’, which is out of date. Some of the other listed POIs are flat out wrong (or omitted, perhaps as they are listed as ‘bars’ or ‘restaurants’, for example, even when I know they are in range based on radius). I did a follow-up search for hairdressers which seemed to be much more on the mark:

Anglian Hair Academy Map.

Anglian Hair Academy Map.

Anglian Hair Academy Street View.

Anglian Hair Academy Street View.

I had no idea that the Anglian Hair Academy even existed and thankfully my wife was there to set me straight, it’s in the right place apparently. From what I know, the other pins are pretty accurate (in this instance Google Maps looked a little out of date this time around). I tested this one last time on supermarkets in the area and it was reasonably accurate in the majority of cases.

This is an interesting little API to experiment with and please feel free to take this code and play around with it as you see fit. Also, please get in touch if the inaccuracies I saw here are due to some kind of error on my part, I’d love to hear how you all get on.

Thanks all and keep coding!

Looking for a Lighthouse

Happy New Year all and I truly hope you all enjoyed a terrific festive period.

As a little weekend treat, I decided to pick myself up a copy of the ‘net’ magazine, mainly due to the included feature articles on which web development and design tools are ‘en-flique’ (that one is for Claire; a trip to the urban dictionary is in order) for 2018. There are also some amazing looking articles discussing things like colour palette choices, and tools that assist in creating them, which should prove handy as I will be looking to potentially change up the Frog & Pencil website a little as I craft a fully functional CMS this year; something that has been well overdue.

As a quick aside, I’ve decided to check out Google’s own automated page analysis tool, Lighthouse. This can run performance and accessibility analysis on public or password protected sites. Information for getting started can be found here.

I’m opting to run this within Chrome development tools, but you can run this from the command line or as a Node module if you prefer (which allows you to hook this into a continuous integration setup, which could be incredibly useful).

Up to this point, I have been using YSlow as a web page dissection tool, but I’m happy to bust out an alternative to keep things interesting.

So, what kind of feedback does the tool provide and how does it present it? I’ll run this against the Frog & Pencil homepage and show you the results (no matter how bad they turn out, I’ll be honest!). When using Chrome you just need to inspect the audits tab, within Chrome development tools (accessed via F12 or Ctrl+Shift+I, on windows), to get up and running as follows:

Lighthouse via the Audits Tab.

Audits Tab.

On clicking ‘Perform an audit…’ you be presented with options as below (I’ll leave them all checked for this particular test run):

Audit Options for Lighthouse.

Audit Options.

The report is, on first inspection, very detailed and, as you can see, I have a fair bit of work to do (although I’m happy with the accessibility rating at least). The report is downloadable using the highlighted button:

Lighthouse Report Header.

Lighthouse Report Header.

The tests performed also appear to be more strict that the YSlow V2 test, which is nice to see:

YSlow V2 Test.

YSlow V2 Test.

There have been some surprising opportunities for improvement highlighted. I’ve long known that I should switch out the entire site to run over https and when the site is overhauled I intend to make better use of bundling for static files and will consider the use of a CDN. I have plenty of work to do with image compression also.

Here are a few things that really caught my attention:

1) How poor the site ran under simulated 3G speeds:

Simulated 3G Speeds

Simulated 3G Speeds.

2) The scale of the improvements still to be made by reducing render blocking scripts/stylesheets (a boo boo that I should really be covering) and image management:

Performance Improvement Opportunities.

Performance Improvement Opportunities.

3) The report highlighted that I was using libraries with known vulnerabilities and that I have left in code that was writing errors to the console (doh!):

Third Party Libraries.

Third Party Libraries.

This does bring into focus the core need of revisiting the website this year and giving it a thorough tune-up, as opportunities towards the end of last year were at a premium. All in all, if you’ve not used Lighthouse yet I would suggest giving it a look; especially as it takes seconds to run. I’ll be working my way through the highlighted areas in the report in the meantime!

All the best πŸ˜‰

Inspect Object Locking with T-SQL

I recently came across a scenario whereby we wanted to programmatically, via T-SQL, inspect locking behaviour on objects in a particular database when operations were being performed on given data (actually just a SELECT (WITH (NOLOCK) and DELETE statement, on a table). The requirement was to get a direct view on whether the table was having an exclusive lock thrust upon it, or whether shared access was still in play (and get an insight into whether we were in the realms of row-level locking or not).

This turns out to be relatively easy through the use of the inbuilt Dynamic Management Views; specifically the sys.dm_tran_locks view, coupled with a cheeky join to sys.objects for information to identify the object being referenced (this is my own interpretation, from scratch, but you can easily find many similar examples so pick what floats your boat!):

/*
	Interrogate the lock types at database level (can see the lock type on a table when another statement is running)
	-------------------------------------------------------------------------------------------------------------------

	1) For information on the different lock modes in SQL Server visit the following resources:
		a) https://technet.microsoft.com/en-us/library/ms175519(v=sql.105).aspx
		b) http://lockergnome.com/2010/01/26/what-are-the-different-lock-modes-in-sql-server/
		c) https://logicalread.com/sql-server-lock-modes-mc03/ - Haven't actually read this one yet but looked intriguing enough to include as a 'bonus' :o)
	2) An interesting aside into the WITH (NOLOCK) hint: http://sqlsoldier.net/wp/sqlserver/thetruthaboutnolockhints
	3) The difference by IX and X locks: http://database.ittoolbox.com/groups/technical-functional/sql-server-l/difference-between-x-and-ix-lock-151903
*/
SELECT 
tl.resource_type AS [ResourceType]
, CASE 
	WHEN tl.request_mode = 'Sch-S'
	THEN 'Schema Stability (ensure entity cannot be dropped)'
	WHEN tl.request_mode = 'IS'
	THEN 'Intent Shared'
	WHEN tl.request_mode = 'IX'
	THEN 'Intent Exclusive (exclusive lock to subordinate resource in the lock hierarchy - table can still be accessed in part)'
	WHEN tl.request_mode = 'X'
	THEN 'Exclusive (full, exclusive access to the resource)'
	ELSE tl.request_mode
END AS [LockType]
, so.[object_id] AS [ObjectId] 
, so.name AS [ObjectName]
, so.type_desc AS [TypeDesc]
FROM sys.dm_tran_locks tl
	-- JOIN to sys.objects to get identifiable information on the resource marked with a lock
	INNER JOIN sys.objects so ON tl.resource_associated_entity_id = so.object_id
-- Show lock information for the database in scope (this could be parameterised - this could be placed in an stored procedure, for example)
WHERE tl.resource_database_id = DB_ID();

In the comments above the code snippet, I’ve included a couple of links to detail the various lock types (information that can be shown in the request_mode view column) that you may encounter. Just run the query whilst a transaction that you want to inspect (for locking activity) is in progress, and voila, you’ll get an idea as to how the resources are being accessed and what impact this is having locking wise.

As a quick illustration let’s knock up a test database and table, using the following script which incorporates a very trivial setup:

-- Test script for checking out the dm_tran_locks test script

-- 1) Create the test database
CREATE DATABASE [LockTestDatabase];
GO

USE [LockTestDatabase];

-- 2) Create the LockTest table within the LockTestDatabase database (super noddy example for illustration only)
CREATE TABLE [dbo].[LockTest]
(
	Id				INT				PRIMARY		KEY	IDENTITY(1, 1)	NOT NULL
	, SomeInfo		NVARCHAR(20)									NOT NULL
);

-- 3) Create some simple test data
DECLARE
@Counter		INT = 1;

WHILE (@Counter <= 250000)		-- Want a non-trivial amount of data so queries take a couple of seconds to complete
BEGIN

	PRINT (@Counter);

	INSERT INTO [dbo].[LockTest]
	(
		SomeInfo
	)
	VALUES
	(
		'Information' + CONVERT(NVARCHAR(6), @Counter)
	);

	SET 
	@Counter += 1;

END;

The insertion of test records takes a short while so it’s possible to run the dm_tran_locks test script and obtain the following results:

LockTest Table Lock State during Insert.

LockTest Table Lock State during Insert.

After the test insertions are complete, attempt to run a SELECT statement using the WITH (NOLOCK) hint:

SELECT 
lt.Id
, lt.SomeInfo
FROM dbo.LockTest lt WITH (NOLOCK);

You should hopefully observe a shared resource lock, for schema stability only, that essentially just prevents the resource from being dropped whilst information is being requested from the table:

LockTest Table Lock State during SELECT (WITH (NOLOCK)).

LockTest Table Lock State during SELECT (WITH (NOLOCK)).

I’m not going into any kind of detailed explanation with regards to the lock types here, I’m just proving that observing the lock types being applied is easily possible. Lastly, attempt a deletion (wrapped in a rollback, so it can easily be re-run):

BEGIN TRANSACTION

	DELETE
	FROM dbo.LockTest;

ROLLBACK TRANSACTION

A lock is applied that indicates exclusive access to the resource (due to the nature of the delete here, as all rows are being removed), aka table:

LockTest Table Lock State during Deletion.

LockTest Table Lock State during Deletion.

This gives you an insight into how straightforward it is to get an impression on how your SQL objects are being influenced, locking wise, by your code. If anyone has any suggestions on further reading, more useful snippets, etc. then please add a comment below. Thanks, all.

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;