On May 19, a very busy day for SQL Saturday events (Atlanta, Dallas, and New York in the US, and Kyiv, Catania and Belo Horizonte elsewhere), I will be speaking in Atlanta on a subject that I am becoming more and more interested in: Hierarchies. This is the abstract:
How to Implement a Hierarchy in SQL Server
One of the most common structures you will come across in the real world is a hierarchy (either a single parent “tree” or a multi-parent “graph”). Many systems will implement the obvious examples, such as a corporate managerial structure or a bill of materials. It turns out that almost any many-to-many relationship can be used as a hierarchy to find the relationship of parent rows to child rows (for example, the relationship of actors to movies they’ve been in). In this session, we’ll discuss several different implementations of hierarchies that you can use to optimize your hierarchy implementations, including SQL Server 2017’s graph tables, and put into practice immediately, with ready-made T-SQL examples.
This is a presentation I first gave a few years back, with the focus being kind of heavily into the ways to implement a tree structure. In the downloads for the presentation, I have code for multiple ways to implement a tree in SQL, from the simple adjacency method, using hierarchyid, and several other interesting methods that all have one or more benefits over another.
With graphs being more in the focus now that it is part of SQL Server 2017, I focus on how to build a graph, which are implemented and processed as a type of adjacency list. So I build up the process of implementing an adjacency list (in the same structure, you have the key of the table, and the referencing key), and then how that algorithm is applied and enhanced using graphs.
I am a big fan of the Atlanta event, first for the people who run it, and second for the themes they always pull. What will it be this year? I have NO idea, but I look forward to finding out!