Access FileTables with File I\O APIs

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

In This Topic

I want to …

• Get Started Using File I/O APIs with FileTables

• Create Files and Directories in a FileTable

• Read Files and Directories in a FileTable

• Update Files and Directories in a FileTable

• Delete Files and Directories in a FileTable

Learn more

• Supported File System Operations

• Additional Considerations for File I/O Access to FileTables

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

  • Partial Updates

  • Transactional Semantics

  • Concurrency Control

  • Triggers

• File System Functionality Supported in FileTables

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.

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.

[TOP]

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.

[TOP]

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.

[TOP]

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.

[TOP]

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

[TOP]

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

[TOP]

Additional Considerations for File I/O Access to FileTables

Using Virtual Network Names (VNNs) with AlwaysOn Availability Groups

When the database that contains FILESTREAM or FileTable data belongs to an AlwaysOn 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 AlwaysOn 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.

[TOP]

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.

[TOP]

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.

[TOP]

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.

[TOP]

File System Functionality Supported in FileTables

[TOP]

See Also

Concepts

Load Files into FileTables

Work with Directories and Paths in FileTables

Access FileTables with Transact-SQL

FileTable DDL, Functions, Stored Procedures, and Views