SQL Server 2012 FileTable

In a series of blog posts we will have a look at the new SQL Server 2012 table type called FileTable. This first blog post will be a simple getting started – how to create a FileTable and how to dump files into the folder and do some simple file manipulation.
A SQL Server FileTable is a special table where you can store directory and files – that’s not special I know, the special thing is that you can access these files and directories from windows applications as if they were stored in the file system.The data stored in the FileTable is exposed to windows through a windows share, and via this share it is possible to gain non-transactional access to the data.If you create or change a file through the windows share the command is intercepted by a SQL Server component and the changes are applied to the corresponding data in the FileTable.
Let’s create a demo database with a FILESTREAM filegroup, I’ll be setting the NON_TRANSACTED_ACCESS to FULL this gives me full non-transactional access to the share, this option can be changed later if you want to do that. Here is the code:

    CREATE DATABASE FileTableDB
    ON  PRIMARY  
    (  
        NAME = N'FileTableDB',  
        FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\FileTableDB.mdf'  
    ),  
    FILEGROUP FilestreamFG CONTAINS FILESTREAM      
    (  
        NAME = MyFileStreamData,  
        FILENAME= 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\Data'  
    )  
    LOG ON  
    (  
        NAME = N'MyFileTableTest_Log',  
        FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\FileTableDB_log.ldf'  
    )  
    WITH FILESTREAM  
    (  
        NON_TRANSACTED_ACCESS = FULL,  
        DIRECTORY_NAME = N'FileTable'  
    )  

That’s the database done, now let’s create the magic table – that is simple done with the “AS FileTable” keyword. When creating a FileTable there is only two options that you can change, everything else is out of the box. Let’s have a look at how I created the table:

    USE FileTableDB  
    GO
      
    CREATE TABLE MyDocumentStore AS FileTable  
    WITH  
    (  
        FileTable_Directory = 'FileTableDB',  
        FileTable_Collate_Filename = database_default  
    );  
    GO  

If we take a look at the GUI after creating the table, we can see that the table is created under the new folder called FileTables





that is not the only new thing, if we right click at the table that we just created we will see an option called “Explore FileTable Directory” – click that and a windows opens with the content of your FileTable. If you drop a file into this folder it will be accessible from SSMS, with a simple SELECT from the MyDocumentStore.

Here is how the folder looks:



And if you query the table you get the following: (This query only shows a few of the columns off the table)



Now you can do file manipulation with SSMS, something like renaming the files is just an update statement.