SQL Server 2005 – Recursion

Christian Donner

16 years ago

Why do I find all the examples of how to use recursion in SQL Server 2005 too complicated (including the one in the Microsoft help)?

Here is mine that does the bare minimum. First, let’s create a table and populate it with hierarchical test data:

create table Hierarchy
(
	id int,
	parent_id int
)
GO

insert into Hierarchy values (0, null);
insert into Hierarchy values (1, 0);
insert into Hierarchy values (2, 0);
insert into Hierarchy values (3, 1);
insert into Hierarchy values (4, 1);
insert into Hierarchy values (5, 1);
insert into Hierarchy values (6, 2);
insert into Hierarchy values (7, 2);
insert into Hierarchy values (8, 7);
insert into Hierarchy values (9, 5);
GO

The recursion uses a Common Table Expression (CTE) with a union clause. The first part of the Union returns the root nodes (multiple roots are possible). In the first iteration, the second part returns all the children of the root, then all the children of those, etc. SQL Server handles a recursion depth of up to 100.

with Recursion (id, parent_id, path, Level)
as
(
select			-- root node(s)
	id,
	parent_id,
	convert(nvarchar(32), '0') as path,
	0
from
	Hierarchy h
where
	h.parent_id is null
union all
select			-- all other nodes
	h.id,
	h.parent_id,
	convert(nvarchar(32), r.path + '>' +
		convert(varchar(10), h.id)) as path,
	r.Level + 1
from
	Hierarchy h

	inner join Recursion as r
	on h.parent_id = r.id
)
select id, parent_id, path, level from Recursion

By joining to the parent record, the 2nd part of the union can do cool things like tracking the recursion depth (in the Level column) or concatenating all ids in the path to the current element (in the Path column). In fact, the string concatenation is the most complicated part of this query. Here is the output:

id parent_id path    level
-- --------- ------- -----
0  NULL      0       0
1  0         0>1     1
2  0         0>2     1
6  2         0>2>6   2
7  2         0>2>7   2
8  7         0>2>7>8 3
3  1         0>1>3   2
4  1         0>1>4   2
5  1         0>1>5   2
9  5         0>1>5>9 3

(10 row(s) affected)

Very convenient …