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"]

[/codesyntax]

and the following in the recursive definition.

[codesyntax lang="tsql"]

[/codesyntax]

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"]

[/codesyntax]

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
Tags:
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




Calendar
July 2011
M T W T F S S
« Jun   Aug »
 123
45678910
11121314151617
18192021222324
25262728293031
Content
SQLHelp

SQLHelp


Welcome , today is Tuesday, July 22, 2014