THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Preparing for my Freecon Session

Deep breath. There is two weeks and two day before the PASS Summit. And like the past few years, I didn't submit any sessions, so those three days will be nonstop learning and chilling with all of the SQL family who makes it to Seattle.

But two weeks and one days from now is the "Seattle SQL Pro Workshop 2017", AKA the Seattle Freecon 2017. It is a one day event, with a slate of speakers that I would be honored to be listed amongst, even if it was just the list of people at the PASS Summit. They are: Andy Leonard, Jimmy May, Gail Shaw, Wayne Sheffield and Jason Brimhall. Nice.

There are sessions on SSIS, SQL Injection, Parameter Sniffing, Monitoring IO, and then my session, which is very much T-SQL coding oriented.

The abstract is here:


Implementing a Hierarchy in SQL Server

One of the more common data structures you will come across in the real world is a hierarchy, sometimes a single parent "tree" or more commonly a multi-parent "graph". Many systems will implement the obvious examples, such as a corporate managerial structure or a bill of materials, however, it turns out that almost any many-to-many relationship can be treated as a hierarchy (for example, a customer to the products they purchased, or the relationship of actors to movies they’ve been in). In this session, we’ll discuss several implementations of trees and graphs using interesting algorithms and built in T-SQL features (CTEs and SQL Graph) that you can use to optimize your hierarchy implementations and put into practice immediately (along with ready-made T-SQL example code.)


I have been interested in hierarchies since I attended Dr Rozneshtein's T-SQL training class back late last century. His book: The Essence of SQL : A Guide to Learning Most of SQL in the Least Amount of Time, was one of my earliest influences on writing excellent SQL, favoring set-based processing and limiting the number of passes you need to take through a set of data to provide the most performance, particularly in reporting type queries where you are processing a large amount of data. During the class, he gave us the start of a book he was working on, named "Tree and Graph Processing in SQL", and while I didn't do much with it back then, it started to foster interest in the subject.

Then a few years back, when I was writing my 2012 edition of my book, I wanted to expand my introduction of hierarchies, so I read a lot of current material on the subject, notably Joe Celko's "Joe Celko's Trees and Hierarchies in SQL for Smarties", some of Paul Nielsen's material in his last involvement in the SQL Server Bible series, and lot of online articles. From this, I was able to put down some conceptual material on several interesting ways to implement a hierarchy.  Not long after, I created a presentation and a lot of sample code to implement and test different methods of implementing tree hierarchies. Most of this presentation will be the same as it was back then (assuming the code works and I can remember how it still works, naturally :)).

What was a very minor part of the presentation last time was graphs. I implemented a simple graph using relational tables, using a recursive CTE for processing. I will extend that example using the new graph tables feature in SQL Server 2017, and extend the example to show the features that the graph tables give you.

Honestly, it is way too much to do in an hour, even if I didn't cover graphs, so I will economize as much as possible, but a load of code will accompany the download that you can download even if you don't make it to the Freecon from my presentation page: Of course, the code will be a lot easier if you attend!

If you failed to click the link earlier, go to the Seattle Freecon 2107 Eventbrite link now. (And before you cry "I thought you said free!", the training and snackage are free, there is a charge for lunch, which is not required but highly recommended…)

Published Monday, October 16, 2017 7:04 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


This Blog


Links to my other sites


Privacy Statement