The Filetable is a very interesting way to work with BLOB data in SQL Server. A Filetable allows "normal" Windows Explorer operations from outside SQL Server to files, but also lets this data appear as a table in SQL Server. This article will not examine how to setup a Filetable, but will instead look at ways in which you can manipulate the files that exist in a Filetable using T-SQL.

The View of a Filetable

Let's assume I have a Filetable called "Books" that exists in one of my databases. If I query the Filetable with this code:

SELECT name
     , parent_path_locator 
     , cached_file_size
     , is_directory 
 FROM Books

I will see the results shown below. Note that I've populated some values and I'm not returning all the fields from the table. Many of those fields are not germane to this article.

You can see here that I have 4 PDFs and 4 jpg files that match up somewhat on names. The actual data here are the ebooks with the titles shown and jpg images of their covers. I can tell all of these are in the root of my Filetable as the parent_path_locator is NULL for each of these rows.

I can also query this "table" from Windows. I have the path for my table set to: \\JOLLYGREENGIANT\SQLFS\Library\Books. If I go to that share on my Windows machine, I see this:

This is the state of my table and I'd like to do a few things now. I'm going to move these files to subfolders from the root and then I'll add a file from T-SQL to the root before removing it again.

Creating a Subfolder

One of the common operations that we do with folders in Windows is create subfolders. They're an organizational item that's needed. In this case, I want to create two subfolders: covers and content.

Creating a folder in a Filetable programmatically is as simple as inserting a row in the table. In this case, we want to insert a row that has a name of "Covers" in the table. However the schema for a Filetable has a number of other fields that must be addressed. Fortunately we can ignore most of them.

To insert a folder, we really only care about the name, the path, and the is_directory field. All other fields will be assigned by SQL Server or do not apply. This means our new folder, inserted into the root of the share, can use this code:

INSERT INTO dbo.Books
        ( name
        , is_directory
        )
    VALUES
        ( 'Covers'
        , 1
);

Since the folder goes in the root, we don't need a path. I can repeat this for my "Content" folder and then I see the share now looks like this image:

I did have to refresh the share on my machine to get the folders to appear.

Suppose I wanted to put a subfolder below one of these. Let's take the Content folder and add a folder called "Archived" under it. The Content folder's contents now look like this:

To add a new folder here, I need to ensure that it's parent_path_locator is equal to the Content folder row. However the parent_path_locator is a computed comlumn, as shown here:

The way that we actually get the correct value in the parent_path_locator is to create a new path for the path_locator field for our new column and ensure that it includes the path of the current "Content" folder row.

To get the new hierarchyID for the path, we use this formula:

parentpath.ToString()

 + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),

                                                  1, 6))) + '.'

 + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),

                                                  7, 6))) + '.'

 + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),

                                                  13, 4))) + '/'

This essentially takes the current hierarchyID path, executes the ToString() method, and then appends three GUIDs that have had some conversions run on them. You can actually see this formula in the definition for the path_locator constraint on a Filetable.

Here's the code that will insert a folder below Content:

DECLARE @path HIERARCHYID
SELECT @path = path_locator
 FROM dbo.Books
 WHERE name = 'Content';
INSERT INTO dbo.Books
  ( name
  , is_directory
  , path_locator
   )
  SELECT 
    'Archived'
    , 1
    , @path.ToString()
        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),
                                                         1, 6))) + '.'
        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),
                                                         7, 6))) + '.'
        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),
                                                         13, 4))) + '/'
;

As you can see, I now have a folder below the Content folder.

That's the majority of how you can manipulate rows in a Filetable, whether they are content items or folders. However let's now look at moving files.

Moving Files to a Subfolder

I've created a subfolder, and now I want to move files to this subfolder. In this case, I want to move all the images to the Covers folder and the PDFs to the Content folder. Just as I've calculated the path above for the folders, I'll do the same thing for the content, but this time using an UPDATE statement.

To move the jpgs, the easy way is to set a WHERE clause like this:

WHERE RIGHT(name, 3) = 'jpg'

That will return me the four rows that have an extention of "jpg". I could use any other method of filtering my rows, but this works well for me.

The next step is to build the rest of the UPDATE statement. In this case, I'll only be altering the path of the files, or rows, to make them a child of the Covers row. To do this, I'll get the hierarchyID of the folder and then calculate a new path.

DECLARE @path HIERARCHYID
SELECT @path = path_locator
  FROM dbo.Books
  WHERE name = 'Covers';
UPDATE dbo.Books
  SET path_locator = @path.ToString()
          + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),
                                                           1, 6))) + '.'
          + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),
                                                           7, 6))) + '.'
          + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),
                                                           13, 4))) + '/'
  WHERE RIGHT(name, 3) = 'jpg'
  ;

When I run this, I'll get the following results.

I'll see this in the root of my share:

And this in the Covers folder:

Now I just repeat the code above, changing the first SELECT to get the path for the "Content" folder and the WHERE clause to look for PDFs.

I haven't found good code for moving files back to the root. It becomes fairly easy to move files around, but to a new root is complex. Here's the best I've found:

BEGIN TRY
INSERT INTO dbo.Books
        ( file_stream
        , name
        )
        SELECT file_stream
            ,   name
            FROM books
            WHERE name = 'The_Adventures_of_Huckleberry_Finn_NT.pdf';
DELETE dbo.Books
    WHERE name = 'The_Adventures_of_Huckleberry_Finn_NT.pdf'
        AND parent_path_locator IS NOT NULL;
END TRY
BEGIN CATCH
  SELECT @@error
END CATCH;

I add another copy of the files to the table and then delete the old one.

I put my files back manually, but I put the two PDFs in the Covers folder. That's wrong, and I want to move them back to Content. Here's the current state of the table.

It's hard to tell, but I have two PDFs in the Content folder:

and two in the Covers folder, which is wrong.

Let's move the two from the Covers to the Content folder. To do this, we need to understand the hierarchyID and use a few of its functions: Get_Level() and GetReparentedValue(). We will use the GetLevel

Adding and Removing Files

If I want to add a new file from Windows, I can drag/drop it in there (or use VBScript/PowerShell/Commpand Prompt Copy, etc.). However if I want to do this from T-SQL, I can do it as well, but I need to insert the entire stream. From a .NET application, using parameters, this is easy. In native T-SQL it's also easy, but cumbersome as I need to specify the binary code.

INSERT INTO Books(stream_id, name, file_stream) 
Values (NEWID()
      , 'circle.jpg'
      , 0xFFD8FFE000104A46494600010101006000600000FFE100684578696600004D4D002A000000080004011A0005000000010000003E011B0005000000010000004601280003000000010002000001310002000000120000004E00000000000000600000000100000060000000015061696E742E4E45542076332E352E313000FFDB0043000201010201010202020202020202030503030303030604040305070607070706070708090B0908080A0807070A0D0A0A0B0C0C0C0C07090E0F0D0C0E0B0C0C0CFFDB004301020202030303060303060C0807080C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0CFFC0001108000A000D03012200021101031101FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A636465666768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F0100030101010101010101010000000000000102030405060708090A0BFFC400B51100020102040403040705040400010277000102031104052131061241510761711322328108144291A1B1C109233352F0156272D10A162434E125F11718191A262728292A35363738393A434445464748494A535455565758595A636465666768696A737475767778797A82838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C03010002110311003F00E97FE0E87FF82CA7ED35FB04FED85E09F03FC2BD6A5F87BE1097408B5C4D523D2ADAF1BC41746795248CBDC4722F970848C18940399373E43478FD75FF00827CFC68F177ED17FB0FFC2AF1D78F3461A078C7C59E19B2D4F57B110B42B14F244199846DF346AFC384392A1C024E335E95E2EF87BE1FF880968BAF687A3EB6B612F9F6C2FECA3B916F27F7D3783B5BDC60D6C5007FFFD9
 );

That will insert the image of a circle into the folder:

Likewise, a simple DELETE statement can remove files, as can the standard click-the-delete-button in Explorer.

DELETE Books where name = 'circle.jpg'

Conclusion

This is a basic look at how you can work with your Filetable using T-SQL. It is expected that most work with occur via Explorer, but you certainly can perform file moves and other work with T-SQL. 

In another article, I'll look at a few more ways in which you can play with Filetables.

References

• SQL Server 2012 FileTables in T-SQL part 3: hierarchyid methods - http://www.sqlskills.com/blogs/bobb/sql-server-2012-filetables-in-t-sql-part-3-hierarchyid-methods/
• FileTables (BOL) - https://msdn.microsoft.com/en-us/library/ff929144.aspx
• Working with Directories and Paths in FileTables - https://msdn.microsoft.com/en-us/library/gg492087.aspx