File Table in SQL Server 2012

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.

First we need to enable the FILE STREAM in SQL Server instance level. Follow the below mentioned steps.

1. Open the SQL Server Configuration Manager by Start -> All Programs -> Microsoft SQL Server 2012 -> Configuration Tools -> SQL Server Configuration Manager

2. Right click on the SQL Server instance under the SQL Server Services menu and select Properties.

3. SQL Server Instance Properties window will be opened. Select the FILESTRAEM tab and tick the below mentioned checkboxes and then click apply and ok

• Enable FILESTREAM for Transact-SQL access 
• Enable FILESTREAM for file I/O access
• Allow remote clients access to FILESTREAM data

4. Execute the below mentioned Transact-SQL code in the SSMS.

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

5. Restart the SQL Server Instance.

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 MyFileTableTest

ON  PRIMARY

(

    NAME = N'MyFileTableTest',

    FILENAME = N'G:\Demo\MyFileTableTest.mdf'

),

FILEGROUP FilestreamFG CONTAINS FILESTREAM

(

    NAME = MyFileStreamData,

    FILENAME= 'G:\Demo\Data'

)

LOG ON

(

    NAME = N'MyFileTableTest_Log',

    FILENAME = N'G:\Demo\MyFileTableTest_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 MyFileTableTest

go

CREATE TABLE MyDocumentStore AS FileTable

WITH

(

    FileTable_Directory = 'MyDocumentStore',

    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 

UPDATE MyDocumentStore

SET name = 'FileNameChanged.txt'

WHERE stream_id = 'A124C74D-CCD3-E111-87F1-0019D1EE2D0C'