This is another post on using FileTables in SQL 2012. For background you can see my earlier posts, Using FileTables in SQL Server 2012 and Converting LOB data to SQL 2012 FileTables.

Assuming that you’ve already enabled FILESTREAMING on your SQL instance, and you have a database with a FILESTREAM filegroup, then you can create a FileTable with a single line. In this case I’m creating a FileTable called Documents.

CREATE TABLE dbo.Documents AS FILETABLE

SQL does all the work for you, creating a table with 17 columns. Using the object explorer in SSMS or executing the sp_help procedure for your table will show the table definition.

image

SQL creates 3 non clustered indexes on your new FileTable; a primary key on the path_locator column, and 2 unique constraints, one on the parent_path_locator and name columns and one on the stream_id column. SQL will use its own naming convention when it creates them, but you can choose your own names when you create the FileTable. At the same time you can choose the directory name (SQL will use the table name as the default).

CREATE TABLE dbo.Documents AS FILETABLE WITH
(   FILETABLE_DIRECTORY = 'DocumentsFT',
    FILETABLE_COLLATE_FILENAME = database_default,
    FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME = PK_Documents,
    FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME = UQ_Documents_StreamID,
    FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME = UQ_Documents_Fullpath
);

SQL also creates a number of constraints on the table. You can see them with the sp_help procedure or by querying sys.filetable_system_defined_objects.

SELECT OBJECT_NAME(object_ID) AS CreatedObject, object_id,
       OBJECT_NAME(parent_object_id) AS Parent, parent_object_id
FROM sys.filetable_system_defined_objects;

image

By looking at the constraint definition you’ll understand the table a bit better. For instance the default constraint for the file metadata columns (is_directory, is_hidden, is_readonly, is_archive, etc.) is 0, except for is_archive which is 1. The constraint for the path_locator column calls newid 3 times to build a hierarchyid.

(convert(hierarchyid, '/' +
convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 1, 6))) + '.' +
convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 7, 6))) + '.' +
convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 13, 4))) + '/'))

One last word on FileTables. You can’t add, delete or rename columns in the table. You can add new indexes and constraints but you can’t remove the ones that already exist, though you can rename them.

References

There are some very good references on FileTables. One of the best is by Sven Aelterman on BeyondRelational

http://beyondrelational.com/modules/24/syndicated/415/sven-aeltermans-blog.aspx?tab=Posts&tn=Tags&tv=FileTable

Mr. Aelterman co-wrote The Art of Filestream with Jacob Sebastian. It’s a fantastic reference on FILESTREAM, and there’s a chaper on FileTables..