Foreign Key Hierarchy Update

Categories: News, Professional, Scripts, SSC
Comments: 1 Comment
Published on: July 11, 2011

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:

[codesyntax lang=”tsql”]


and the following in the recursive definition.

[codesyntax lang=”tsql”]


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.

[codesyntax lang=”tsql”]


I hope you will play with this script, test it out and make recommendations or even find bugs with it and let me know.

Meme Monday – Horoscope

Categories: Meme Monday, News, Professional, SSC
Comments: No Comments
Published on: July 11, 2011

Did you know that your sign may have changed?  I didn’t – at least not until reading the meme Monday announcement.  Most people probably will see no difference in their astrological sign.  However, if you believe that there is a new all powerful 13th sign and if your sign is Scorpio – your sign probably changed.  In order to squeeze in that bakers dozenth sign, Scorpio got cut down to a whopping six days long.  In tribute to Scorpio, my meme Monday horoscope will be for that astrological sign.

You are a born performer, with an incredible transaction list to commit today.  A hard working instance, you tend to do your work with barely a spike in resources.  This is a great time to be clustered with others both local and geographically disperse.  This ability to run batches, run TSQL and always be available is the key to pleasing your DBA and avoiding the scrap heap.  There are DB systems (RDBMS and NOSQL alike) springing up all around you which always seem to challenge your status quo–be prepared.  You must decide whether the risk is worth the change it will bring about in your production environment.  New ways to alert or an easy interface will make monitoring and troubleshooting go well.  You may find yourself more resource taxed and burdened than usual. You are appreciated for your ability to multi-task.

page 1 of 1

July 2011
« Jun   Aug »


  • @SQLHammer: @DSFNet Not really. Its just data that you technically could copy out of msdb but I don't see any purpose to that. #sqlhelp
  • @DSFNet: #SQLHelp Does anyone know if you can store the Data-tier Application DAC definition in a database other than "msdb"?
  • @SQLSoldier: @thewolf66 There is no "correct num". It will vary depending on size of file and will be reset based on model on restart. #sqlhelp
  • @SQLSoldier: @live_sql Spider? SQL job to kill sessions in a sleeping state with a last_request_end_time > 2 hours ago in sys.dm_exec_sessions. #sqlhelp
  • @thewolf66: I've been doing some research on the correct num of VLF for TempDB log and I've found some mixed answer does anyone have opinion #sqlhelp
  • @live_sql: #sqlhelp anyone know how to implement a system that kills off spider after 2 hours of inactivity ? How would one automate ? Thanks
  • @zippy1981: @sqlstudent144 #sqlhelp How much PII is in the database? Would you let this dev have a backup of prod
  • @sqlstudent144: Is there any down side to granting VIEW SERVER STATE in PROD to a developer? #sqlhelp
  • @arvisam: @clay75_75 In the project properties you can switch 'target platform' to SQL2005 and it will immediately tell you what breaks. #sqlhelp
  • @ConanTheCdn: Is SSIS Packages written in VS 2008 (SQL2K8R2) that run from windows file system supported on Windows 2k12R2? #SQLHELP #SSISHELP

Welcome , today is Thursday, August 27, 2015