quote:

---

Originally posted by snSQL

Although you won't know the depth ahead of time, do you know a maximum depth? Using a work table won't mean you have to know depth, but sorting the rows nested according to the hierarchy will need to use a sort column and it will be easier to implement that if there is a known maximum.

---

--Create a sample table of hierarchically related objects
create table #Objects
(ObjectID int, ObjectName varchar(20), ParentObjectID int)
insert #Objects
select 1, 'Object A', NULL union all
select 2, 'Object B', NULL union all
select 3, 'SubObject A1', 1 union all
select 4, 'SubObject B1', 2 union all
select 5, 'SubObject A11', 3 union all
select 6, 'SubObject B2', 2 union all
select 7, 'SubObject A2', 1

--This is the T-SQL solution
--Create the work table
create table #Hierarchy
(Sort bigint, ObjectID int, ParentObjectID int, ObjectDepth tinyint)

--A variable for sorting the nested hierarchy
declare @StepSize bigint
set @StepSize = 9223372036854775807 / (select count(*) from #Objects where ParentObjectID is null)

--Initially populate the work table with all rows, setting the depth and sort for the top level rows only
insert #Hierarchy
select case when ParentObjectID is null 
		then 1 + ((select count(*) from #Objects where ObjectID < o.ObjectID) * @StepSize)
		else null end
	, ObjectID, ParentObjectID
	, case when ParentObjectID is null 
		then 1 
		else null end 
	from #Objects o

--Now loop, updating down the levels until all rows have their depth and sort set
declare @Depth tinyint
set @Depth = 1
while exists (select * from #Hierarchy where ObjectDepth is null)
begin
	set @StepSize = @StepSize / (select count(*) from #Hierarchy where ObjectDepth is null)
	set @Depth = @Depth + 1
	update #Hierarchy
	set Sort = p.Sort + 1 + ((select count(*) 
			from #Hierarchy x 
			where x.ObjectID < #Hierarchy.ObjectID 
				and x.ParentObjectID = #Hierarchy.ParentObjectID) * @StepSize)
		, ObjectDepth = @Depth 
	from #Hierarchy 
	inner join #Hierarchy p on #Hierarchy.ParentObjectID = p.ObjectID and p.ObjectDepth = @Depth - 1
end

--Final select to get the rows from the work table joined to the original data
select (select count(*) from #Hierarchy where Sort <= h.Sort) as RowNumber 
	, o.ObjectID, o.ObjectName, h.ObjectDepth
from #Objects o 
inner join #Hierarchy h on o.ObjectID = h.ObjectID
order by h.Sort

drop table #Hierarchy

--Create a sample table of hierarchically related objects
create table #Objects
(ObjectID int identity, ObjectName varchar(20), ParentObjectID int)
insert #Objects
select 'Object A', NULL union all
select 'Object B', NULL union all
select 'SubObject A1', 1 union all
select 'SubObject B1', 2 union all
select 'SubObject A11', 3 union all
select 'SubObject B2', 2 union all
select 'SubObject A2', 1

--This is the T-SQL solution
--Create the work table
create table #Hierarchy
(Sort bigint, ObjectID int, ParentObjectID int, ObjectDepth tinyint)

--Initially populate the work table with the top level rows only
insert #Hierarchy
select null, ObjectID, ParentObjectID, 1 
	from #Objects o
	where ParentObjectID IS NULL --Change this where clause to select the top level rows only

--A variable for sorting the nested hierarchy
declare @StepSize bigint
set @StepSize = 9223372036854775807 / (select count(*) from #Hierarchy)
--Set the sort for the top level rows
update #Hierarchy
set Sort = 1 + ((select count(*) from #Hierarchy h where h.ObjectID < #Hierarchy.ObjectID) * @StepSize)

--Get the nested rows for the selected top level rows
declare @Depth tinyint
set @Depth = 1
while exists (select * from #Objects o
		inner join #Hierarchy h on o.ParentObjectID = h.ObjectID and h.ObjectDepth = @Depth)
begin
	insert #Hierarchy
	select null, o.ObjectID, o.ParentObjectID, @Depth + 1
		from #Objects o
		inner join #Hierarchy h on o.ParentObjectID = h.ObjectID and h.ObjectDepth = @Depth
	set @Depth = @Depth + 1
end

--Now loop, updating down the levels until all rows have their depth and sort set
set @Depth = 1
while exists (select * from #Hierarchy where Sort is null)
begin
	set @Depth = @Depth + 1
	set @StepSize = @StepSize / (1 + (select count(*) from #Hierarchy where ObjectDepth = @Depth))
	update #Hierarchy
	set Sort = p.Sort + 1 + ((select count(*) 
			from #Hierarchy x 
			where x.ObjectID < #Hierarchy.ObjectID 
				and x.ParentObjectID = #Hierarchy.ParentObjectID) * @StepSize)
	from #Hierarchy 
	inner join #Hierarchy p on #Hierarchy.ParentObjectID = p.ObjectID and p.ObjectDepth = @Depth - 1
end

--Final select to get the rows from the work table joined to the original data
select (select count(*) from #Hierarchy where Sort <= h.Sort) as RowNumber 
	, o.ObjectID, o.ObjectName, h.ObjectDepth
from #Objects o 
inner join #Hierarchy h on o.ObjectID = h.ObjectID
order by h.Sort

drop table #Hierarchy

quote:

---

Originally posted by tkizer

But there probably isn't a way to write this as a set-based solution anyway, so either a cursor or a WHILE loop has to be used!

Tara Kizer

---

quote:

---

Originally posted by snSQL

Absolutely! Also, putting the row number in the final result is very expensive, so you could improve things by taking that out if you don't need it.

And if you want a cursor based solution, (which will work for any size data), there is a nice one here
http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm

---

select count(*) from #Hierarchy where Sort <= h.Sort

quote:

---

Originally posted by madrak

quote:

---

Originally posted by tkizer

But there probably isn't a way to write this as a set-based solution anyway, so either a cursor or a WHILE loop has to be used!

Tara Kizer

---

Is there an advantage to the WHILE loop over a cursor? I understood that there was some additional overhead incurred by a cursor that made it even worse than a while loop, although this may certainly be incorrect.

---

quote:

---

Originally posted by tkizer

quote:

---

Originally posted by madrak

Is there an advantage to the WHILE loop over a cursor? I understood that there was some additional overhead incurred by a cursor that made it even worse than a while loop, although this may certainly be incorrect.

---

You would need to test the performance difference.

Tara Kizer

---