Common Table Expressions in SQL Server

Introduction

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.

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.

 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.

)

 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)

AS

( SELECT Empid,

   FirstName,

    LastName,

    Manager FROM Employees WHERE Empid=@Empid

UNION ALL

   SELECT E.Empid,

      E.FirstName,

      E.LastName,

      E.Manager

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

RETURN

END

GO

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)

Conclusion