Skip to content
Donner's Daily Dose of Drama
Donner's Daily Dose of Drama
  • The Good
    • Blogging
    • Consumer Protection
    • Environment
    • Ethics
    • Geek’s Home
    • Lisa Lanett
    • Medfield
    • Music
    • Parenting and Technology
    • Travel
    • wow
  • The Bad
    • Business
    • Ebay
    • Investment
    • Job search
    • Personal Finance
    • Politics
  • The Ugly
    • Information Technology
      • Business Intelligence
      • Content Management
      • Free Software
      • I18N and L10N
      • Java
      • Open Source
      • Mobile Devices
      • Open Source Business Intelligence
      • OSBI
      • SDA
      • Security
      • Smartphone
      • Software Best Practices
      • Software Engineering
      • SQL Server
      • Streaming Media
      • Web
    • Austria
    • Fiction
    • Hardware
    • iPod
    • Miscellaneous
    • Uncategorized
    • Video
    • Weekend Warrior
Donner's Daily Dose of Drama

SQL Server 2005 – Recursion

Christian Donner, May 3, 2008April 12, 2009

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 …

Related Posts:

  • TyreWiz not working after battery change
  • OpenVPN
  • The Great Cat Litter Poop Off
  • The Voip.ms SMS Integration for Home Assistant
  • Computer Build 2025

SQL Server RecursionSQL

Post navigation

Previous post
Next post

Leave a Reply

Your email address will not be published. Required fields are marked *

Pages

  • About
  • Awards
    • TechnoLawyer
  • Contact Christian Donner
  • Project Portfolio
  • Publications
  • Speaking Engagements

Recent Comments

  • Christian Donner on Sealing a leaky cast-iron fireplace chimney damper
  • Eric on Sealing a leaky cast-iron fireplace chimney damper
  • Christian Donner on Contact Christian Donner
  • Max on Contact Christian Donner
  • Christian Donner on Contact Christian Donner

Tags

AHCI Amazon Android ASP.Net AT&T Droid Drupal email Error failure featured firmware Garmin Godaddy Google honda Internet Explorer 8 iPhone Lenovo Lisa Lanett Modules NAS Nexus One Paypal Performance Privacy QNAP raid RS-407 sauna Security spam SQL SR3600 Synology T-Mobile T430s transmission tylö Verizon Virus VMWare Windows 7 windows 8.1 Windows Mobile
  • About
  • Awards
    • TechnoLawyer
  • Contact Christian Donner
  • Project Portfolio
  • Publications
  • Speaking Engagements
©2025 Donner's Daily Dose of Drama | WordPress Theme by SuperbThemes