Access FileTables with File Input-Output APIs

Artikkeli
02/28/2023

Applies to: SQL Server

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.

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:

Directory Management
File Management

FileTables do not support the following operations:

Disk Management
Volume Management
Transactional NTFS

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).

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.

Triggers

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

Capability	Supported	Comments
Oplocks	Yes	There is support for Level 2, Level 1, Batch and Filter oplocks.
Extended Attributes	No
Reparse Points	No
Persistent ACLs	No
Named Streams	No
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.
Compression	Yes
Encryption	Yes
TxF	No
File Ids	No
Object Ids	No
Symbolic links	No
Hard links	No
Short names	No
Directory change notifications	No
Byte range locking	Yes	Requests for byte range locking are passed to the NTFS file system.
Memory mapped files	No
Cancel I/O	Yes
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.

Jaa