How to use Common Table Expressions aka CTE in SQL Server 2005 to display hiarecheal data

Common Table Expressions in SQL Server 2005


In one of my recent project works, I was assigned to deliver the silverlight 2.0 solution to display Organization Chart.
I was required to represent the flat or relational data into a hierarchy.
Though there are number of traditional ways of achieving the same. I wanted to do something new which can increase the weightage of my decision to use Microsoft Technologies.
I had proposed SQL Server 2005 and .NET Framework 3.5 to the client. I am strong believer of the fact that if you are using a certain technology then makes the most out of it.
Try new ways of doing the things rather than doing it in plain old way. In this article, I shall not be talking about the complete solution, may be in near future I shall post another article for it. In this one I will concentrate on the backend solution, SQL Server 2005 feature to retrieve the data.
I shall introduce to new feature in SQL Server 2005 called Common Table Expression (CTE). A CTE is temporary result with a name that can be used in other expressions and code. It is more or less like an in-memory table that you can link to the other tables. The best thing I like about them is that they support recursion, though the CTE can also work without recursion. It allows us to perform powerful queries with minimal code. To know more about them you can read it here


I had taught of creating a SQL Server Table Valued User Defined Function which would return me the table consisting of the hierarchy data.
But before diving into the code of this function it is important that you get the understanding of the table and its data.
The figure 1 below represents the Employee table design.

Figure 1 Employee Table Design

Employee Table Structure

Employee Table Structure

The table structure is the self explanatory. Just one thing to keep in mind is the manager column which is foreign key pointing to the primary key Empid. This table is not part of my original solution. I have removed many of the columns to keep it short and simple. I have also added dummy records in this employee table for the purpose of the explanation. Figure 2 shows the data that is within this table.

Figure 2 Employee Table Data

Employee table data

Employee table data

My solution supports up to nth level of depth in hierarchy.
But in the data above I have shown up to the grand children level. E.g. see the record number with Empid 8 which is the grand child of Employee id 2 (Grand Parent: 2, Parent: 7, Child: 8).

So now enough of talking and let us jump into the code of the user defined function. The code of the function is appended below.


— Author: Muhammad Afzal aka GoldyTech
— Create date: 19-Jan-09
— Description: Gets the subordinates or child records based on Empid

ALTER FUNCTION fnGetSubordinates
-- Add the parameters for the function here
@EmpID int

RETURNS @TblSubOrdinates TABLE (EmpId INT, FirstName VARCHAR(50),LastName Varchar(50))
-- Add the SELECT statement with parameter references here
WITH CTESubordinates(Empid,FirstName,LastName,Manager) --Creating Common Table Expression (CTE)
WHERE Empid=@Empid


Employees AS E
CTESubordinates AS M
ON E.Manager=M.Empid

--INSERT the Resultset into the Return Table

INSERT INTO @TblSubOrdinates (Empid,FirstName,LastName) SELECT Empid,FirstName,LastName FROM CTESubordinates


After the function signature is defined it accepts the input parameter @Empid. The RETURNS statement tells the calling code that the function returns the table object with Empid, FirstName and LastName as its columns.
The table returned from this function can be used like any other normal table.
The interesting part of the code is the beginning of WITH statement.
This statement indicates the start of Common Table Expression (CTE).This code shows a recursive CTE.
A recursive CTE consists of two parts: the anchor member and the recursive member. In the above code the first select is the anchor member. When this function is executed, This Anchor SELECT statement is fired first. Then for each record in the resultset the recursive member is triggered and subsequently added to the resultset. This loop of recursion continues until no more records are encountered.

Let us now execute this function and see the results.

SELECT * from dbo.fnGetSubordinates(2)

Figure 3 Resultset of fnGetSubordinates

Resultset of fngetsubordinates

Resultset of fngetsubordinates


SQL Server 2005 comes with lot of interesting features which every developer must have insight of it.
This knowledge will help him out to solve the common day to day issues.
Again I repeat make the best use of the Technology to solve the business problems.


One thought on “How to use Common Table Expressions aka CTE in SQL Server 2005 to display hiarecheal data

  1. Pingback: How to work with Hierarchal data in Entity Framework « Getting Deep into .net

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s