Achilles' Blog

Imagine this table, filled with more than a million records containing a 7-levels tree:

CREATE TABLE HierTest (
	Id		BIGINT PRIMARY KEY IDENTITY(1,1),
	Path	HIERARCHYID NOT NULL UNIQUE,
	Name	NVARCHAR(100) NOT NULL
)

Now imagine there’s a node with Id=1839861 and you want to get all of its ancestors. I know of two ways to do this (sources: Simon Ince’s blog post and StackOverflow):

DECLARE @Hiernode HIERARCHYID;
SELECT @Hiernode=[ht].[Path] FROM [TestSchema].[HierTest] AS [ht] WHERE [ht].[Id]=1839861;
SELECT
	*--,
	--[ht].[Path].ToString() AS PathString
FROM [TestSchema].[HierTest] AS [ht]
WHERE @Hiernode.[IsDescendantOf]([ht].[Path])=1
ORDER BY [ht].[Path];

and

WITH Tree(Id, Name, Path, AncestorPath) AS(
	SELECT
		[ht].[Id],
		[ht].[Name],
		[ht].[Path],
		[ht].[Path].GetAncestor(1) AS 'AncestorPath'
	FROM [TestSchema].[HierTest] AS [ht]
	WHERE [ht].[Id]=1839861

	UNION ALL

	SELECT
		[ht].[Id],
		[ht].[Name],
		[ht].[Path],
		[ht].[Path].GetAncestor(1) AS 'AncestorPath'
	FROM [TestSchema].[HierTest] AS [ht]
	INNER JOIN [Tree] t ON [ht].[Path] = [t].[AncestorPath]
)
SELECT
	[t].[Id],
	[t].[Name],
	[t].[Path]
FROM [Tree] t
ORDER BY [t].[Path]

Measuring the execution time shows the “Take II” to be more than 100 times faster than “Take I”!!

I’ll update this post with what I’ll learn in the future.

Download the scripts from: Hierarchy Test.