If you are currently using the nested sets model for storing trees then it may be about time for a change. This model is great for queries but inserts and updates can touch nearly every row in the table. The more natural way of storing trees is via a ParentID known as the adjacency list model as shown below:
create
table dbo.Projects(
ProjectID
int not null, ParentID
int null, Name
nvarchar(max) not null)
The big drawback, however, is that you need to write recursive queries to get anything useful. Help is at hand though as SQL Server 2005 adds some features to automatically unwind the tree: Common Table Expressions or CTEs.
The basic structure of a CTE working with hierarchies is:
with
<theCTE>(
<anchor expression> union all <recursive expression>)
select
* from <theCTE>
Where the <anchor expression> determines where to start the recursion and the <recursive expression> does the work. Note that the <recursive expression> should join to the CTE to work properly, for example the following lists all descendants of the project defined by <rootID>:
with
Tree (ParentID, ProjectID, Name, Level, Path)as
(
select ParentID, ProjectID, Name, 0
as [Level], '/' + Name as [Path] from dbo.Projects
where ParentID = <rootID> union all select p.ParentID, p.ProjectID, p.Name, t
.Level + 1, t.Path + '/' + p.Name from dbo.Projects p join Tree t on t.ProjectID = p.ParentID)
select
ParentID, ProjectID, Name, Level, Pathfrom
Tree torder
by ParentID, Level, ProjectID
Note how the Level and Path are calculated by adding to the result from the previous iteration.
You can also recurse up the tree:
with Tree (ParentID, ProjectID, Name)as( select ParentID, ProjectID, Name from dbo.Projects where ProjectID = <rootID> union all select p.ParentID, p.ProjectID, p.Name from dbo.Projects p join Tree t on t.ParentID = p.ProjectID)select distinct ProjectID, ParentID, Name
from Tree t
order by ParentID, ProjectID
When you consider you can wrap these queries into table valued functions, there is a lot of scope for writing cohesive reusable SQL.