Category Archives: T-SQL

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.

Bulk Backup All SQL databases

This is a simple SQL-T script to backup every database you have on an SQL-Server Engine.

USE master;
GO
DECLARE @dbName NVARCHAR(MAX);
DECLARE curDBs CURSOR FAST_FORWARD READ_ONLY FOR SELECT [s].[name] FROM [sys].[sysdatabases] AS s WHERE [s].[dbid] > 4;
OPEN curDBs
FETCH NEXT FROM curDBs INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
	DECLARE @cmd NVARCHAR(MAX);
	SET @cmd = N'BACKUP DATABASE ['+ @dbName +'] TO DISK = N''D:\xData\_backups\'+ @dbName +'.bak'' WITH NOFORMAT, NOINIT, NAME = ''' + @dbName + N'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10';
	PRINT @cmd;
	EXEC [sys].[sp_executesql] @cmd;

	FETCH NEXT FROM curDBs INTO @dbName
END
CLOSE curDBs
DEALLOCATE curDBs