I've been working with the SQL Server 2012 FileTable feature lately. Besides learning to appreciate the esoteric features of the NTFS file system and SMB protocol, only some of which are supported by FileTables, I've been trying to work with FileTables in SQL Server using T-SQL. This turns out to be an interesting exercise, especially if you're trying to brush up on your skills with the hierarchyid data type.
It turns out that a FileTable is just like a normal SQL Server table with a filestream varbinary(max) column (named, unsurprisingly, file_stream). It uses computed columns and constraints rather extensively. Behind the scenes, SQL Server functions as a Win32 namespace owner and exposes a virtual file share. Although the namespace management and fitting non-transactional access into a transaction-based system, and its easy to think of the share as a real file share, SQL Server manages everything. And you can manage the FileTable data completely with T-SQL. This one's for the folks that think *everything* ought to be managed in T-SQL. But before trying to create and manage files with T-SQL, I looked into the FileTable specific functions, GetFileNamespacePath(), FileTableRootPath(), and GetPathLocator(). These will be useful and they have some interesting options.
GetFileNamespacePath() is exposed as a method on the file_stream column.Besides being used without options, it has two options. The first option allows you to get a full namespace path instead of a relative path. Relative path is the default. So if, for example, you have a FileTable named "Documents" on a database named FTTest on a machine named ZMV04, your full path for a file at the root named Testfile1.txt will be "\\ZMV04\MSSQLSERVER\FTTest\Documents\Testfile1.txt", and relative path would be "\Documents\Testfile1.txt". A second option lets you decide whether you want a NETBIOS name, Machine name, or Domain name in your path.
FileTableRootPath() is a global function. It produces slightly different names for the root depending on whether or not you use the first parameter, the name of the filetable. Without this parameter, FileTableRootPath() doesn't include the part of the path that includes the filetable. So, in our case its "\\ZMV04\MSSQLSERVER\FTTest". When the filetable name is specified this function returns "\\ZMV04\MSSQLSERVER\FTTest\Documents. This option makes a difference when using this method to construct full paths, as we'll soon see. There's a second option for path using NETBIOS name, Machine name, FQDN is analogous to GetFileNamespacePath().
Finally there is another global function, GetPathLocator(). This one was kind of strange, even though the use case provided by the BOL was clear enough. You'd use it if you have existing (pre-FileTable) SQL Server tables that use path locators. Path locators, if you're unfamiliar with the term, refer to storing a path name as a column value in a table. You'd use that name, stored in the database, to locate the file to open and read/write. The drawback to traditional path locators is that its up to you to keep the file location in the database in sync with the filesystem. And, of course, the files that file locators point to have no intergrated backup/restore with the database. With FileTables, management is taken care of by the database engine, along with coordinated backup and restore and other nice database features.
The twist is that FileTables don't directly expose file paths, although you can derive them using our first two functions. FileTables use the hierarchyid data type, stored in a column path_locator. The GetPathLocator() function can get you a path_locator (hierarchyid) given a valid string data type FileTable-produced path name. So, if I have a file "TestFile1.txt" in the root of a FileTable named "Documents", I can get its path locator hierarchyid by using GetPathLocator(file_stream.GetFileNamespacePath(1)). Of course, you can simply use the column value in the path_locator column, but this function may come in handy later on.
This posting has gone a little long, so I'll continue it in a subsequent posting.
@bobbeauch