Expanding Hierarchies

Microsoft has expanded the information about expanding hierarchies in SQL Server Books Online (BOL) since SQL Server 6.x. But the BOL solution merely displays the output; you can’t join the results with other views, tables, or results. My T-SQL coding solution fills this gap. Also, the solution I present here is 5 to 6 times faster than the one provided by BOL for SQL Server 2000. In addition, I’ll show you useful SQL Server 2000 features such as templates, functions, and new table data types. One more thing I feel that it is difficult for SQL Programmers to understand the logic of LOOPS, IF structures and so on. So I present a flowchart, so you’ll have better understanding of the solution.

What Are Hierarchies?

DBAs deal with hierarchies every day, whether the hierarchy is an organizational chart, a file directory structure, geographically dispersed sale points, inventory, etc. When you map these hierarchies in databases, you either use redundant data, such as in an inventory table where you might have attributes such as No, Head, Level1, Level2, Level3,…, Leveln or use a self-referencing table (e.g., an Employees table having an attribute ReportsTo or ManagerID. The first example uses repeating groups, which don’t fall in the first normal form and hence don’t fall in higher normal forms, whereas the latter solution gives you a scalable, normalized solution, which contains no redundancy. But this is a complex solution. It means we have to expand the hierarchies; only then it can the solution be useful for us. Let’s consider a particular example from Books Online (BOL) under Expanding Hierarchies. Look at the indented view a parent-child relationship, which Figure 1 shows.

How can you expand these hierarchies by using SQL? The easy answer is to go to BOL and look at the code under Expanding Hierarchies. Listing 1 shows the code sample from BOL.

This code performs the following:

So far, so good. However, you can use the code that BOL gives only to produce output on the display. You can’t join the results other views, tables or results. In addition, you can improve the performance if you add non-leaf nodes to the #stack table as in step 6.b and insert leaf nodes directly to another temporary table called #hierarchy. The reason for the potential performance improvement is that if you look at any given hierarchical structure, you’ll find more leaves than branches, whether it’s a tree, a file directory structure or an inventory table, etc. To test this solution, I used data from my accounts receivables table, which encompasses almost 8000 rows and the response time was reduced to almost 15 percent compared with performance of the BOL technique. The BOL technique returned data in 35 seconds, and the technique I propose returned data in just 5 seconds.

Now, how can you return data from one stored procedure and use the data from a calling procedure? One possible solution is that your stored procedure might return data by using a select statement, and the calling procedure can use an INSERT SELECT statement to store the results in a table, as follows:

CREATE TABLE #temp(SerialNo int NULL,No int NULL, tName VARCHAR(255))INSERT INTO #temp EXEC ExpandHierarchy 5

We can even improve the code above by having the calling procedure create a temporary table, and the called procedure populates it. The calling procedure can have the following code:

CREATE TABLE #Holder(SerialNo int,No int, Head varchar(300))EXEC ExpandHierarchy 5SELECT * FROM #Holder

The code is looking fine, but there’s one more problem to fix: Users will have to remember the syntax to call the stored procedure. The good news is that if you’re using SQL Server 2000, you can use templates and then insert these templates into Query Analyzer. Write the following code in Query Analyzer, and from the File menu select Save As. Under Save as Type Select Template SQL files (*.tql):

CREATE TABLE #Holder(SerialNo INT,No INT, Head VARCHAR(300))EXEC ExpandHierarchy SELECT * FROM #Holder

Now, whenever you want to call this stored procedure, Select Insert Template from the Edit menu, select the template, and press Enter. Now you can join your existing queries with the #Holder table.

So, in the end you have a template that you can distribute to the developers. Let’s test our template. Suppose you want to take the balances of a branch, say 5, and its children from the Journal table. Listing 2 shows the code that does this. The code in Listing 3 shows the revised ExpandHierarchy Stored Procedure. Figure 2, which you can open by clicking Download the code in the Article Information box, shows the flowchart for this stored procedure.

I have another solution to present. SQL Server 2000 has among its many great features user-defined functions and the table data type. So, in SQL Server 2000 your function might look like the code that Listing 4 shows. On client side, you can simply write:

SELECT a.No,a.Head,a.SerialNo,SELECT (SUM(b.Dr-b.Cr) FROM Journal b WHERE a.NO=b.Ac_No) AS sumFROM funcExpandHierarchy(5) aORDER BY SerialNo

To test this solution, you can use the sample tables and data that Listing 5 shows.