FileTables in SQL Server 2012

SQL Server 2008 gave us filestreams. Filestream saves files on the disk instead of using BLOB’s. This was done to get better performance. Now the SQL Server 2012 is here with a new feature called FileTables. With FileTables you can store files and documents in special tables in SQL Server called FileTables, but access them from the filesystem. Let's see how it works.

The FileTable feature builds on top of SQL Server FILESTREAM technology. Therefore the first step is to enable filestreams on the SQL SERVER Service.

Open the SQL Server Configuration Manager and open the property window for the SQL Server Service. Open the FIELSTREAM tab and enable the FILESTEAM Feature.

Open SQL Server Management Studio and run the following SQL statement

<span style="color:blue;">EXEC </span><span style="color:maroon;">sp_configure </span><span style="color:teal;">filestream_access_level</span><span style="color:gray;">, </span>2
<span style="color:blue;">RECONFIGURE </span>

Restart the SQL Server Service.

The next step is to create a database with FILESTEAM enabled. Run the following SQL statement to create a database called sandbox

<span style="color:blue;">CREATE DATABASE </span><span style="color:teal;">Sandbox </span><span style="color:blue;">ON PRIMARY </span><span style="color:gray;">( </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:red;">N'Sandbox'</span><span style="color:gray;">, </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">N'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATASandbox.mdf' </span><span style="color:gray;">), </span><span style="color:blue;">FILEGROUP </span><span style="color:teal;">F1 </span><span style="color:magenta;">CONTAINS </span><span style="color:blue;">FILESTREAM </span><span style="color:gray;">( </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:teal;">F1</span><span style="color:gray;">, </span><span style="color:blue;">FILENAME</span><span style="color:gray;">= </span><span style="color:red;">'C:TmpF1' </span><span style="color:gray;">) </span><span style="color:magenta;">LOG </span><span style="color:blue;">ON </span><span style="color:gray;">( </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:red;">N'Sandbox_Log'</span><span style="color:gray;">, </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">N'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATASandbox.ldf' </span><span style="color:gray;">) </span><span style="color:blue;">WITH FILESTREAM </span><span style="color:gray;">( </span><span style="color:teal;">NON_TRANSACTED_ACCESS </span><span style="color:gray;">= </span><span style="color:blue;">FULL</span><span style="color:gray;">, </span><span style="color:teal;">DIRECTORY_NAME </span><span style="color:gray;">= </span><span style="color:red;">N'FT' </span><span style="color:gray;">) </span>

Now we need to create a table for all the files.

<span style="color:blue;">USE </span><span style="color:teal;">Sandbox</span><span style="color:gray;">; </span><span style="color:blue;">GO CREATE TABLE </span><span style="color:teal;">Files </span><span style="color:blue;">AS FileTable WITH </span><span style="color:gray;">( </span><span style="color:teal;">FileTable_Directory </span><span style="color:gray;">= </span><span style="color:red;">'Files'</span><span style="color:gray;">, </span><span style="color:teal;">FileTable_Collate_Filename </span><span style="color:gray;">= </span><span style="color:teal;">database_default </span><span style="color:gray;">) </span>

Refresh SQL Server Management Studio to see the table we created.

You will also see a directory called F1 at the Tmp directory at your C-drive. This is where the files is stored, due to the CREATE DATABASE statement.

In SQL Server Management Studio right-click the table called Files and select “explore filetable directory”. This is the path you can use if you want to access the files directly from the file system. Copying some files to the directory and run the following SQL Statement will give you a result of the files copied to the directory including some metadata for the files.

<span style="color:blue;">USE </span><span style="color:teal;">Sandbox</span><span style="color:gray;">; </span><span style="color:blue;">GO SELECT </span><span style="color:gray;">* </span><span style="color:blue;">FROM </span><span style="color:teal;">Files </span>

With FileTables I will now consider saving files into SQL Server instead of just saving the part in the database and save the files in the file system. With FileTables my C# applications or reporting service reports still can access the files, as whey the files stores in the file system but backup is done by the dba’s. Also the fact that I can run a full-text search on the files is very powerful.

Any questions?
Please reach out to info@inspari.dk or +45 70 24 56 55 if you have any questions. We are looking forward to hearing from you.