microsoft sql server 2005 cte query

8/7/2019 Microsoft SQL Server 2005 CTE Query

1/14

Microsoft SQL Server 2005 - CTE Example of a simple hierarchy

I have a simple table here which shows a list of devices and each device can have a parent device except for the top itemwhich has a value for the ParentID column.

Using SQL 2005 CTE (Common Table Expression) to render this full hierarchy we could use a simple query such as this:


2/14

What this CTE is doing is allowing us to run a hierarchical query.

We first off find the root of our tree, which in this case is referenced by a NULL value in the ParentID column.

SELECT ParentID, ID,0, [DeviceType], cast(DeviceType as nvarchar(1024))FROM DeviceType

WHERE ParentID IS null

This technically returns the same details as row #1 in the above graphic.

What the UNION ALL does is executes a query with a join against the CTE virtual table.

So we technically are sort of doing this:

Create Virtual Table with the following fields:

ParentID

ID,

Level,

DevieType,

Sort

This line inserts one row into the virtual table, the ID in this case will be 1 because that was the ID in our DeviceType tableof the record with the ParentID ofNull.

SELECT ParentID, ID,0, [DeviceType], cast(DeviceType as nvarchar(1024))

FROM DeviceTypeWHERE ParentID IS null

Now we run the following query on the virtual table until every row is processed in the virtual table.

Note: This select is really like an insert into the virtual table. each row returned in the select is appended to our virtual table.

SELECT dt.ParentID, dt.ID, level+1, dt.DeviceType, cast(Sort + '|'+dt.Devicetype as nvarchar(1024))FROM DeviceType dt INNER JOIN hierarchy h ON dt.ParentID = h.ID

So the first time it is run we see that h.ID would be equal to 1 which was the ID of the record with ParentID ofNull. Ifthere was any records matching this select statement they are added to the virtual table and this continues until all of thevirtual table rows have been processed.

At the end of it we have a Virtual table that we can then do what we want with such as join it to another table or just returnthe data, in our case we run this sql statement.

select ID, ParentID, DeviceType, LEVEL, Sort

from Hierarchy

order by sort

But what happens if I have an record and want to find the parent hierarchy for it?

The following query will allow us to return the parent hierarchy for the item Virtual Server Hosts which has anID of 10.

Note: There are problems with the results of this query!


3/14

Can you see the problem with the results?

We have correctly returned the parents starting from the Virtual Server Hosts and walked up the tree until we get to theroot node. But if you look at the Levelfield it is not actually containing the correct value as the Virtual Server Hosts fieldsshould be at level 3 and not at level 0.

Why is this?

Well we can't determine at what level the Virtual Server Hosts is at when we start creating the virtual table so we just hardcode it to 0.

The problem also is that if you wanted to render this out to say a web browser using the following hierarchy we would haveto navigate through our records backwards.

Object

o Server

Virtual Servers

Virtual Server Hosts

How to fix this!

What we can do in this case is run a second CTE joined to the first CTE because each CTE is just like a virtual table. When thefirst CTE has completed processing we are basically left with a table with records in it like below.

Our second CTE now just references the records in the first CTE as if it was a standard table.


4/14

As you can see in the query in the above graphic our second CTE starts by looking for a record which has the ParentID

being set to NULL.

This will return our real parent as the first record to be inserted into our second virtual table.

Now we run the following query on the second virtual table until every row is processed in the virtual table. Like the firstvirtual table, the select appends records to our virtual table and these records get processed until all records have beenprocessed.

SELECT h.ParentID, h.ID, hr.LEVEL+1, h.DeviceType, cast(hr.Sort + '|'+h.sort as nvarchar(1024))

FROM Hierarchy h INNER JOIN HierarchyReverse hr ON h.ParentID = hr.ID

We have changed the join in the second CTE to join from the ParentID --> ItemID, which is the opposite of the first CTEwhich joined the ItemID --> ParentID. This allows us to navigate down the tree instead of up the tree.

Confusing? hopefully not....

What we end up with is this:

Now you may be wondering what this Sort column is and why we have it at all?

The reason is that we need to have something that is sortable, using the DeviceType is not since it would corrupt ourhierarchy by giving us A-Z, the same goes for level, 0-9

What we do is we create a field that contains the hierarchy so far and then add the DeviceType to the end of it. This makes asortable field that will not corrupt our hierarchy but give us A-Z sorting within each level of the hierarchy.

Notice the problem with this?


5/14

Well I have made a decision that all of my hierarchies of DeviceType will not exceed 1024 characters by casting the valueswith the statement : NVARCHAR(1024)

Well we could use a NVARCHAR(MAX) and the problem is resolved, but this is dependant on your knowledge of theapplication.

Hopefully this has shown you and me how the CTE's work while they are running - well it looks like that to me and it makesme understand it a bit easier as they can be hard to picture sometimes.

Table of Contents Hierarchy

set nocount on

declare @Sample1 table

(RecordID int Primary key NOT NULL ,

ParentRecordID int,SortOrder int,

Description nvarchar(100),Salary money

)

/* Start loading of test data */insert into @Sample1 values(1,null,null,'CEO',10)

insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)

insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)

insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)insert into @Sample1 values(7,4,1,'Human Resources Director',4)

insert into @Sample1 values(8,4,2,'Some other item',3)insert into @Sample1 values(9,6,1,'Research Analyst',2)

set nocount off;

with RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary,TOC)

as(

select RecordID,ParentRecordID,SortOrder,Salary,convert(varchar(100),'') TOC

from @Sample1where ParentRecordID is null

union allselect R1.RecordID,

R1.ParentRecordID,R1.SortOrder,

R1.Salary,

case when DataLength(R2.TOC) > 0then convert(varchar(100),R2.TOC + '.'

+ cast(R1.SortOrder as varchar(10)))

else convert(varchar(100),cast(R1.SortOrder as varchar(10)))

end as TOCfrom @Sample1 as R1

join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID)

select * from RecursionCTE order by ParentRecordID,SortOrder asc

Results


6/14

RecordID ParentRecordID SortOrder Salary TOC----------- -------------- ----------- -----------------1 NULL NULL 10.002 1 1 9.00 13 1 2 8.00 26 1 3 5.00 34 2 1 7.00 1.15 2 2 6.00 1.2

7 4 1 4.00 1.1.18 4 2 3.00 1.1.29 6 1 2.00 3.1

Sum Up Subordinate Salaries of All Employeesset nocount ondeclare @Sample1 table(

RecordID int Primary key NOT NULL ,ParentRecordID int,SortOrder int,

Description nvarchar(100),Salary money

)/* Start loading of test data */insert into @Sample1 values(1,null,null,'CEO',10)insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)insert into @Sample1 values(7,4,1,'Human Resources Director',4)insert into @Sample1 values(8,4,2,'Some other item',3)insert into @Sample1 values(9,6,1,'Research Analyst',2)

set nocount off;

with RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary)as(select RecordID,ParentRecordID,SortOrder,Salary



R1.ParentRecordID,R1.SortOrder,

R1.Salaryfrom @Sample1 as R1join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID

)select sum(R1.salary) as Salaryfrom @Sample1 as R1JOIN RecursionCTE as R2on R1.RecordID = R2.RecordID

Results

Salary---------------------54.00

(1 row(s) affected)


7/14

Sum Up Subordinate Salaries of a Specific Employeeset nocount ondeclare @Sample1 table(

RecordID int Primary key NOT NULL ,ParentRecordID int,SortOrder int,

Description nvarchar(100),Salary money)

/* Start loading of test data */insert into @Sample1 values(1,null,null,'CEO',10)insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)insert into @Sample1 values(7,4,1,'Human Resources Director',4)insert into @Sample1 values(8,4,2,'Some other item',3)insert into @Sample1 values(9,6,1,'Research Analyst',2)

set nocount off;

with RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary)as(select RecordID,ParentRecordID,SortOrder,Salary

from @Sample1where ParentRecordID =2 -- specific employee id


R1.ParentRecordID,R1.SortOrder,R1.Salary

from @Sample1 as R1join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID

)select sum(R1.salary) as Salaryfrom @Sample1 as R1JOIN RecursionCTE as R2on R1.RecordID = R2.RecordID

Results:

Salary---------------------20.00

Manager to Subordinate Salary Differential

set nocount ondeclare @Sample1 table(

RecordID int Primary key NOT NULL ,ParentRecordID int,SortOrder int,Description nvarchar(100),Salary money

)

/* Start loading of test data */insert into @Sample1 values(1,null,null,'CEO',10)insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)


8/14

insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)insert into @Sample1 values(7,4,1,'Human Resources Director',4)insert into @Sample1 values(8,4,2,'Some other item',3)insert into @Sample1 values(9,6,1,'Research Analyst',2)

set nocount off;

with RecursionCTE (RecordID,ParentRecordID,SortOrder,ParentSalary,Salary,Differential)as(select RecordID,ParentRecordID,SortOrder,

convert(money,null) as ParentSalary,Salary,convert(money,null) as Differential



R1.ParentRecordID,

R1.SortOrder,convert(money,R2.Salary) as ParentSalary,R1.Salary,convert(money,R2.Salary - R1.Salary) as Differential

from @Sample1 as R1join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID

)

select * from RecursionCTE order by ParentRecordID,SortOrder asc

RecordID ParentRecordID SortOrder ParentSalary Salary Differential----------- -------------- ----------- --------------------- --------------------- ---------------------1 NULL NULL NULL 10.00 NULL2 1 1 10.00 9.00 1.003 1 2 10.00 8.00 2.006 1 3 10.00 5.00 5.004 2 1 9.00 7.00 2.005 2 2 9.00 6.00 3.007 4 1 7.00 4.00 3.008 4 2 7.00 3.00 4.009 6 1 5.00 2.00 3.00

Common Table Expressions (CTE) in SQL Server 2005

IntroductionWhen crafting a query in SQL, there are often times when we may need to operate over a set of data that

doesn't inherently exist within the system. For example, the database for an eCommerce web application wouldhave the standard tables - Products, Customers, Orders, OrderDetails, and so on - but we may need to run

reports on a particular subset of the data or against aggregate data across these tables. Or the reportingqueries we need might need to group or filter by results returned by scalar subqueries. Typically,views are

used to break down complex queries into digestible chunks or to provide scalar subquery results that can begrouped and filtered. Views, however, are sometimes overkill, as they are permanent objects at the system-

level. If we only need to reference this complex query in a single stored procedure or UDF, another option is touse a derived table. Unfortunately, derived tables muddle the readability of the query and must be repeated for

each use in a statement.

Common Table Expressions, or CTE, are a new construct introduced in Microsoft SQL Server 2005 that offer a

more readable form of the derived table that can be declared once and referenced multiple times in a query.Moreover, CTEs can be recursively defined, allowing a recursive entity to be enumerated without the need for
http://www.odetocode.com/Articles/299.aspxhttp://www.odetocode.com/Articles/299.aspxhttp://www.4guysfromrolla.com/webtech/112098-1.shtmlhttp://www.odetocode.com/Articles/299.aspxhttp://www.4guysfromrolla.com/webtech/112098-1.shtml


9/14

recursive stored procedures. In this article we'll examine the benefits, uses, and syntax of both recursive andnon-recursive CTEs. Read on to learn more!

Obtaining the Northwind Traders Database for SQL Server 2005

The examples used in this article are all operating against the Northwind

Traders database, which has a simple eCommerce schema (Products,Customers, Orders, Order Details, and so on). Originally, the Northwind

database was released for Microsoft Access. A version for SQL Server 2000was later provided. Microsoft has not posted an "official" Northwind databasefor SQL Server 2005. However, you can create a SQL Server 2005 databaseand then import the schema and data from the create scripts provided in theSQL Server 2000 version download. Or, even better yet, you can simplydownload the Northwind database files from the list below for SQL Server2005 and bypass having to create this database yourself.

A Simple Common Table Expression ExampleBefore we dive into the syntax or gritty details of CTEs, let's start by looking at a simple example. I think you'll

agree that even without knowing the syntax of CTEs, they are pretty readable and straightforward (thehallmark of a well-designed programming language construct).

WITH ProductAndCategoryNamesOverTenDollars (ProductName, CategoryName, UnitPrice) AS

(SELECT

p.ProductName,c.CategoryName,

p.UnitPriceFROM Products p

INNER JOIN Categories c ONc.CategoryID = p.CategoryID

WHERE p.UnitPrice > 10.0)

SELECT *

FROM ProductAndCategoryNamesOverTenDollarsORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC

This query creates a CTE named ProductAndCategoryNamesOverTenDollars that returns the name, categoryname, and price of those products whose unit price exceeds $10.00. Once the CTE has been defined, it mustthen immediately be used in a query. The query treats the CTE as if were a view or table in the system,returning the three fields defined by the CTE (ProductName, CategoryName, and UnitPrice), orderedalphabetically by category, then by price, and then alphabetically by product name.

In short, a Common Table Expression allows us to define a temporary, view-like construct. We start by(optionally) specifying the columns it returns, then define the query. Following that, the CTE can be used in aSELECT, INSERT, UPDATE, or DELETE statement.


10/14

Common Table Expression SyntaxA Common Table Expression contains three core parts:

The CTE name (this is what follows the WITH keyword)

The column list (optional)

The query (appears within parentheses after the AS keyword)

The query using the CTE must be the first query appearing after the CTE. That is, you cannot do the following:

WITH ProductAndCategoryNamesOverTenDollars (ProductName, CategoryName, UnitPrice) AS(

SELECTp.ProductName,

c.CategoryName,p.UnitPrice

FROM Products pINNER JOIN Categories c ON

c.CategoryID = p.CategoryIDWHERE p.UnitPrice > 10.0

)

SELECT *

FROM Products

SELECT *

FROM ProductAndCategoryNamesOverTenDollarsORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC

The ProductAndCategoryNamesOverTenDollars CTE only applies to the first query following it. So when thesecond query is reached, ProductAndCategoryNamesOverTenDollars is undefined, resulting in an "Invalid objectname 'ProductAndCategoryNamesOverTenDollars'" error message.

You can, however, define multiple CTEs after the WITH keyword by separating each CTE with a comma. Forexample, the following query uses two CTEs. The subsequent SELECT query then uses an INNER JOIN to match

together the records from the two CTEs:

WITH CategoryAndNumberOfProducts (CategoryID, CategoryName, NumberOfProducts) AS(

SELECTCategoryID,CategoryName,(SELECT COUNT(1) FROM Products pWHERE p.CategoryID = c.CategoryID) as NumberOfProducts

FROM Categories c),

ProductsOverTenDollars (ProductID, CategoryID, ProductName, UnitPrice) AS(

SELECTProductID,CategoryID,ProductName,UnitPrice

FROM Products pWHERE UnitPrice > 10.0

)

SELECT c.CategoryName, c.NumberOfProducts,p.ProductName, p.UnitPrice

FROM ProductsOverTenDollars pINNER JOIN CategoryAndNumberOfProducts c ON

p.CategoryID = c.CategoryID

ORDER BY ProductName


11/14

Unlike a derived table, CTEs can be defined just once, yet appear multiple times in the subsequent query. Todemonstrate this, consider the following example: the Northwind database's Employees table contains an

optional ReportsTo column that, if specified, indicates the employee's manager. ReportsTo is a self-referencingforeign key, meaning that, if provided, it refers back to another EmployeeID in the Employees table. Imagine

that we wanted to display a list of employees including how many other employees they directly managed. Thiscould be done using a simple, CTE-free SELECT statement, but let's use a CTE for now (for reasons which will

become clear soon):

WITH EmployeeSubordinatesReport (EmployeeID, LastName, FirstName, NumberOfSubordinates,ReportsTo) AS(

SELECTEmployeeID,

LastName,FirstName,(SELECT COUNT(1) FROM Employees e2WHERE e2.ReportsTo = e.EmployeeID) as NumberOfSubordinates,ReportsTo

FROM Employees e)

SELECT LastName, FirstName, NumberOfSubordinatesFROM EmployeeSubordinatesReport

This query will return the employees records, showing each employee's last and first name along with howmany other employees they manage. As the figure below shows, only Andrew Fuller and Steven Buchanan aremanager material.


12/14

Now, imagine that our boss (Andrew Fuller, perhaps) comes charging into our office and demands that thereport also lists each employee's manager's name and number of subordinates (if the employee has amanager, that is - Mr. Fuller is all to quick to point out that he reports to no one). Adding such functionality is asnap with the CTE - just add it in a LEFT JOIN!

WITH EmployeeSubordinatesReport (EmployeeID, LastName, FirstName, NumberOfSubordinates,

ReportsTo) AS(

SELECTEmployeeID,

LastName,FirstName,

(SELECT COUNT(1) FROM Employees e2WHERE e2.ReportsTo = e.EmployeeID) as NumberOfSubordinates,

ReportsToFROM Employees e

)

SELECT Employee.LastName, Employee.FirstName, Employee.NumberOfSubordinates,Manager.LastName as ManagerLastName, Manager.FirstName as ManagerFirstName,

Manager.NumberOfSubordinates as ManagerNumberOfSubordinatesFROM EmployeeSubordinatesReport Employee

LEFT JOIN EmployeeSubordinatesReport Manager ONEmployee.ReportsTo = Manager.EmployeeID

With this additional LEFT JOIN, the employee's manager's results are returned; if there's no manager for the

employee, NULLs are returned instead.

When to Use Common Table Expressions

Common Table Expressions offer the same functionality as a view, but are ideal forone-off usages where you don't necessarily need a view defined for the system. Even

when a CTE is not necessarily needed (as when listing just the employees and their

subordinate count in the example above), it can improve readability. In UsingCommon Table Expressions, Microsoft offers the following four advantages of CTEs:

Create a recursive query.

Substitute for a view when the general use of a view is not required; that is,you do not have to store the definition in metadata.

Enable grouping by a column that is derived from a scalar subselect, or a

function that is either not deterministic or has external access.

Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance ofcomplex queries. The query can be divided into separate, simple, logical buildingblocks. These simple blocks can then be used to build more complex, interim CTEs

until the final result set is generated.Using scalar subqueries (such as the (SELECT COUNT(1) FROM ...) examples we'velooked at in this article) cannot be grouped or filtered directly in the containing query.Similarly, when using SQL Server 2005's ranking functions - ROW_NUMBER(),
http://msdn2.microsoft.com/en-us/library/ms190766.aspxhttp://msdn2.microsoft.com/en-us/library/ms190766.aspxhttp://msdn2.microsoft.com/en-us/library/ms190766.aspxhttp://msdn2.microsoft.com/en-us/library/ms190766.aspx


13/14

RANK(), DENSE_RANK(), and so on - the containing query cannot include a filter orgrouping expression to return only a subset of the ranked results. For both of theseinstances, CTEs are quite handy. (For more on SQL Server 2005's ranking capabilities,be sure to read: Returning Ranked Results with Microsoft SQL Server 2005.)

CTEs can also be used to recursively enumerate hierarchical data. We'll examine this

next!

Recursive Common Table ExpressionsRecursion is the process of defining a solution to a problem in terms of itself. For example, a teacher needs to

sort a stack of tests alphabetically by the students' names. She could process the tests one at a time and, foreach test, insert it into the appropriate spot to the left (called insertion sort), probably the way most people

sort a hand of cards (at least that's the way I do it). However, depending on the distribution of the tests, thesize of the work space, the number of tests to sort, and so on, it may be far more efficient to break down the

problem into pieces. Rather than doing an insertion sort right off the bat, it might first make sense to divide thestack of papers in half, and then do an insertion sort on one half, an insertion sort on the second half, and then

a merge of the two piles. Or perhaps it would make sense to divide the tests into four piles, or eight piles. (Thisapproach is referred to as merge sort.)

With a recursive solution you will always have the following two pieces:

The base case - what to do when you're done recursing. After dividing the tests into separate piles ofsay, eight elements per pile, the base case is to sort these piles via insertion sort.

The recursive step - the action to perform that involves plugging the input "back into" the system. For

merge sort, the recursive step is the division of one pile into two. Then into four. Then into eight, andso on, until the base case is reached.

For more on recursion, see Recursion, Why It's Cool.

Returning to CTEs... the Employees database table holds the corporate hierarchy within its rows. Imagine that

good ol' Andrew Fuller has come back and insisted on a report that would list all persons in the company alongwith their position in the hierarchy. Since the Employees table can capture an arbitrary number of hierarchy

levels, we need a recursive solution. Enter CTEs...

Like any recursive definition, a recursive Common Table Expression requires both a base case and the recursivestep. In SQL parlance, this translates into two SQL queries - one that gets the "initial" data UNIONed with onethat performs the recursion. For the Employees example, the base case is returning those employees that haveno manager:

SELECT ...FROM Employees

WHERE ReportsTo IS NULL

The recursion includes a query on the CTE itself. The following shows the CTE - with both the base case andrecursive step - along with a SELECT query that returns the rows from the CTE:

WITH EmployeeHierarchy (EmployeeID, LastName, FirstName, ReportsTo, HierarchyLevel) AS(

-- Base caseSELECT

EmployeeID,LastName,FirstName,ReportsTo,1 as HierarchyLevel

FROM EmployeesWHERE ReportsTo IS NULL

UNION ALL

-- Recursive stepSELECT

e.EmployeeID,
http://www.4guysfromrolla.com/webtech/010406-1.shtmlhttp://en.wikipedia.org/wiki/Insertion_sorthttp://en.wikipedia.org/wiki/Mergesorthttp://www.4guysfromrolla.com/webtech/060299-2.shtmlhttp://www.4guysfromrolla.com/webtech/010406-1.shtmlhttp://en.wikipedia.org/wiki/Insertion_sorthttp://en.wikipedia.org/wiki/Mergesorthttp://www.4guysfromrolla.com/webtech/060299-2.shtml


14/14

e.LastName,

e.FirstName,e.ReportsTo,

eh.HierarchyLevel + 1 AS HierarchyLevelFROM Employees e

INNER JOIN EmployeeHierarchy eh ONe.ReportsTo = eh.EmployeeID

)

SELECT *FROM EmployeeHierarchy

ORDER BY HierarchyLevel, LastName, FirstName

The recursion occurs in the second query in the CTE by joining the results of Employees against the CTE itself(EmployeeHierarchy) where the employees' ReportsTo field matches up to the CTE's EmployeeID. Included inthis query is the HierarchyLevel field, which returns 1 for the base case and one greater than the previous levelfor each recursive step down the hierarchy. As requested, this resultset clearly shows that Mr. Fuller is thealpha male in this organization. Furthermore, we can see that Steven, Laura, Nancy, Janet, and Margaret makeup the second tier in the organizational hierarchy, while poor Anne, Robert, and Michael are down at thebottom:

Alternatives to Recursive Common Table Expressions

As we saw in this article, enumerating hierarchical data recursively can beaccomplished via CTEs (for more on using recursive CTEs, don't forget to check outthe official documentation - Recursive Queries Using Common Table Expressions).However, there are other options as well. One choice is to perform the recursion atthe ASP/ASP.NET layer. That is, read in allemployee information to a Recordset ofDataSet in code, and then recurse there. My article Efficiently DisplayingParent-ChildData discusses this approach.

If you need to perform the recursion in SQL, you can use recursive stored procedures,as discussed in The Zen of Recursion. If you are designing a data model that needs to

support hierarchical data, your best bet is to bake in some lineage information directlyinto the table from the get-go. See SQL for Threaded Discussionsand More Trees &Hierarchies in SQL for more information.

ConclusionOne of the many new features in SQL Server 2005 are Common Table Expressions (CTEs), which provide amore readable and usable approach to derived tables. Additionally, CTEs may be recursively defined, allowing arecursive entity to be enumerated without the need for recursive stored procedures. For more on the newfeatures found in SQL Server 2005, be sure to also check out Returning Ranked Results with Microsoft SQLServer 2005 and TRY...CATCH in SQL Server 2005.
http://msdn2.microsoft.com/en-us/library/ms186243.aspxhttp://www.4guysfromrolla.com/webtech/101202-1.shtmlhttp://www.4guysfromrolla.com/webtech/101202-1.shtmlhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/recursion.asphttp://www.sqlteam.com/item.asp?ItemID=1602http://www.sqlteam.com/item.asp?ItemID=1602http://www.sqlteam.com/item.asp?ItemID=8866http://www.sqlteam.com/item.asp?ItemID=8866http://www.4guysfromrolla.com/webtech/010406-1.shtmlhttp://www.4guysfromrolla.com/webtech/010406-1.shtmlhttp://www.4guysfromrolla.com/webtech/041906-1.shtmlhttp://msdn2.microsoft.com/en-us/library/ms186243.aspxhttp://www.4guysfromrolla.com/webtech/101202-1.shtmlhttp://www.4guysfromrolla.com/webtech/101202-1.shtmlhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/recursion.asphttp://www.sqlteam.com/item.asp?ItemID=1602http://www.sqlteam.com/item.asp?ItemID=8866http://www.sqlteam.com/item.asp?ItemID=8866http://www.4guysfromrolla.com/webtech/010406-1.shtmlhttp://www.4guysfromrolla.com/webtech/010406-1.shtmlhttp://www.4guysfromrolla.com/webtech/041906-1.shtml

microsoft sql server 2005 cte query

Documents