So the functions/methods that I wrote about in previous post are needed because FileTables don't store the UNC path name of the file, they store the path_locator as a hierarchyid data type. Wonder what encoding scheme they're using. Let's see, by doing
SELECT path_locator.ToString(), Name
FROM dbo.Documents
We get hierarchyid strings that look like this: "/192992825631153.73945086322524.2119705196/" Turns out that the encoding scheme involves newid() as you can see by looking at the definition for the default constraint for the path_locator column. It looks like this:
convert(hierarchyid, '/' +
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))) + '/')
In fact, almost all of the columns in a FileTable have defaults or are computed columns. So, to create a row in a FileTable, let's say, a file named "Testfile1.txt" in the root of the file share, all that's required is:
INSERT INTO dbo.Documents(Name, file_stream) VALUES('Testfile1.txt', 0x);
But, if I have a directory named "SQLFiles" at the root? How do I create a file in that directory? You might think parent_path_locator, but that's a computed column. Well, how about…
SELECT @pathstring = path_locator.ToString() from documents where name = 'SQLFiles';
SET @newpath = @pathstring + 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))) + '/';
INSERT INTO dbo.Documents(Name, path_locator, file_stream) VALUES('SQLFilesTest.txt', @newpath, 0x);
You could also just use "@pathstring + '1/'" but this sticks to the "native" encoding scheme. And no, newid() can't be used in a function, if that's what you're thinking.
Now that I've got a zero-length file, can I open it and edit it with Notepad.exe? Well, you can if its empty. But once there is data in the file, attempting to edit with Notepad.exe returns an error, "This request is not supported". This is because Notepad.exe uses memory-mapped files, a win32 feature which isn't supported by FileTable. Paint.exe uses them too. So you'd need to open it from a remote location. Actually, connecting the share as a network drive will work as well.
But what if there are directories named "SQLFiles" at different subdirectory levels? How can we distinguish between them? That's where the hierarchyid comes in. I'll continue with that next.
@bobbeauch