Work with Directories and Paths in FileTables

Describes the directory structure in which the files are stored in FileTables.

In This Topic

I want to …

• Work with Directories and Paths in FileTables

• Use Relative Paths for Portable Code

Learn more

• Important restrictions

  • Nesting level

  • Length of full path name

• The full path to an item stored in a FileTable

• The semantics of the root directories at the instance, database, and FileTable levels

• The is_directory column in the FileTable schema

• Using Virtual Network Names (VNNs) with AlwaysOn Availability Groups

How To: Work with Directories and Paths in FileTables

You can use the following 3 functions to work with FileTable directories in Transact-SQL:

[TOP]

How to: Use Relative Paths for Portable Code

To keep code and applications independent of the current computer and database, avoid writing code that relies on absolute file paths. Instead, get the complete path for a file at run time by using the FileTableRootPath (Transact-SQL) and GetFileNamespacePath (Transact-SQL)) functions together, as shown in the following example. By default, the GetFileNamespacePath function returns the relative path of the file under the root path for the database.

USE database_name;
DECLARE @root nvarchar(100);
DECLARE @fullpath nvarchar(1000);

SELECT @root = FileTableRootPath();
SELECT @fullpath = @root + file_stream.GetFileNamespacePath()
    FROM filetable_name
    WHERE name = N'document_name';

PRINT @fullpath;
GO

[TOP]

Important restrictions

Nesting level

Length of full path name

The full path to an item stored in a FileTable

The full path to a file or directory stored in a FileTable begins with the following elements:

1. The share enabled for FILESTREAM file I/O access at the SQL Server instance level.

2. The DIRECTORY_NAME specified at the database level.

3. The FILETABLE_DIRECTORY specified at the FileTable level.

The resulting hierarchy looks like this:

\\<machine>\<instance-level FILESTREAM share>\<database-level directory>\<FileTable directory>\

This directory hierarchy forms the root of the FileTable’s file namespace. Under this directory hierarchy, the FILESTREAM data for the FileTable is stored as files, and as subdirectories which can also contain files and subdirectories.

It is important to keep in mind that the directory hierarchy created under the instance-level FILESTREAM share is a virtual directory hierarchy. This hierarchy is stored in the SQL Server database and is not represented physically in the NTFS file system. All operations that access files and directories under the FILESTREAM share and in the FileTables that it contains are intercepted and handled by a SQL Server component embedded in the file system.

[TOP]

The semantics of the root directories at the instance, database, and FileTable levels

This directory hierarchy observes the following semantics:

• The instance-level FILESTREAM share is configured by an administrator and stored as a property of the server. You can rename this share by using SQL Server Configuration Manager. A renaming operation does not take effect until the server is restarted.

• The database-level DIRECTORY_NAME is null by default when you create a new database. An administrator can set or change this name by using the ALTER DATABASE statement. The name must be unique (in a case-insensitive comparison) in that instance.

• You typically provide the FILETABLE_DIRECTORY name as part of the CREATE TABLE statement when you create a FileTable. You can change this name by using the ALTER TABLE command.

• You cannot rename these root directories through file I/O operations.

• You cannot open these root directories with exclusive file handles.

[TOP]

The is_directory column in the FileTable schema

The following table describes the interaction between the is_directory column and the file_stream column that contains the FILESTREAM data in a FileTable.

[TOP]

Using Virtual Network Names (VNNs) with AlwaysOn Availability Groups

When the database that contains FILESTREAM or FileTable data belongs to an AlwaysOn availability group:

• The FILESTREAM and FileTable functions accept or return virtual network names (VNNs) instead of computer names. For more information about these functions, see Filestream and FileTable Functions (Transact-SQL).

• All access to FILESTREAM or FileTable data through the file system APIs should use VNNs instead of computer names. For more information, see FILESTREAM and FileTable with AlwaysOn Availability Groups (SQL Server).

[TOP]

See Also

Concepts

Enable the Prerequisites for FileTable

Create, Alter, and Drop FileTables

Access FileTables with Transact-SQL

Access FileTables with File I\O APIs