Access FileTables with File Input-Output APIs
Describes how file system I/O works on a FileTable.
Get Started Using File I/O APIs with FileTables
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.
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.
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.
We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. You can click the This page button in the Feedback section at the bottom of this page. We read every item of feedback about SQL, typically the next day. Thanks.
Creating Files and Directories in a FileTable
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.
Reading Files and Directories in a FileTable
Read Committed isolation semantics are enforced in SQL Server for all file I/O access operations on stream and attribute data.
Writing and Updating Files and Directories in a FileTable
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.
Deleting Files and Directories in a 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 or 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.
Supported File System Operations
FileTables support the file system APIs related to the following file system operations:
FileTables do not support the following operations:
Additional Considerations for File I/O Access to FileTables
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).
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.
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.
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.
File System Functionality Supported in FileTables
|Oplocks||Yes||There is support for Level 2, Level 1, Batch and Filter oplocks.|
|Sparse Files||Yes||Sparseness 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.|
|Directory change notifications||No|
|Byte range locking||Yes||Requests for byte range locking are passed to the NTFS file system.|
|Memory mapped files||No|
|Security||No||Windows share level security and SQL Server table and column level security are enforced.|
|USN journal||No||Metadata 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.