Access FileTables with File Input-Output APIs


Updated: August 25, 2016

Describes how file system I/O works on a FileTable.

The primary usage of FileTables is expected to be through the Windows file system and file I/O APIs. FileTables support non-transactional access through the rich set of available file I/O APIs.

  1. File I/O API access typically begins by acquiring a logical UNC path for the file or directory. Applications can use a Transact-SQL statement with the GetFileNamespacePath (Transact-SQL) function to obtain the logical path for the file or directory. For more information, see Work with Directories and Paths in FileTables.

  2. Then the application uses this logical path to obtain a handle to the file or directory and do something with the object. The path can be passed to any supported file system API function, such as CreateFile() or CreateDirectory(), to create or open a file and obtain a handle. The handle can then be used to stream data, to enumerate or organize directories, to get or set file attributes, to delete files or directories, and so forth.

A file or directory can be created in a FileTable by calling file I/O APIs such as CreateFile or CreateDirectory.

  • All creation disposition flags, share modes, and access modes are supported. This includes file creation, deletion and in-place modification. Also supported are File Namespace updates i.e. directory creation/deletion, rename and move operations.

  • The creation of a new file or directory corresponds to the creation of a new row in the underlying FileTable.

  • For files, the stream data is stored in the file_stream column; for directories, this column is null.

  • For files, the is_directory column contains false. For directories, this column contains true.

  • Sharing and concurrency of access are enforced when multiple concurrent file I/O operations or Transact-SQL operations affect the same file or directory in the hierarchy.

Read Committed isolation semantics are enforced in SQL Server for all file I/O access operations on stream and attribute data.

  • All file I/O write or update operations on a FileTable are non-transactional. That is, no SQL Server transaction is bound to these operations, and no ACID guarantees are provided.

  • All file I/O streaming/in-place updates are supported for the FileTable.

  • Updates to the FILESTREAM data or attributes through file I/O APIs result in updates of the corresponding file_stream and file attribute columns in the FileTable.

All Windows file I/O API semantics are enforced when you delete a file or directory.

  • Deleting a directory fails if the directory contains any files subdirectories.

  • Deleting a file or directory removes the corresponding row from the FileTable. This is equivalent to deleting the row through a Transact-SQL operation.

FileTables support the file system APIs related to the following file system operations:

  • Directory Management

  • File Management

FileTables do not support the following operations:

  • Disk Management

  • Volume Management

  • Transactional NTFS

Using Virtual Network Names (VNNs) with Always On Availability Groups

When the database that contains FILESTREAM or FileTable data belongs to an Always On availability group, then 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 Always On Availability Groups (SQL Server).

Partial Updates

A writable handle obtained for FILESTREAM data in a FileTable by using the GetFileNamespacePath (Transact-SQL) function can be used to make in-place, partial updates to the FILESTREAM content. This behavior is different from the transacted FILESTREAM access through a handle obtained by calling OpenSQLFILESTREAM() and passing an explicit transaction context.

Transactional Semantics

When you access the files in a FileTable by using file I/O APIs, these operations are not associated with any user transactions, and have the following additional characteristics:

  • Since non-transacted access to FILESTREAM data in a FileTable is not associated with any transaction, it does not have any specific isolation semantics. However SQL Server may use internal transactions to enforce locking or concurrency semantics on the FileTable data. Any internal transactions of this type are done with read-committed isolation.

  • There are no ACID guarantees for these non-transacted operations on FILESTREAM data. The consistency guarantees are similar to those for file updates made by applications in the file system.

  • These changes cannot be rolled back.

However, the FILESTREAM column in a FileTable can also be accessed with transactional FILESTREAM access by calling OpenSqlFileStream(). This kind of access can be fully transactional and will honor all the levels of transactional consistently that are currently supported.

Concurrency Control

SQL Server enforces concurrency control for FileTable access among file system applications, and between file system applications and Transact-SQL applications. This concurrency control is achieved by taking appropriate locks on the FileTable rows.


Creating, modifying, or deleting files or directories or their attributes through the file system results in corresponding insert, update, or delete operations in the FileTable. Any associated Transact-SQL DML triggers are fired as part of these operations.

OplocksYesThere is support for Level 2, Level 1, Batch and Filter oplocks.
Extended AttributesNo
Reparse PointsNo
Persistent ACLsNo
Named StreamsNo
Sparse FilesYesSparseness can be set only on files, and affects the storage of the data stream. Since FILESTREAM data is stored on NTFS volumes, the FileTable feature supports sparse files by forwarding the requests to the NTFS file system.
File IdsNo
Object IdsNo
Symbolic linksNo
Hard linksNo
Short namesNo
Directory change notificationsNo
Byte range lockingYesRequests for byte range locking are passed to the NTFS file system.
Memory mapped filesNo
Cancel I/OYes
SecurityNoWindows share level security and SQL Server table and column level security are enforced.
USN journalNoMetadata changes to files and directories in a FileTable are DML operations on a SQL Server database. Therefore they are logged in the corresponding database log file. However they are not logged in the NTFS USN journal (except for changes in size).

 SQL Server change tracking capabilities can be used to capture similar information.

Load Files into FileTables
Work with Directories and Paths in FileTables
Access FileTables with Transact-SQL
FileTable DDL, Functions, Stored Procedures, and Views

Community Additions