Foreign Key Hierarchy Update

Today I would like to revisit a post of mine that is rather old. More precisely, the script in that post needs revisiting. This is one of my more favorite scripts and I still have more ideas to implement with it. The post/script in question can be found here.

In revisiting this script, I simplified it a bit. I also had to work on a problem with it that occurs in Hierarchies with circular dependencies. Quite frankly, that was a huge pain in the butt. There are some tricks out there to try and help with it – but I was having a hard time getting any of them to work in this scenario. I also updated the script to better handle self-referencing objects.

When you have circular references, and are trying to recurse the tree via a CTE, an instant blocker comes into play. You can only reference the anchor of the Recursive CTE once. Fixing a circular reference would be many times easier if you could reference the anchor twice.

In the end, the biggest hint to getting this to work came from this post. For it to work, I needed to find which combination of fields would work best. I finally settled on using the Object_ID to help reduce my pain. I settled on using the following in the anchor:

You can see that I am concatenating into a string for this column. This seems to work well for the purpose of eliminating those circular references.

Other adjustments to the script are not quite as significant but there is a small performance gain to be seen by these subtle changes. The most notable is the change to remove the two joins out to sys.columns in order to get the column names of the Parent and Child objects. In lieu of these joins, I am using the COL_NAME() function. This little change came to me thanks to a little work done last week on my statistics script that you can read here.

The final notable change comes in the naming of the CTEs in this script. I decided to rename the CTEs to something a bit more meaningful. In this case, Hierarchy and Ancestry are much more appropriate.

Without further adieu, here is the next major revision of that script.