Create, Alter, and Drop FileTables
Describes how to create a new FileTable, or alter or drop an existing FileTable.
I want to …
A FileTable is a specialized user table that has a pre-defined and fixed schema. This schema stores FILESTREAM data, file and directory information, and file attributes. For information about the FileTable schema, see FileTable Schema.
You can create a new FileTable by using Transact-SQL or SQL Server Management Studio. Since a FileTable has a fixed schema, you do not have to specify a list of columns. The simple syntax for creating a FileTable lets you specify:
A directory name. In the FileTable folder hierarchy, this table-level directory becomes the child of the database directory specified at the database level, and the parent of the files or directories stored in the table.
The name of the collation to be used for file names in the Name column of the FileTable.
The names to be used for the 3 primary key and unique constraints that are automatically created.
How To: Create a FileTable
Requirements and Restrictions for Creating a FileTable
You cannot alter an existing table to convert it into a FileTable.
The parent directory previously specified at the database level must have a non-null value. For information about specifying the database-level directory, see Enable the Prerequisites for FileTable.
A FileTable requires a valid FILESTREAM filegroup, since a FileTable contains a FILESTREAM column. You can optionally specify a valid FILESTREAM filegroup as part of the CREATE TABLE command for creating a FileTable. If you do not specify a filegroup, then the FileTable uses the default FILESTREAM filegroup for the database. If the database does not have a FILESTREAM filegroup, then an error is raised.
You cannot create a table constraint as part of a CREATE TABLE…AS FILETABLE statement. However you can add the constraint later by using an ALTER TABLE statement.
You cannot create a FileTable in the tempdb database or in any of the other system databases.
You cannot create a FileTable as a temporary table.
Since a FileTable has a pre-defined and fixed schema, you cannot add or change its columns. However, you can add custom indexes, triggers, constraints, and other options to a FileTable.
For information about using the ALTER TABLE statement to enable or disable the FileTable namespace, including the system-defined constraints, see Manage FileTables.
How To: Change the Directory for a FileTable
Requirements and Restrictions for Altering a FileTable
You cannot alter the value of FILETABLE_COLLATE_FILENAME.
You cannot change, drop, or disable the system-defined columns of a FileTable.
You cannot add new user columns, computed columns, or persisted computed columns to a FileTable.
You can drop a FileTable by using the ordinary syntax for the DROP TABLE (Transact-SQL) statement.
When you drop a FileTable, the following objects are also dropped:
All the columns of the FileTable and all the objects associated with the table, such as indexes, constraints, and triggers, are also dropped.
The FileTable directory and the sub-directories that it contained disappear from the FILESTREAM file and directory hierarchy of the database.
The DROP TABLE command fails if there are open file handles in the FileTable’s file namespace. For information about closing open handles, see Manage FileTables.
When you create a new FileTable, some system-defined indexes and constraints are also created. You cannot alter or drop these objects; they disappear only when the FileTable itself is dropped. To see the list of these objects, query the catalog view sys.filetable_system_defined_objects (Transact-SQL).
--View all objects for all filetables, unsorted SELECT * FROM sys.filetable_system_defined_objects; GO --View sorted list with friendly names SELECT OBJECT_NAME(parent_object_id) AS 'FileTable', OBJECT_NAME(object_id) AS 'System-defined Object' FROM sys.filetable_system_defined_objects ORDER BY FileTable, 'System-defined Object'; GO