Common Table Expressions in SQL Server

Posted by in Sql Server category on for Intermediate level | Views : 10983 red flag

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.


My Solution

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.

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.

 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)) AS

BEGIN -- Add the SELECT statement with parameter references here

WITH CTESubordinates(Empid,FirstName,LastName,Manager) --Creating Common Table Expression (CTE)


( SELECT Empid,



    Manager FROM Employees WHERE Empid=@Empid


   SELECT E.Empid,




FROM Employees AS E JOIN CTESubordinates AS M ON E.Manager=M.Empid ) --INSERT the Resultset into the ReturnTable

INSERT INTO @TblSubOrdinates (Empid,FirstName,LastName)

SELECT Empid,FirstName,LastName FROM CTESubordinates




Code Explanations

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)

 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.
Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Muhammad Afzal Qureshi
Member Level: Bronze
Member Status: Member
Member Since: 8/4/2009 10:58:17 PM
Country: India
Hello Everyone Myself Muhammad Afzal , aka GoldyTech. Thats my pen name. I reside in India and work as a solution Architect on .NET platform. I hope you must have enjoyed reading my blog. Please leave your comments or suggestions good or bad and help me to improve

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)