Last week I had a problem where I needed a hierarchical chain of results. I had to get results of all organisations above my organsiation in a hierarcy. I knew how I would do this by recursion in C#, but had no idea how to get it done in SQL. But asking a bit around, and searching the web I got a really nice solution using the to me new WITH-command. And it seems quite fast aswell. Here is a SQL script with some testdata:

Create table #Relations 
(
  ID int PRIMARY KEY,
  ChildId int,
  name varchar(50)
)
insert into #Relations values (5, 4, 'Me')
insert into #Relations values (4, 3, 'Father')
insert into #Relations values (3, 2, 'Grandpa')
insert into #Relations values (2, 1, 'Great Grandpa')
insert Into #Relations  values (1, null, 'Great Old Grandpa')

declare @startid int
set @startid = 5

;WITH Names AS
(
--Gets the starting line
SELECT * FROM #Relations where id = @startid
UNION ALL
SELECT r.* FROM #Relations r
INNER JOIN names n ON n.ChildId=r.id
)
select * from names

Here are the results. As you can see, it works;)

ID ChildId name
---------- ----------- --------------------------------------------------
5 4 Me
4 3 Father
3 2 Grandpa
2 1 Great Grandpa
1 NULL Great Old Grandpa
(5 row(s) affected)