Versatile High Performance Hierarchies in SQL Server

Versatile High Performance Hierarchies in SQL Server

Page 1 of 3 - by Dennis W. Forbes

---

Introduction

Figure 1 : Inverted Tree

Figure 2 : Corporate Reporting Hierarchy

EmployeeID	FullName	Boss1ID	Boss2ID	Boss3ID	Boss4ID
1	Bob	Null	Null	Null	Null
2	Jane	1	Null	Null	Null
3	Joan	1	Null	Null	Null
4	Ken	2	1	Null	Null
5	Tony	2	1	Null	Null
6	Karen	4	2	1	Null
7	Paul	3	1	Null	Null
8	Howard	6	4	2	1
9	Tina	5	2	1	Null

Figure 3 : Multi-Referential Employee Table

EmployeeID	FullName	BossID
1	Bob	Null
2	Jane	1
3	Joan	1
4	Ken	2
5	Tony	2
6	Karen	4
7	Paul	3
8	Howard	6
9	Tina	5

Figure 4 : Adjacency List

CREATE FUNCTION dbo.GetReports(@IncludeParent bit, @EmployeeID int)
RETURNS @retFindReports TABLE (EmployeeID int, Name varchar(50), BossID int)
AS  
BEGIN 
	IF (@IncludeParent=1) 
	BEGIN
		INSERT INTO @retFindReports
		SELECT * FROM Employees WHERE EmployeeID=@EmployeeID
 	END

	DECLARE @Report_ID int, @Report_Name varchar(50), @Report_BossID int

	DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
	SELECT * FROM Employees WHERE BossID=@EmployeeID

	OPEN RetrieveReports

	FETCH NEXT FROM RetrieveReports
	INTO @Report_ID, @Report_Name, @Report_BossID

	WHILE (@@FETCH_STATUS = 0) 
	BEGIN
		INSERT INTO @retFindReports
		SELECT * FROM dbo.GetReports(0,@Report_ID)
  
		INSERT INTO @retFindReports
		VALUES(@Report_ID,@Report_Name, @Report_BossID)
   
		FETCH NEXT FROM RetrieveReports
		INTO @Report_ID, @Report_Name, @Report_BossID
	END
	
	CLOSE RetrieveReports
	DEALLOCATE RetrieveReports

	RETURN
END

Figure 5 : GetReports User Defined Function

A commonly modeled database design is the ubiquitous hierarchy: A relationship of items, all but the root having a single “parent”, with each having zero or more child nodes. Such a structure is often referred to as an “inverted tree” due to its visualized similarity to, not surprisingly, an inverted tree (Figure 1). Examples of such hierarchies include corporate reporting structures (Figure 2), geographical groupings, inventory tracking (such as when parts are assembled into components, which themselves might be combined into products, which might then become combined into shipping units, etc), auction listing categories, etc. The file system on our computers, a tree of folders and files, is a common example of such a hierarchical structure.

Unfortunately, hierarchies present a quandary when implemented within a data model: The most versatile, highly-relational designs can be complex and inefficient to query upon, while the more limited, hard-coded solutions offer short-term benefits, yet turn into a critical weakness as systems expand. This document gives some techniques to achieve the best of both worlds: A powerful method of creating simple, self-maintaining, versatile, relational hierarchical sets.

Hierarchical Data Models

There are several methods of implementing a hierarchy in a database. One common approach is to include references to each successively higher level in each record, with all of the levels sharing a single table. An example of this multi-level relational design in common use is the standard address: street, city, state, and country – All levels are often stored with each address entry, rather than having the record reference a street record, which itself links to a city record, and so on. In a corporate reporting structure such a solution could be implemented via a link in each person’s record to their supervisor, their supervisor’s supervisor, and so on (Figure 3), to the depth required given the vertical height of the organization's organizational chart. Such a technique allows for generally straightforward querying, such as the following query which computes the total salary of all of Jane’s reports.

SELECT Sum(Salary) FROM Employees WHERE Boss1ID=2 OR Boss2ID=2 OR Boss3ID=2 or Boss4ID=2

Such a structure is easy to query upon, however it has a large degree of redundancy--a change of hierarchical relationships can cascade across rows and columns in a complex manner-- and lacks flexibility--If additional levels were added it would require a schema change (i.e. the addition of columns). This isn't appropriate for anything but the most simplistic and static of hierarchies.

Figure 4 demonstrates another, simplified method of storing a hierarchy. This technique, often called an adjacency list, is a variation of the structure described in Figure 3, but without the redundant information-- Only the parent ID is stored, as successively higher levels can be determined by walking up the hierarchy programmatically. Such an approach offers versatility, and is an efficient choice for irregular (jagged) trees that go deep on some branches while remaining shallow on others, as the depth of the tree is not hard coded into the schema design. Whole branches can be moved by changing a parent relationship in a single record, and there is minimal space requirements imposed by the parent field used to create the relationships. However, adjacency lists can be complex to query in a hierarchical fashion without loads of self-joins or iterative loops: Answering a question such as "What is the total salary of Jane’s division?" can be a complex task, requiring the construction of temporary tables and costly recursive calls. Previously such a programmatic solution was difficult to implement in practice within SQL Server, however the task is made easier using the inline table-valued User Defined Functions functionality of SQL Server 2000.

Figure 5 demonstrates a user defined function (UDF) which takes two parameters: The first parameter, @IncludeParent, is a boolean value indicating whether to include the parent record in the result set, while the second parameter, @EmployeeID, is an integer identifying the employee whose subordinates we desire. This function returns a table containing all descendant records. This result set can be used for aggregate functions, such as the following query that returns the sum of the salaries for all reports under Jane’s direct or indirect command, excluding Jane herself.

SELECT sum(Salary) FROM dbo.GetReports(0,2)

Another common task would be to perform searches in branches of the tree. For example “I remember meeting a guy named Howard in Jane’s division…who was that guy?”

SELECT * FROM dbo.GetReports(0,2) WHERE FullName='Howard'

This function could be also used with the powerful IN condition for modifiable resultsets.

SELECT * FROM Employees WHERE EmployeeID IN (SELECT EmployeeID FROM dbo.GetReports(0,2))

SQL Server 2005 Update (2004-08-08)

SQL Server 2005, formerly codenamed Yukon, offers a simple T-SQL native mechanism for querying recursive sets - Recursive Common Table Expressions (Recursive CTE). CTEs could be considered a type of dynamic inline view, with the added benefit that they also offer self-referential functionality. Presuming that the adjacency table in Figure 4 were called "Employees", the following T-SQL would pull all employees reporting directly or indirectly to Jane.

DECLARE @boss_id int

SET @boss_id = 2; 

WITH CTE_Example (EmployeeID, FullName, BossID, Depth)
AS
(
	SELECT EmployeeID, FullName, BossID, 0 AS Depth
	FROM Employees WHERE EmployeeID = @boss_id
	UNION ALL
	SELECT Employees.EmployeeID, Employees.FullName, Employees.BossID, CTE_Example.Depth + 1 AS Depth FROM Employees
	JOIN CTE_Example ON Employees.BossID = CTE_Example.EmployeeID
)

SELECT * FROM CTE_Example

In no way does Recursive CTE functionality invalidate the string nested set technique: Under the covers a recursive CTE is simply some syntactical sugar that functionally performs largely the same operation as seen in Figure 5.

 

Note: SQL Server 2005 is edging close to completion. Read about how SQL Server 2005 will change your solutions.

Bob L:1 R:18
Jane L:2 R:13		Joan L:14 R:17
Ken L:3 R:8	Tony L:9 R:12	Paul L:15 R:16
Karen L:4 R:7	Tina L:10 R:11
Howard L:5 R:6

Figure 6 : Left and Right Bounded Hierarchies

EmployeeID	Name	BossID	SetString	Depth
1	Bob	Null	A	1
2	Jane	1	AA	2
3	Joan	1	AB	2
4	Ken	2	AAA	3
5	Tony	2	AAB	3
6	Karen	4	AAAA	4
7	Paul	3	ABA	3
8	Howard	6	AAAAA	5
9	Tina	5	AABA	4

Figure 7 : EmployeeSet Table with a String Coded Nested Set

CREATE TABLE [dbo].[EmployeesSet] (
	[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
	[FullName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Salary] [money] NULL ,
	[BossID] [int] NULL ,
	[StringSet] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Depth] [int] NULL 
) ON [PRIMARY]
GO

CREATE  CLUSTERED  INDEX [IX_StringSet] ON [dbo].[EmployeesSet]([StringSet]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[EmployeesSet] WITH NOCHECK ADD 
	CONSTRAINT [PK_EmployeesSet] PRIMARY KEY  NONCLUSTERED 
	(
		[EmployeeID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_Depth] ON [dbo].[EmployeesSet]([Depth]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[EmployeesSet] ADD 
	CONSTRAINT [FK_EmployeesSet_EmployeesSet] FOREIGN KEY 
	(
		[BossID]
	) REFERENCES [dbo].[EmployeesSet] (
		[EmployeeID]
	)
GO

Figure 8 : EmployeeSet DDL

CodeKey	CodeChar
1	A
2	B
3	C
4	D
...	...
26	0
27	1
28	2
..	..

Figure 9 : Code Table

SET ANSI_NULLS off
GO

CREATE PROCEDURE dbo.UpdateStringSet(@ParentEmployeeID int=null) AS

DECLARE @ParentDepth int, @ParentStringSet varchar(255)

SET @ParentDepth = null

SELECT @ParentDepth=Depth, @ParentStringSet=StringSet FROM EmployeesSet WHERE EmployeeID=@ParentEmployeeID

IF (@ParentDepth IS NULL) 
BEGIN
   SET @ParentDepth=0
   SET @ParentStringSet = ''
END

DECLARE @Offset int

SET @Offset = 0

DECLARE GetChildrenCursor CURSOR READ_ONLY STATIC LOCAL FOR
SELECT EmployeeID FROM EmployeesSet WHERE BossID=@ParentEmployeeID ORDER BY StringSet

OPEN GetChildrenCursor

DECLARE @EmployeeID int

DECLARE @NewStringSet varchar(255), @OldStringSet varchar(255)

FETCH NEXT FROM GetChildrenCursor
INTO @EmployeeID

WHILE (@@FETCH_STATUS = 0)  
BEGIN
   SELECT @NewStringSet=@ParentStringSet+CodeChar From Code Where CodeKey=@Offset
   
   UPDATE EmployeesSet SET Depth=@ParentDepth+1,@OldStringSet=StringSet, StringSet=@NewStringSet WHERE EmployeeID=@EmployeeID

   SET @Offset = @Offset + 1

   -- Recusively do the same for all children nodes if the string set has changed on this node
   IF (@OldStringSet <> @NewStringSet) BEGIN
      EXEC dbo.UpdateStringSet @EmployeeID
   END
   
   FETCH NEXT FROM GetChildrenCursor
   INTO @EmployeeID
END

CLOSE GetChildrenCursor
DEALLOCATE GetChildrenCursor
GO

Figure 10 : UpdateStringSet Stored Procedure

High Performance Hierarchies with Nested Sets

Another powerful technique for coding and querying hierarchical data, a hybrid of an adjacency list and the multi-level relational techniques previously discussed, is the “nested set”: By storing hierarchical hints with each record in the hierarchy, we can pull out branches and nodes using straightforward SQL WHERE conditions. The "nested set" approach offers tremendous advantages over both: It offers the speed and simplicity of the multi-level relational, with the schema independence of the adjacency list--The best of both worlds.

The common method of implementing such hierarchical hints is by using left-right record boundary hint, with parent nodes having bounds encapsulating the bounds of all child nodes, and so on. This is the technique advocated by Joe Celko in his excellent book SQL For Smarties, and is oft used as the reference implementation of powerful hierarchical designs. Figure 6 demonstrates such a design, with each record having an L (left) and R (right) value -- By selecting the left and right value for the desired parent record, one can select all child records by simply querying for records containing a left value greater than the selected left value and a right value less than the selected right value. While it offers a very powerful approach, it does have some downsides, primarily the complexity involved when modifying relationships in the hierarchy: It is a complex task to maintain the nested set information when nodes are moved, deleted, and inserted, so instead we’re going to examine an equally powerfully approach utilizing strings.

String-Based "Nested Sets"

SQL Server offers some powerful string comparison features such as LIKE. Coupling these with appropriate indexing allows for high performance "nested sets" (where we are bounding sets of data by textual markers) utilizing string keys to encapsulate hierarchical relationships in records. Figure 7 represents the adjacency list from Figure 4 with the addition of the columns SetString, a varchar column with case sensitive, accent sensitive collation (for example Latin1_General_CS_AS), and Depth, an integer column. Figure 8 includes the SQL DDL to create a table and set-up the appropriate indexes for speedy hierarchical searches. SetString, you will notice, is a string with each record having the value of its parent record, but with an incrementing additional character. Depth indicates the distance from the root (note that root in actuality is always an invisible “null” record, though in usage most users will have a single literal record as their functional root), and will always equal the length of the string held by the SetString field, though we maintain it as a separate field to allow for easy indexing. Such an approach allows for incredibly simplistic querying: To return a set of all of Jane’s underlings, both direct and indirect, one can run a simple query like the one following.

SELECT * FROM Employees WHERE SetString LIKE (SELECT SetString+’%’ FROM Employees WHERE EmployeeID=2)

With an index on the SetString, such a query is highly efficient. This could easily be modified to only include records within a certain depth range if desired. The follow query gets all of Jane’s direct reports, and reports of her direct reports (the two levels below her).

DECLARE @SetString nvarchar(255), @DepthTop int, @DepthBottom int

SELECT @SetString = SetString+’%’, @DepthTop=Depth+1, @DepthBottom=Depth+2 FROM Employees WHERE EmployeeID=2

SELECT * FROM Employees WHERE SetString LIKE @SetString and Depth >= @DepthTop and Depth < = @DepthBottom


Of course this could also be accommodated by using the functionality of the LIKE comparator.

DECLARE @SetString

SELECT @SetString = SetString FROM Employees WHERE EmployeeID=2

SELECT * FROM Employees WHERE SetString LIKE @SetString + ‘_’ OR SetString LIKE @SetString+’__’

In such an example only SetStrings with one or two additional characters beyond a matching initial set will be returned, achieving the same results.

 

Automatic Nested Sets

Generating the SetStrings field values to properly encapsulate the hierarchical relationships is a non-trivial operation. The first step is deciding on the code sequencing that we wish to use, as there may be a desire to use these coding patterns outside of the database (for example if used for employee organizational codes)--i.e. If there is a desire for them to be readable and memorable where necessary (a requirement which excludes us from using carriage returns or other system characters). The number of items that can share a common direct parent is dictated by the breadth of the coding pattern: In this case we’ve used a varchar--varchars use one byte, allowing 256 distinct values per character--because it can be matched and stored efficiently, though if one planned on having hundreds or thousands of elements with the same parent, an nvarchar could be used. So long as you created the StringSet field as a case-sensitive, accent-sensitive column, you can have both an upper and lower-case letter as separate character codes, as well as letters with varying accents. One could simply have an incrementing number that is converted to a char or nchar (using the functions CHAR() or NCHAR(), respectively), however for our sample we’ve set up a code table (see Figure 9), unsurprisingly named code . Because we've opted against iterating through all available character values, to control the code order and printability, we have limited the maximum number of sibling members (or nodes which share a single direct parent) to 36 in the instance that we use only upper case characters and numbers, though you could significantly increase that by adding lower case characters, punctuation, etc, changing the field to an nvarchar and utilizing unicode if the situation warranted it (i.e. very wide hierarchies).

Figure 10 demonstrates a stored procedure which, through recursive iteration, walks from a particular element (specified by the parameter @ParentEmployeeID) down the employee tree filling in the StringSet and Depth values. By calling this stored procedure with a parameter of null we can update the StringSet for every record from the root element(s) (if there is more than one element at the root, they are presumed to have a virtual unspecified parents, and to be siblings), and immediately we can do powerful and efficient hierarchical queries. Indeed, such queries could not only return branches of the `tree for direct use, but can be used as subqueries on other tables with the IN condition to generate sales aggregates, etc. To call this stored procedure on updates or inserts on a nested set enabled table would be a trivial operation.

Note: SQL Server 2005 is edging close to completion. Read about how SQL Server 2005 will change your solutions.

SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.UpdateStringSetStack(@ParentEmployeeID int=null, @MaxDepth int=64) AS

DECLARE @StartDepth int, @ParentDepth int, @ParentStringSet varchar(255)

SET @ParentDepth = null

SELECT @ParentDepth=Depth, @ParentStringSet=StringSet FROM EmployeesSet WHERE EmployeeID=@ParentEmployeeID

IF (@ParentDepth IS NULL) 
BEGIN
   SET @ParentDepth=0
   SET @ParentStringSet = ''
END

SET @StartDepth = @ParentDepth

DECLARE @CurrentDepth int
SET @CurrentDepth = @ParentDepth + 1

DECLARE @StackTable table(EmployeeID int, Depth int)
DECLARE @ValueTable table(Depth int, ParentStringSet varchar(255), Offset int)

DECLARE @SingleEmployeeID int, @SingleDepth int
DECLARE @NewStringSet varchar(255), @OldStringSet varchar(255)

INSERT INTO @StackTable(EmployeeID, Depth)
SELECT EmployeeID,@CurrentDepth FROM EmployeesSet WHERE BossID=@ParentEmployeeID

INSERT INTO @ValueTable(Depth, ParentStringSet, Offset)
VALUES(@CurrentDepth,@ParentStringSet,0)

DECLARE @Offset int
WHILE (@CurrentDepth > @StartDepth) 
BEGIN
   SELECT @Offset = Offset, @ParentStringSet=ParentStringSet FROM @ValueTable WHERE Depth = @CurrentDepth

   SET @SingleEmployeeID=NULL
   SELECT TOP 1 @SingleEmployeeID = EmployeeID FROM @StackTable WHERE Depth=@CurrentDepth
   
   IF (@SingleEmployeeID IS NOT NULL) 
   BEGIN
      UPDATE @ValueTable SET Offset = (@Offset + 1) WHERE Depth=@CurrentDepth
      SELECT @NewStringSet=@ParentStringSet+CodeChar From Code Where CodeKey=@Offset

      UPDATE EmployeesSet SET Depth=@CurrentDepth,@OldStringSet=StringSet, StringSet=@NewStringSet 
      WHERE EmployeeID=@SingleEmployeeID
 
      -- Remove this item from the stack, and then add its children records
      DELETE FROM @StackTable WHERE EmployeeID=@SingleEmployeeID

      IF (@NewStringSet <> @OldStringSet) 
      BEGIN
         SET @CurrentDepth = @CurrentDepth + 1
 
         IF ((@CurrentDepth - @StartDepth)>@MaxDepth) 
         BEGIN
            RAISERROR ('The maximum recursive levels of %d was exceeded building nested set',16, 1, @MaxDepth)
         END
         
         INSERT INTO @ValueTable(Depth,ParentStringSet,Offset)
         VALUES(@CurrentDepth,@NewStringSet,0)

         -- Compute the new code for this item based upon the parent.
         INSERT INTO @StackTable(EmployeeID, Depth)
         SELECT EmployeeID,@CurrentDepth FROM EmployeesSet WHERE BossID=@SingleEmployeeID
      END      
   END ELSE BEGIN
      -- We have exhausted this branch
      DELETE FROM @ValueTable WHERE Depth = @CurrentDepth
      SET @CurrentDepth = @CurrentDepth - 1
   END
END
GO

Figure 11 : UpdateStringSetStack Stored Procedure

SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.SingleStringSet @EmployeeID int AS

DECLARE @BossID int

SELECT @BossID = BossID FROM EmployeesSet WHERE EmployeeID=@EmployeeID

DECLARE @BossDepth int, @BossStringSet varchar(255), @OldStringSet varchar(255), @NewStringSet varchar(255), @OldDepth int

SET @BossDepth = 0
SET @BossStringSet = ''

SELECT @BossDepth=Depth, @BossStringSet=StringSet FROM EmployeesSet WHERE EmployeeID=@BossID

SELECT @OldStringSet=StringSet, @OldDepth=Depth FROM EmployeesSet WHERE EmployeeID=@EmployeeID

IF (ISNULL(LEFT(@OldStringSet,@BossDepth),'')<>@BossStringSet) OR (@BossDepth = 0) OR (LEN(@OldStringSet)<>(@BossDepth+1)) BEGIN
   DECLARE @NewCodeChar char(1)
   SELECT TOP 1 @NewCodeChar=CodeChar FROM Code WHERE CodeChar NOT IN 
   (SELECT ISNULL(SUBSTRING(StringSet,@BossDepth+1,1),'') FROM EmployeesSet 
   WHERE BossID=@BossID AND EmployeeID<>@EmployeeID)

   SET @NewStringSet = @BossStringSet+ISNULL(@NewCodeChar,'Z')

   BEGIN TRAN

   UPDATE EmployeesSet SET Depth=@BossDepth+1, @OldStringSet=StringSet, 
   StringSet=@NewStringSet WHERE EmployeeID=@EmployeeID

   IF (@OldStringSet <> @NewStringSet) 
   BEGIN
      EXEC UpdateStringSetStack @EmployeeID
   END

   COMMIT TRAN
END
GO

Figure 12 : SingleStringSet Stored Procedure

CREATE TRIGGER trg_UpdateHierarchy ON dbo.EmployeesSet 
FOR INSERT, UPDATE
AS

DECLARE IterateNewRecords CURSOR LOCAL STATIC  FOR
SELECT EmployeeID FROM inserted

DECLARE @EmployeeID int

OPEN IterateNewRecords 

FETCH NEXT FROM IterateNewRecords
INTO @EmployeeID

WHILE (@@FETCH_STATUS = 0) BEGIN
   EXEC SingleStringSet @EmployeeID

   FETCH NEXT FROM IterateNewRecords
   INTO @EmployeeID
END

CLOSE IterateNewRecords
DEALLOCATE IterateNewRecords

Figure 13 : EmployeeSet Trigger

Pulling It All Together - Automatically Maintained Nested Sets

While the stored procedure in Figure 10 powerfully enables the on-demand creation of nested sets, it is limited in that it relies upon recursion. Recursion is constrained by SQL Server limitations to a maximum of 32 recursive nested calls. While a 32-level hierarchy is large enough for most hierarchies, one should strive to avoid such arbitrary limitations[*] where possible, ensuring that crippling constraints aren't encountered on a fateful day in the future (usually on the same day that everything else that could go wrong does). As such a variant of Figure 10 which uses a table "stack" architecture instead of recursive calling is desired. Figure 11 shows just such a stored procedure. With this stored procedure the hierarchy depth is limited only by the space avaialable for the table variables (which generally will facilitate massive hierarchies).

The next step in the journey to high performance, reliable hierarchies is to make it automatic: When records are added or deleted, or relationships changed, the hierarchical information is automatically updated. To accommodate this requirement, we will first create a intermediary stored procedure: A bit of functionality that checks to see if a given record needs to have its nested set information (the StringSet and Depth fields) updated, and only then will it perform the potentially costly task of cascade updating the dependant records. Figure 12 demonstrates just such a procedure, verifying first that a given record no longer correlates hierarchicially with its parent, in such an insance calling UpdateStringSetStack to update the child records.

Now that the plumbing is all in place, all that remains is the addition of a trigger on our EmployeeSet table to automatically call SingleStringSet when necessary. Figure 13 demonstrates such a trigger, iterating throught the inserted virtual table and calling our intermediary stored procedure, SingleStringSet, to determine if more action is necessary. Observant readers will note that the trigger only operates on the INSERT and UPDATE events, ignoring the DELETE events - This is by design, as the appropriate self-referential foreign key relationship will ensure that no record can be deleted until the dependant records have been assigned a different parent record, eliminating the need to handle DELETE events as such records could only be leaf nodes.

 

Conclusion

While the examples and code in this paper have been specific, in this case to an employee hierarchy, such a design can be easily adapted to any other hierarchy type. By creating automatically maintained nested sets, you acquire the simplicity of versatile relational adjacency lists, with the speed and simplicity of nested sets: A win-win for any database.

* - Of course, we have imposed just such an arbitrary limitation by utilizing a coding table, limiting us to 36 possible siblings with a single parent if only upper-case characters and numbers are used; However that limit can be easily expanded (by adding elements to our code table we can expand out to 256 siblings using a varchar string, or 65536 with a nvarchar string), and is unlikely to become a practical limit in any case. i.e. a 4 level hierarchy (presuming a virtual root) with 36 items per parent offers up some 1.6 million nodes.

 

---

© 2003 - yafla