Associative Grouping using tSQL (Part 2)

In part one of this series we looked at how we could use recursive CTE’s toto find overlapping groups in two columns in a table, in order to put them into a new super group of associated groups. Since I wrote that post, SQL Server 2019 CTP 3.1 has been released, and with it comes some enhancements to the graph processing functionality, namely the new SHORTEST_PATH() function. To use this code yourself you will need to be using SQL Server Read more…

Associative Grouping using tSQL (Part 1)

Recently I was asked by a friend to have a look at an interesting query problem he had been looking at. He was trying to find overlapping groups in two columns, in order to put them into a new super group of associated groups. The simplest way to describe the problem is with an demo, so off we go… Demo Data In our scenario, we have a that company has grown through acquisitions, with each one bringing a new set Read more…

Azure Data Factory Lookup: First Row Only & Empty Result Sets

When using the lookup activity in Azure Data Factory V2 (ADFv2), we have the option to retrieve either a multiple rows into an array, or just the first row of the result set by ticking a box in the UI. This allows us to either use the lookup as a source when using the foreach activity, or to lookup some static or configuration data. Recently when connecting to a source system to retrieve configuration data, I discovered that the development Read more…

Signing Stored Procedures for Server Level Permissions

What’s the Problem Stored procedures are often used to allow users to access data in tables that they do not have explicit permissions to use. The mechanism that makes this possible is ownership chaining, whereby in the context of the stored procedure, the permissions of its owner are used (often dbo). This means users only have a limited set of explicit permissions (i.e. EXEC on the stored procedure), but are able to execute CRUD statements using the stored procedures. However, Read more…