-- Create the test table
USE AdventureWorks
GO
IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id=OBJECT_ID('dbo.Groups'))
BEGIN
	DROP TABLE dbo.Groups
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.tables WHERE object_id=OBJECT_ID('dbo.Groups'))
BEGIN
	CREATE TABLE dbo.Groups
	(
		GroupID int identity NOT NULL,
		GroupName nvarchar(100),
		ParentGroupID int NULL,
		[Description] nvarchar(150)
	)

	ALTER TABLE dbo.Groups ADD CONSTRAINT PK_Groups PRIMARY KEY CLUSTERED (GroupID)
	CREATE INDEX IX_Groups_ParentGroupID ON dbo.Groups (ParentGroupID)
END
GO
IF EXISTS(SELECT 1 FROM sys.Procedures WHERE object_id=object_id('dbo.GetDescendantsLoop'))
DROP PROCEDURE dbo.GetDescendantsLoop
GO
CREATE PROCEDURE dbo.GetDescendantsLoop @GroupID int
AS
DECLARE @Start datetime,@Finish Datetime
SET @Start=GETDATE()
SET NOCOUNT ON

DECLARE @RowCnt int,@Level int
SELECT @Level=0,@RowCnt=0

DECLARE @Descendants TABLE (		
					GroupID int NOT NULL,
					GroupName 	nvarchar(100) NOT NULL,
					ParentGroupID int NULL,
					ParentGroupName nvarchar(100) NOT NULL,
					[Level]	int NOT NULL)

INSERT @Descendants (GroupID, ParentGroupID, GroupName, ParentGroupName,[Level])
SELECT G.GroupID, G.ParentGroupID, G.GroupName, ISNULL(P.GroupName,N''),0
FROM dbo.Groups G LEFT JOIN dbo.Groups P
ON G.ParentGroupID=P.GroupID
WHERE G.GroupID=@GroupID

SET @RowCnt=ISNULL(@@ROWCOUNT,0)
WHILE @RowCnt>0
BEGIN
	INSERT @Descendants (GroupID, ParentGroupID, GroupName, ParentGroupName,[Level])
	SELECT T1.GroupID,T1.ParentGroupID, T1.GroupName,T2.GroupName,@Level+1
	FROM dbo.Groups T1
	INNER JOIN @Descendants T2 ON T1.ParentGroupID IS NOT NULL AND T1.ParentGroupID=T2.GroupID AND T2.[Level]=@Level
	SET @RowCnt=ISNULL(@@ROWCOUNT,0)

	SET @Level=@Level+1
END
SELECT GroupID, ParentGroupID, GroupName, ParentGroupName,[Level]
FROM @Descendants
ORDER BY [Level],ParentGroupID,GroupID

SET @Finish=GETDATE()
PRINT 'By Loop: Starts at '+convert(nvarchar(100),@Start,121)+', Finishes at '+convert(nvarchar(100),@Finish,121)+', Takes '+CAST(datediff(ms,@Start,@Finish) as nvarchar(20))+'ms'
GO
IF EXISTS(SELECT 1 FROM sys.Procedures WHERE object_id=object_id('dbo.GetDescendantsCTE'))
DROP PROCEDURE dbo.GetDescendantsCTE
GO
CREATE PROCEDURE dbo.GetDescendantsCTE @GroupID int
AS
DECLARE @Start datetime,@Finish Datetime
SET @Start=GETDATE()
SET NOCOUNT ON;

WITH Descendants (GroupID, ParentGroupID, GroupName, ParentGroupName, [Level])
AS
(
-- Anchor member definition
    SELECT G.GroupID, G.ParentGroupID, G.GroupName, ISNULL(P.GroupName,N''), 0 AS [Level]
    FROM dbo.Groups G LEFT JOIN dbo.Groups P
	ON G.ParentGroupID=P.GroupID
	WHERE G.GroupID=@GroupID 
    UNION ALL
-- Recursive member definition
    SELECT G.GroupID, G.ParentGroupID, G.GroupName, P.GroupName, P.[Level]+1
    FROM dbo.Groups G 
	INNER JOIN Descendants P ON G.ParentGroupID = P.GroupID
)
-- Statement that executes the CTE
SELECT GroupID, ParentGroupID, GroupName, ParentGroupName, [Level]
FROM Descendants
ORDER BY [Level],ParentGroupID,GroupID
SET @Finish=GETDATE()
PRINT 'By CTE: Starts at '+convert(nvarchar(100),@Start,121)+', Finishes at '+convert(nvarchar(100),@Finish,121)+', Takes '+CAST(datediff(ms,@Start,@Finish) as nvarchar(20))+'ms'
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetDescendantsLoop2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetDescendantsLoop2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetDescendantsLoop2] @GroupID int
AS
DECLARE @Start datetime,@Finish Datetime
SET @Start=GETDATE()
SET NOCOUNT ON

DECLARE @RowCnt int,@Level int
SELECT @Level=0,@RowCnt=0

DECLARE @Descendants TABLE (		
					GroupID int NOT NULL,
				--	GroupName 	nvarchar(100) NOT NULL,
				--	ParentGroupID int NULL,
				--	ParentGroupName nvarchar(100) NOT NULL,
					[Level]	int NOT NULL)-- PRIMARY KEY CLUSTERED([Level],GroupID))

INSERT @Descendants (GroupID, [Level])
SELECT G.GroupID, 0
FROM dbo.Groups G
WHERE G.GroupID=@GroupID

SET @RowCnt=ISNULL(@@ROWCOUNT,0)
WHILE @RowCnt>0
BEGIN
	INSERT @Descendants (GroupID, [Level])
	SELECT T1.GroupID,@Level+1
	FROM dbo.Groups T1
	INNER JOIN @Descendants T2 ON T1.ParentGroupID IS NOT NULL AND T1.ParentGroupID=T2.GroupID AND T2.[Level]=@Level
	SET @RowCnt=ISNULL(@@ROWCOUNT,0)

	SET @Level=@Level+1
END
SELECT G.GroupID, G.ParentGroupID, G.GroupName, P.GroupName AS ParentGroupName,D.[Level]
FROM @Descendants D INNER JOIN dbo.Groups G ON D.GroupID=G.GroupID
	LEFT JOIN dbo.Groups P ON G.ParentGroupID=P.GroupID
ORDER BY [Level],ParentGroupID,GroupID

SET @Finish=GETDATE()
PRINT 'By Loop: Starts at '+convert(nvarchar(100),@Start,121)+', Finishes at '+convert(nvarchar(100),@Finish,121)+', Takes '+CAST(datediff(ms,@Start,@Finish) as nvarchar(20))+'ms'
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetDescendantsCTE2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetDescendantsCTE2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetDescendantsCTE2] @GroupID int
AS
DECLARE @Start datetime,@Finish Datetime
SET @Start=GETDATE()
SET NOCOUNT ON;

WITH Descendants (GroupID, [Level])
AS
(
-- Anchor member definition
    SELECT G.GroupID,  0 AS [Level]
    FROM dbo.Groups G 
	WHERE G.GroupID=@GroupID 
    UNION ALL
-- Recursive member definition
    SELECT G.GroupID, P.[Level]+1
    FROM dbo.Groups G 
	INNER JOIN Descendants P ON G.ParentGroupID = P.GroupID
)
-- Statement that executes the CTE
SELECT G.GroupID, G.ParentGroupID, G.GroupName, P.GroupName AS ParentGroupName, D.[Level]
FROM Descendants D INNER JOIN dbo.Groups G ON D.GroupID=G.GroupID
LEFT JOIN dbo.Groups P ON G.ParentGroupID=P.GroupID
ORDER BY [Level],ParentGroupID,GroupID
SET @Finish=GETDATE()
PRINT 'By CTE: Starts at '+convert(nvarchar(100),@Start,121)+', Finishes at '+convert(nvarchar(100),@Finish,121)+', Takes '+CAST(datediff(ms,@Start,@Finish) as nvarchar(20))+'ms'
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAncestorsLoop]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetAncestorsLoop]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetAncestorsLoop] @GroupID int
AS
DECLARE @Start datetime,@Finish Datetime
SET @Start=GETDATE()
SET NOCOUNT ON

DECLARE @RowCnt int,@Level int
SELECT @Level=0,@RowCnt=0

DECLARE @Ancestors TABLE (		
					GroupID int NOT NULL,
					[Level]	int NOT NULL)

INSERT @Ancestors (GroupID, [Level])
SELECT G.GroupID, 0
FROM dbo.Groups G
WHERE G.GroupID=@GroupID

SET @RowCnt=ISNULL(@@ROWCOUNT,0)
WHILE @RowCnt>0
BEGIN
	INSERT @Ancestors (GroupID, [Level])
	SELECT T1.ParentGroupID,@Level+1
	FROM dbo.Groups T1
	INNER JOIN @Ancestors T2 ON T1.GroupID=T2.GroupID AND T2.[Level]=@Level AND T1.ParentGroupID IS NOT NULL
	SET @RowCnt=ISNULL(@@ROWCOUNT,0)

	SET @Level=@Level+1
END
SELECT G.GroupID, G.ParentGroupID, G.GroupName, P.GroupName AS ParentGroupName,D.[Level]
FROM @Ancestors D INNER JOIN dbo.Groups G ON D.GroupID=G.GroupID
	LEFT JOIN dbo.Groups P ON G.ParentGroupID=P.GroupID
ORDER BY [Level],ParentGroupID,GroupID

SET @Finish=GETDATE()
PRINT 'By Loop: Starts at '+convert(nvarchar(100),@Start,121)+', Finishes at '+convert(nvarchar(100),@Finish,121)+', Takes '+CAST(datediff(ms,@Start,@Finish) as nvarchar(20))+'ms'
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAncestorsCTE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetAncestorsCTE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetAncestorsCTE] @GroupID int
AS
DECLARE @Start datetime,@Finish Datetime
SET @Start=GETDATE()
DECLARE @MaxLevel int
SET NOCOUNT ON;

WITH Ancestors (GroupID, [Level])
AS
(
-- Anchor member definition
    SELECT G.GroupID,  0 AS [Level]
    FROM dbo.Groups G 
	WHERE G.GroupID=@GroupID 
    UNION ALL
-- Recursive member definition
    SELECT G.ParentGroupID, A.[Level]+1
    FROM dbo.Groups G 
	INNER JOIN Ancestors A ON G.GroupID = A.GroupID AND G.ParentGroupID IS NOT NULL
)

-- Statement that executes the CTE
SELECT G.GroupID, G.ParentGroupID, G.GroupName, P.GroupName AS ParentGroupName, D.[Level]
FROM Ancestors D INNER JOIN dbo.Groups G ON D.GroupID=G.GroupID
LEFT JOIN dbo.Groups P ON G.ParentGroupID=P.GroupID
ORDER BY [Level],ParentGroupID,GroupID
SET @Finish=GETDATE()
PRINT 'By CTE: Starts at '+convert(nvarchar(100),@Start,121)+', Finishes at '+convert(nvarchar(100),@Finish,121)+', Takes '+CAST(datediff(ms,@Start,@Finish) as nvarchar(20))+'ms'
GO