SQL Server 2012 FileTables in T-SQL part 3: hierarchyid methods

So finally, we get to using FileTable's path_locator column with the methods of hierarchyid. To pick up where we left off, remember that hierarchyid has a method named GetLevel(). Given the following directory structure in the FileTable's share:

File1.txt
File2.txt
SQLFiles
  SubdirFile1.txt
  SubdirFile2.txt

I have a few more levels of subdirectory here, but hopefully, you get the idea. Issuing the query

SELECT path_locator.GetLevel() as Level, Name
FROM dbo.Documents;

show, unsurprisingly, File1.txt, File2.txt, and SQLFiles directory at level 1, and the subdirectory files at level 2. So to be sure we're getting the "right" SQLFiles directory, we could change that query in the previous blog entry to:

SELECT @pathstring = path_locator.ToString() FROM dbo.Documents WHERE Name = 'SQLFiles' AND path_locator.GetLevel() = 1;

Note also that, the way the FileTable namespace works, the dbo.Documents directory is the root of the hierarchy. We can prove it using hierarchyids.

— Level Number of table is 0. Table PathLocator is root
SELECT FileTableRootPath('dbo.Documents') as RootPath,
       GetPathLocator(FileTableRootPath('dbo.Documents')) as Path,
       GetPathLocator(FileTableRootPath('dbo.Documents')).GetLevel() as Level;

As long are we're on that subject, let's use the rest of the hierarchyid methods on the FileTable as well.

— an interesting way to get all direct children of the top-level SQLFiles directory node
SELECT p.name as ParentName, c.name as ChildName, c.is_directory,
 c.file_stream.GetFileNamespacePath() as ChildPath
FROM dbo.Documents p
JOIN dbo.Documents c
ON p.name = 'SQLFiles'
AND p.path_locator.GetLevel() = 1
AND c.path_locator.GetAncestor(1) = p.path_locator;

— but we have a persisted computed column for that…
SELECT p.name as ParentName, c.name as ChildName, c.is_directory,
 c.file_stream.GetFileNamespacePath() as ChildPath
FROM dbo.Documents p
JOIN dbo.Documents c
ON p.name = 'SQLFiles'
AND p.path_locator.GetLevel() = 1
AND c.parent_path_locator = p.path_locator;

How about all children of that node, at any subdirectory level?

— child of the top-level SQLFiles node, any
SELECT p.name as ParentName, c.name as ChildName, c.is_directory,
 c.file_stream.GetFileNamespacePath() as ChildPath
FROM dbo.Documents p
JOIN dbo.Documents c
ON p.name = 'SQLFiles'
AND p.path_locator.GetLevel() = 1
AND c.path_locator.IsDescendantOf(p.path_locator) = 1;

And of course, wind our way up the tree:

— parents of a specific node
SELECT p.name as ParentName, c.name as ChildName, p.is_directory,
 p.file_stream.GetFileNamespacePath() as ParentPath
FROM dbo.Documents p
JOIN dbo.Documents c
ON c.name = 'SQLSubdirSomeText.txt'
AND c.path_locator.IsDescendantOf(p.path_locator) = 1;

And, we can see the obvious use of GetReparentedValue. It moves a file from one subdirectory to another. You can't just set parent_path_locator because that's a computed column.

DECLARE @oldpath hierarchyid, @newpath hierarchyid;
SELECT @oldpath = path_locator from documents where name = 'SQLServerSubdir' AND path_locator.GetLevel() = 2;
SELECT @newpath = path_locator from documents where name = 'SQL Server' AND path_locator.GetLevel() = 1;
UPDATE dbo.Documents SET path_locator = path_locator.GetReparentedValue(@oldpath, @newpath)
WHERE name = 'SubSyntheticFile2.txt';

Enjoy!

@bobbeauch

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.