I have consolidated some frequently asked questions for FileTable. I’ll try to cover as much as I can so that all your questions related to Filetable can be clarified. All these answers are based on SQL Server 2012 version these may change in future version and editions which I’ll be updating with the latest release.
Can I modify Filetable?
You are allowed to modify Filetable path other than that you are not allowed to change the DDL of Filetable
How can I block access to Windows while loading data into FileTable?
Use the query below to disable windows access while you load data to filetable through SQL Server
ALTER TABLE FileTableName DISABLE FILETABLE_NAMESPACE
What happens if I drop FileTable?
Table will be dropped and it’s associated file will be deleted
Is it transaction scoped?
Yes it is however non transactional access will not be taken care by ACID property in SQL Server
Can I perform DML Operations in FileTable?
Yes you can. In addition to this you are allowed to access it from Windows too.
Is Triggers are supported on FileTable?
Yes both DDL & DML triggers are supported however DML trigger can’t update another FileTable
Is there any change required in my application?
Not at all, all windows operations can be performed as usual
Maintenance Operation and FileTable
All maintenance operations are supported. Point in time recovery may be possible with recent version of the file that’s backed up through maintenance plan.
Security and FileTable
All security can be enforced both in SQL as well as Win32 access
High Availability and FileTable?
AlwaysOn is fully supported
Virtual Network Name Support for application failure
Supports multiple secondary nodes
Cannot participate in read-only replicas
Replication is not supported
Partitioning and FileTable?
Partitions are not supported
Can I change my existing table as FileTable?
No that’s not possible
Can I create FileTable in system databases?
No you can’t create FileTable in system databases
Can temportary table or table variable be declared as FileTable?
No you can’t do that