Table Hierarchy updated

Recently a need resurfaced to explore the foreign key tree (hierarchy/genealogy) as it related to a specific table within a database.  As I pulled out the script from the repository, I realized there was some unfinished work to be done.  But there was also some polish that needed to be added.  This is an update to the most recent posting of that script.  You can see several revisions in the series at this link or the group here.

Some of the changes involve formatting and and labeling.  I added a new column called “Direction” to help understand the relationship of the key to the table in question.  I also changed up the FKGenealogy (formerly called SortCol) to reflect the source table more accurately in the case when the key comes from an ancestor rather than a descendant.  The Level of the FK was also modified to help understand a little better how far away the ancestor was in relationship to the origin table.

A final adjustment also comes from the Genealogy attribute.  Ancestors were all starting at the wrong point in the lineage.  I adjusted that so the lineage can be seen from the point in the tree that the ancestor is related rather than as a root direct from the origin table.

All in all, this script should make more sense to the user than the previous versions.

[codesyntax lang="tsql"]

[/codesyntax]

Table Hierarchy goes CS

Categories: News, Professional, Scripts, SSC
Comments: 1 Comment
Published on: November 16, 2011

At the urging of a friend, this script is being updated for those that are dealing with Case Sensitivity.  The first few rounds, I neglected Case Sensitivity and never tested for that.  It makes sense to have this script updated for that if anybody out there is using it.

The updates are simple enough, it is just frustrating if you run into an error caused by CS and then you waste time troubleshooting it.  Believe me, it has happened to me recently – and I don’t much like it.

Without further ado, here is the udpated script:

[codesyntax lang="tsql"]

[/codesyntax]

Still on the todo list is to make this bad boy run faster in the event of circular references.  If you find something else with it that you think could use adjusting, let me know.

FK Hierarchy v 2.1

Categories: News, Professional, SSC
Comments: 1 Comment
Published on: August 9, 2011

Last month I published an update to my Foreign Key Hierarchy script.  Today, I am providing a new update for that script.  A friend (Rémi Grégoire) helped out with some mods to this script.  The change for this month is nothing too intrusive.  The script is now updated for those databases that are Case Sensitive.

[codesyntax lang="tsql"]

[/codesyntax]

This update should make it more usable for any that may be using it or is interested in using it.  Thanks for Rémi for taking the time to propose this update.

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.

TSQL Sudoku

Comments: 8 Comments
Published on: August 17, 2011

I am a big Sudoku fan.  Typically if I need a break, I will break out a Sudoku puzzle from any of a number of different sources (Websudoku, Android Apps, Puzzle Books).  Over time, I have come across a solution here or there to solve these puzzles via TSQL.

There are a few of these solutions out there already, such as one by Itzik Ben-Gan (which I can’t get to download without the file corrupting so I still haven’t seen it), or this one on SSC (which works most of the time but does provide inaccurate results from time to time).  I still wanted something to do this via CTE (much like the solution by Itzik is described to be at the link provided – if you have that code, I want to SEE it).

Just a couple of years ago, there was a post at SSC asking for some help converting a solution from Oracle to TSQL.  I checked out that code and worked on it for a day or two.  Then I got busy with other work that replaced the pet project.  I hadn’t given the idea much thought until just a few days ago as I was browsing my Topic list I had been building for articles.

This solution stuck with me this time around and I wanted to finish it up.  The Oracle solution for whatever reason made a lot more sense to me this time around, and I made great progress quickly.  It was actually this project that I was working on that prompted another post.  While working through the solution, I learned a fair amount about both flavors of SQL.  So, in preface to continuing to read here, you may want to check out the other article real quick since it pertains to some of the conversions done in this project.

Problems First

The OP supplied the Oracle solution asking for help in creating a TSQL Solution.  Here is that Oracle version.

[codesyntax lang="sql"]

[/codesyntax]

If you read that other post I mentioned, you will quickly identify 5 functions/objects in use in this script that just don’t work in TSQL.  Those are:  dual, instr, substr, connect by, and trunc.  I did not mention mod in my other post, but mod is also done differently in TSQL than in Oracle.  I thought this one was a bit obvious and stuck with the top 5 ;).

Solution

After figuring out some of the subtle differences between commands and the best way to approach this, I was able to come up with a TSQL solution that works.  Take not first of that last where clause in the CTE of the Oracle solution.  That clause is very similar to what I refer to as the train-stop method to get unique paths in a hierarchy.  There are several methods to do similar functionality – I have concatenated strings with Stuff as wells cast to produce this functionality.

So here goes with the first rendition of this query.

[codesyntax lang="tsql"]

[/codesyntax]

Notice that I have chosen to use an Itzik style numbers table/CTE.  This functions as my “dual” table translation and is necessary in the remainder of the query.  The final where clause of the CTE is simplified in TSQL by simply removing the TRUNC commands.  The original solution was merely removing the decimal precision.  In TSQL, the conversion is done to INT implicitly in this case.  I need to test a few more cases, but so far it works without error.

What this does not do…

This is the first rendition of the script.  Currently, it only returns the number sequence in one big long string.  I am working on modifying this script to produce a grid layout with the solution.  I envision this will require the use of PIVOT and possibly UNPIVOT to get me close.  In addition, I expect that further string manipulation will be needed – such as stuffing commas and then splitting it to make the PIVOT/UNPIVOT easier.  I’ll have to try some things and figure it out.  Also, I expect that some explicit conversions may be best in this query.  That could help improve performance a tad.

This, to this point, has been a fun diversion.  This has helped to learn a bit about Oracle, hierarchies, and to do a little math – all in one.  Better yet is that there is still work to be done on it and more learning.  If you have ideas how to make it better – I am very interested.

Top 5 Oracle Nuances I learned Today

Categories: News, Professional, SSC
Tags: ,
Comments: 1 Comment
Published on: August 16, 2011

I don’t do much with Oracle – at all.  Once in a blue moon, I find a little project to do that might involve Oracle.  I have never put a lot of thought to the differences between SQL and Oracle.  On the pet project I am doing right now, I put a little more thought into those differences and finally decided to write a little something about five things I am working with in the Oracle world and how those translate (or at least how I translated them) to the SQL world.

Let’s start with some very similar commands.

  1. substr().  In SQL, this translates to substring.  Easy enough right?  There is one more difference between the two than just the name.  The parameters are ordered differently in substr() than they are in substring().   Pay careful attention to your parameter sequence when converting this function from Oracle to SQL Server.
  2. instr().  This one is less obvious.  I have used PatIndex() and CharIndex() for this one – depends on needed functionality.  If you understand that instr is searching for a value within a string – it makes it a little easier to understand.  Also knowing that PatIndex searches for “Patterns” and Charindex() searches for a character is helpful.  If you need to supply the optional parameter used by instr(), then you should use Charindex.  Though not entirely the same – similar functionality is available in SQL for the instr() function.
  3. trunc().  This is a function used in Oracle to convert date and numbers to a shorter format (either different date format or fewer decimal places).  This is achieved through different means in SQL.  Two common methods are cast() and convert().
  4. dual.  This is not a function.  This is an internal table containing a single row.  There are many uses for this internal table.  One common use is equivalent to the Numbers/Tally table in SQL server.  Pick your favorite numbers/tally table method in these types of cases.
  5. connect by.  This is actually a pretty cool piece of functionality unique to Oracle.  I have seen this used in recursive CTEs to help control the hierarchy.  In these cases, it limits the result set to rows meeting the criteria of the connect by statement.  Similar functionality can be achieved through use of Joins and the Where clause.  This is a command that would be really cool in SQL.  It is true that you can build the hierarchy without this command in SQL.  I think it would help make that task easier and give it more flexibility.  It would also make it a little easier to read/understand.

This is all pretty cool.  It should be pretty straight forward stuff for most DBAs.  Some day, maybe we’ll explore a post dedicated to connect by and how some of the features of that command can be translated into SQL.  For now, just know that there is some commonality between the two RDBMSs – just a little translation may be necessary.

T-SQL Tuesday #18 – CTEs

Comments: 3 Comments
Published on: May 10, 2011

To CTE or not to CTE, that is the Question

So my post really has nothing to do with the title.  This is not a post to help you determine whether a CTE is appropriate or not.  Or is it?

This month, we have the 18th installment in the TSQLTuesday series.  We are being hosted by Bob Pusateri  (Blog|@SQLBob) this month.  The essence of the topic this month is around CTEs (common table expressions).  There are a great many uses for a CTE in SQL server and this was a nice addition to the product.

One thing I like about CTEs is how much cleaner the code looks to me.  Another benefit for me is the recursion that is available through the use of a CTE.  An observation about CTEs is that a common use would be to use them to replace inline derived tables (which lends to cleaner looking code for some).

Think Think Think

I gave this topic a good long thought.  As I thought about the topic, I came to the conclusion that I had nothing new or unique on the subject.  I did however have some scripts that I posted once upon a time that would work very well for this topic.  Though it is a bit of a cop out, it is an appropriate solution for this month.

In the case of the CTE that I have chosen, there are multiple CTEs being used.  I use the CTE to recurse through data, and then to recurse that same data again – in reverse.  This particular script was created to traverse through system catalogs and create a hierarchy of table relationships.  I use this hierarchy to better understand the structure of the database and the interrelationship of the data between objects.  It is a cheap way of mapping out the objects in an effort to better understand it.

I had thought about using this script once upon a time for a different TSQLTuesday, but thought better of it that time.  Since the original post is more than a year old, it is a good time to bring it up and use it again.  Without further adieu, you can read about that script and CTE here.  I hope you enjoy.

Key Discovery III

Categories: News, Professional, Scripts, SSC
Comments: 2 Comments
Published on: February 1, 2010

In Part I and Part II of the series, I discussed documenting and discovering Primary Keys and Clustered Indexes.  In this article I will show how to document table relationships from a hierarchical standpoint.  The type of documentation I will demonstrate in this article will prove insightful and useful – if for nothing more than pure documentation.

I have had the pleasure of sharing this information in a User Group meeting.  That was the first time I had presented at a User’s Group meeting.  I gave that presentation shortly after hammering out the script to help document a database as a part of a project that is still on-going.  For the project, I first set out to find a script that fulfill the requirements for me.  All I could find were scripts similar to what I had already written.  They would present the Tables and foreign keys for those tables as well as the child tables.  The problem with this was that the list was in no particular order.  I then found another script that presented the data in an hierarchy – however it was not a true hierarchy.  I needed a script that could lay out the hierarchy of the foreign keys in the database when provided with one table from the database as the starting point.  This is how I define the perspective.  Now would be a good time to discuss the requirements I had defined for this script.

  1. Document Foreign Key Relationships
  2. Foreign Key Relationships listed in Hierarchical Format
  3. Create Perspectives (View of Hierarchy Tree from Specified Parent Table)
  4. No Cursors or While Loops

Why are these requirements necessary?  This is to help document a database.  I needed to be able to see the ERD at a reasonable size.  By adding the perspective requirement I could take a large database and reduce the number of objects displayed very quickly.  Furthermore, by using a script, I can create the documentation that displays the table relationships very quickly.  This information also helps in knowing the insert and delete order of data in the respective tables as they relate to one another.

The scripts I could find on the net were quickly eliminated due to the requirements.  Most used a looping mechanism, while others did not create a true hierarchy.  Thus I turned to my own information store to develop a script that could traverse the system views and create the kind of report that I needed.

To meet the requirements, I started looking to a CTE.  And yes a recursive CTE at that.  I know – not 100% set-based due to the recursion – but, it is very efficient for this purpose.  In scenarios such as this, this kind of solution is acceptable.  So, starting with the base query:

[codesyntax lang="sql"]

[/codesyntax]

This query gets me pretty close to the desired outcome and only needs a few more tweaks.  The above script more or less does the same sort of thing I saw other scripts doing that I found from internet searches.  The tables and foreign key level are displayed, but no linkage is quickly displayed by the query.  That can be resolved by adding a varbinary to the the query and then concatenating the value with each recursion.

[codesyntax lang="sql"]

[/codesyntax]

Ok, now the query is very close to what I need.  From the requirements, I only need to alter the script just a bit more in order to be able to create a perspective.  The perspective is achieved through variables and a slight change to the CTE.

[codesyntax lang="sql"]

[/codesyntax]

Through the above script, I am now able to achieve all of the requirements that had been pre-defined.  In testing the query, however, I discovered that the query was not yet quite complete.  There comes a time when there is an additional foreign key defined on the table that does not fit into the above query as a downstream perspective of the origin table.  There may be an occasion where an additional “parent” table needs to be displayed in the query.  Thus I must be able to traverse back upwards now in order to complete the perspective.  This was achieved through the following query.

[codesyntax lang="sql"]

[/codesyntax]

This query can easily be modified to pull back just the unique table names that are found in the CTE.  Why would that be necesary?  That would provide an easy method to pull back unique tables in the event that an ERD is to be derived from the perspective.  I didn’t delve too much into any of the scripts presented here.  At this point in the series, the only complexity that may need to be explained is the recursive piece of the scripts.  I think there are plenty of articles on that very subject.  The main goal here is to show that the documentation of FKs can be made relatively simple as well as provide plenty of insight into the database.  Test the queries.

I am including the scripts and the execution plan for the final script.  Included in the scripts will be the presentation given on the same subject.  The inclusion of that slide deck is a secondary reason for not getting into great detail in this post.  You can download it here.

The final blog in this particular series will be very short. I will cover the need for indexes on FKs. This is another topic that has been recently discussed. I will explore a couple of different scripts and compare and contrast those scripts performance.

page 1 of 1




Calendar
September 2014
M T W T F S S
« Aug    
1234567
891011121314
15161718192021
22232425262728
2930  
Content
SQLHelp

SQLHelp


Welcome , today is Tuesday, September 23, 2014