Monthly Archives: April 2017

Finding ancestors in a SQL-Server’s HierarchyId tree

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.

Windows TCP 49154 – What is it!!?

Just had a tough struggle with my “MUTE” friend – Windows Server – trying to understand what’s running behind tcp/49154 on my server, accepting connection.

There are many ALLOW ALL PROFILES WITH ANY PORTS listed on windows’ firewall by default and thinking of them as being secure is not right.

File Server Remote Management (WMI-In) was listening on that port! Disabled the rule and now I’m in peace again! 😀