Speed up SQL script deployment

Problem
In SQL Server 2000 there is no simple way to create recursive queries that have several levels of data (hierarchical data).  Generally a recursive query is needed when you have a parent and child data stored in the same table.  One example may be employee data where all employee data is stored in one Employee table and there is an indicator that specifies the employees supervisor that points back to another record in the same table.  This is done quite often for other data as well. 

The following shows a sample table and data.

To query this data to find the employee and supervisor is pretty easy.  The query would be:

When we run this query these are the results we get:

So you can see this is pretty simple to just get this level of data, but what if you want to find out all of the direct and indirect reports of Bob Jones.  Well this now becomes a bit more difficult. In this example there are only 3 levels of data, but what if you have a much larger hierarchy?

Solution
As mentioned above there is no simple way to produce recursive queries that have several levels of data, but several implementations have been produced and are available on the internet.  Finding all of these options, understanding them, implementing and testing takes some time, so to make your life a little easier we have gathered a list of some useful variations on how to produce recursive queries in SQL Server 2000.

The following solution is pretty straight forward and offers a lot of flexibility.  Although it met my needs, take a look at the other options as well to see which one makes the most sense for your needs.

• http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

This is a list of various implementations and methods for creating recursive queries for SQL Server 2000. There are probably others out there as well.

• http://support.microsoft.com/default.aspx?scid=kb;en-us;q248915
• http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm
• http://www.sqlteam.com/item.asp?ItemID=8866
• http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sql03b8.asp
• http://www.sqlteam.com/item.asp?ItemID=1602

With all of these options the parentID is found and then there is a link to the child data and then that child's data etc...  One of the things that these articles also specify is creating a lineage of what records belong to which parent, grand-parent, etc...  This lineage then gets stored with the data so that when you are searching for records you can use the LIKE clause in your WHERE statement to find any records that have this value. 

Overall there is not a simple clean approach for dealing with this data, but using one or a combination of these techniques should allow you to reach your goal.

Note: With SQL Server 2005 you have the option of creating recursive queries using Common Table Expressions or CTEs. With CTEs, Microsoft has made this process a little easier then in previous versions of SQL Server.  Take a look at this post to learn more about CTEs and look for a future tip on how to use CTEs with SQL Server 2005.

Next Steps

• Take a look at these various methods for creating recursive queries
• Select a method that works best for your environment
• Modify these methods to implement the right solution for your needs

• Script to check that backup files still exist for SQL Server
• List columns and attributes for every table in a SQL Server database
• Splitting Delimited Strings Using XML in SQL Server
• Auto generation of UPDATE triggers for data auditing of your SQL Server tables
• Capture all statements for a SQL Server session
• More...

• Building a Data Mart with Integration Services

• Discuss this tip, post comments and feedback