FileTable Compatibility with Other SQL Server Features
Describes how FileTables work with other features of SQL Server.
When the database that contains FILESTREAM or FileTable data belongs to an AlwaysOn availability group:
FileTable functionality is partially supported by AlwaysOn Availability Groups. After a failover, FileTable data is accessible on the primary replica, but FileTable data is not accessible on readable secondary replicas.
Notice that after a failover all FILESTREAM functionality is supported. FILESTREAM data is accessible on both readable secondary replicas and on the new primary.
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).
Partitioning is not supported on FileTables. With the support for multiple FILESTREAM file groups, pure scale-up issues can be handled without having to resort to partitioning in most scenarios (unlike SQL 2008 FILESTREAMs).
Replication and related features (including transactional replication, merge replication, change data capture, and change tracking) are not supported with FileTables.
The query cannot contain reference to the FILESTREAM column in the FileTable, in the WHERE clause or any other part of the query.
SELECT INTO statements from a FileTable will not propagate the FileTable semantics on the created destination table (just like FILESTREAM columns in a regular table). All the destination table columns will behave just like normal columns. They will not have any FileTable semantics associated with them.
Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation (SI) rely on the ability to have a snapshot of the data available for readers even when update operations are happening on the data. However FileTables allow non-transactional write access to Filestream data. As a result, the following restrictions apply to the use of these features in databases that contain FileTables:
A database that contains FileTables can be altered to enable RCSI/SI.
When non_transactional access is set to FULL for the database, then a transaction running under RCSI or SI has the following behavior:
Any Transact-SQL reads of the FileTable file_stream column fail. INSERT and UPDATE to the column still succeed, as long as they do not read from the file_stream column.
If the Transact-SQL statement specifies READCOMMITTEDLOCK table hints, the reads succeed, and take locks on the rows, rather than use row versioning.
Transacted Win32 FileStream open requests also fail.
Non-transacted FileTable Win32 access succeeds. All internal queries done by FileTable are not affected.
Fulltext indexing always succeeds, no matter what the database options are (READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION).
The same considerations apply to readable secondary databases as to snapshots, as described in the preceding section, Snapshot Isolation and FileTables.
The FILESTREAM feature on which the FileTable feature depends requires some configuration outside of the database. Therefore a database that uses FILESTREAM or FileTable is not fully contained.
You can set database containment to PARTIAL if you want to use certain features of contained databases, such as contained users. In this case, however, you must be aware that some of the database settings are not contained in the database and are not automatically moved when the database moves.