Working with hierarchical data in SQL Server databases
Last
updated: October 29th '05 | Best viewed with: All popular
browsers | Best viewed at: 1024x768 | Links to external
sites will open in a new window
Working with hierarchical data in SQL Server databases
CREATE TABLE dbo.Emp ( EmpID int PRIMARY KEY, EmpName varchar(30), MgrID int FOREIGN KEY REFERENCES Emp(EmpID) ) GO
Notice that, EmpID is decalred as a primary key, and the MgrID column is declared as a foreign key constraint, that references the EmpID column of the same table, that is, a self referencing table. This is so, because all employees and managers are stored in the same table.
Since EmpID is declared as a primary key, by default it will be implemented as a unique clustered index. Now, let's create a non-clustered index on MgrID column, to improve the query performance:
CREATE NONCLUSTERED INDEX NC_NU_Emp_MgrID ON dbo.Emp(MgrID) GO
Let's populate the employee table with some data:
INSERT dbo.Emp SELECT 1, 'President', NULL INSERT dbo.Emp SELECT 2, 'Vice President', 1 INSERT dbo.Emp SELECT 3, 'CEO', 2 INSERT dbo.Emp SELECT 4, 'CTO', 2 INSERT dbo.Emp SELECT 5, 'Group Project Manager', 4 INSERT dbo.Emp SELECT 6, 'Project Manager 1', 5 INSERT dbo.Emp SELECT 7, 'Project Manager 2', 5 INSERT dbo.Emp SELECT 8, 'Team Leader 1', 6 INSERT dbo.Emp SELECT 9, 'Software Engineer 1', 8 INSERT dbo.Emp SELECT 10, 'Software Engineer 2', 8 INSERT dbo.Emp SELECT 11, 'Test Lead 1', 6 INSERT dbo.Emp SELECT 12, 'Tester 1', 11 INSERT dbo.Emp SELECT 13, 'Tester 2', 11 INSERT dbo.Emp SELECT 14, 'Team Leader 2', 7 INSERT dbo.Emp SELECT 15, 'Software Engineer 3', 14 INSERT dbo.Emp SELECT 16, 'Software Engineer 4', 14 INSERT dbo.Emp SELECT 17, 'Test Lead 2', 7 INSERT dbo.Emp SELECT 18, 'Tester 3', 17 INSERT dbo.Emp SELECT 19, 'Tester 4', 17 INSERT dbo.Emp SELECT 20, 'Tester 5', 17 GO
Notice that the MgrID of President is NULL, that means, he is the super boss and has nobody managing him. As you can see, rest of the employees are linked to their respective managers using the MgrID column.
Now, let's create a stored procedure, that traverses this employee hierarchy recursively and displays the employees in the form of an indented tree structure. The following stored procedure has an input parameter named Root, that takes the ID of the employee (equivalent to the ID of a node in a tree) and displays all the employees managed by him and his sub-ordinates.
CREATE PROC dbo.ShowHierarchy
(
@Root int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @EmpID int, @EmpName varchar(30)
SET @EmpName = (SELECT EmpName FROM dbo.Emp WHERE EmpID = @Root)
PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpName
SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root)
WHILE @EmpID IS NOT NULL
BEGIN
EXEC dbo.ShowHierarchy @EmpID
SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root AND EmpID> @EmpID)
END
END
GO
While creating the above stored procedure, you will receive the following warning:
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'ShowHierarchy'. The stored procedure will still be created.
Nothing to worry about. It's a just a warning, as SQL Server is a little confused about the recursive stored procedures.
As you can see, the above stored procedure calls itself recursively. You should be aware of a limitation imposed by SQL Server though. A stored procedure can nest itself upto a maximum of 32 levels. If you exceed this limit, you will receive the following error:
Server: Msg 217, Level 16, State 1, Procedure sdss, Line 1
Maximum stored procedure nesting level exceeded (limit 32).
Now let's run this procedure by passing the IDs of different employees and look at the output:
--Complete hierarchy EXEC dbo.ShowHierarchy 1 GO ---President ------Vice President ---------CEO ---------CTO ------------Group Project Manager ---------------Project Manager 1 ------------------Team Leader 1 ---------------------Software Engineer 1 ---------------------Software Engineer 2 ------------------Test Lead 1 ---------------------Tester 1 ---------------------Tester 2 ---------------Project Manager 2 ------------------Team Leader 2 ---------------------Software Engineer 3 ---------------------Software Engineer 4 ------------------Test Lead 2 ---------------------Tester 3 ---------------------Tester 4 ---------------------Tester 5 --From Group Project Manager onwards EXEC dbo.ShowHierarchy 5 GO ---Group Project Manager ------Project Manager 1 ---------Team Leader 1 ------------Software Engineer 1 ------------Software Engineer 2 ---------Test Lead 1 ------------Tester 1 ------------Tester 2 ------Project Manager 2 ---------Team Leader 2 ------------Software Engineer 3 ------------Software Engineer 4 ---------Test Lead 2 ------------Tester 3 ------------Tester 4 ------------Tester 5 --From Project Manager 1 onwards EXEC dbo.ShowHierarchy 6 GO ---Project Manager 1 ------Team Leader 1 ---------Software Engineer 1 ---------Software Engineer 2 ------Test Lead 1 ---------Tester 1 ---------Tester 2
The above is just a simple example of traversing hierarchies. There's more to hierarchies than just traversing, that is, adding to, deleting from and modifying hierarchies. The following links deal with hierarchies in greater detail and propose different methodologies for managing hierarchies efficiently.