With the introduction of the FileTable feature in SQL Server 2012, a few new supporting functions and stored procedures were also added. One of these functions is an intrinsic function called GetFileNamespacePath which you can call on the file_stream column of a FileTable. The function returns the UNC path of the file in question.
But didn’t we already have a function that does that? The PathName function, which has been around since SQL Server 2008’s introduction of FILESTREAM, also returns the UNC path to a file. Why do we need both? See below for a quick comparison table. Below the table are some more details.
| PathName | GetFileNamespacePath | |
|---|---|---|
| Use with |
When transactional consistency is desired. |
FileTable only (1)No transactional consistency is provided. |
| Case-sensitive? | Yes (2) | Yes (2) |
| Sample Output from FILESTREAM field (SQL 2012) |
\\LAPTOP\MSSQLSERVER\v02-A60EC2F8-2B24-11DF-9CC3-AF2E56D89593\Test_FILESTREAM\dbo\Filestream\blob\742A0E02-D0A1-4F62-A723-23828248CAB4\VolumeHint-HarddiskVolume2 | N/A |
| Sample Output from FileTable file_stream field (SQL 2012) |
\\LAPTOP\MSSQLSERVER\v02-A60EC2F8-2B24-11DF-9CC3-AF2E56D89593\Test_FILESTREAM\dbo\FileTable\file_stream\B805499E-BDDA-E111-A9CB-0050B64B3664\VolumeHint-HarddiskVolume2 | \\LAPTOP\MSSQLSERVER\Test_FILESTREAM\FileTable\file.txt |
| Requires transaction to open stream | Yes (3) | No |
(1): When you try to call this method on a FILESTREAM column, this error message is shown:
Msg 33419, Level 16, State 1, Line 7
Function GetFileNamespacePath is only valid on the varbinary(max) FILESTREAM column in a FileTable.
(2): Believe it or not, but regardless of collation case sensitivity settings, you must properly capitalize PathName and GetFileNamespacePath.
(3): Just to be clear, you can call the PathName function outside of a SQL transaction context, but you cannot create a SqlFileStream instance without an active SQL transaction.
Arguments
Both functions can take optional arguments. The arguments and their effects on the functions’ return value are described below.
@option
This optional argument can be used with PathName and GetFileNamespacePath. It determines if any formatting is applied to the computer name portion of the UNC path. There are 3 valid values:
0: The computer name is formatted in NetBIOS format (all UPPERCASE) and a maximum of 16 characters.
1: The computer name is not formatted and retrieved as is.
2: The computer name is formatted as a fully qualified DNS name (which would apply in Active Directory domains).
0 is the default value for this argument.
is_full_path
This argument exists only on the GetFileNamespacePath function. It determines if the return value should be a full path, meaning including the computer name, share name, and database root folder. The default value is 0, indicating that a relative path should be used. When you specify a value of 1 for this argument, the full path is returned. Below is an example of the relative path (default) and the full path:
\FileTable\file.txt
\\LAPTOP\MSSQLSERVER\Test_FILESTREAM\FileTable\file.txt
The maroon portion is the share name, the green portion the database root folder, the blue portion is the table root folder and the orange piece is the file or folder name. The orange piece could consist out of additional portions if the file or folder is stored in a parent folder, etc.
PathName And Folders
This, of course, only applies to FileTables as the concept of a folder isn’t inherently present in FILESTREAM.
Because the file_stream value of a folder record in a FileTable is NULL, the PathName function will return NULL for folders. The background behind this is that you wouldn’t open a folder using the SqlFileStream API. Only files can be opened in that way, and for a file to be opened with transactional consistency, there must be a non-NULL value in the FILESTREAM column.
Note that FileTable also supports transactional access.
Sven Aelterman 