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.

Read more about FileTables in BOL

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'C:\FileTable\MyFileTableTest.mdf'
),
FILEGROUP FilestreamFG CONTAINS FILESTREAM
(
    NAME = MyFileStreamData,
    FILENAME= 'C:\FileTable\Data'
)
LOG ON
(
    NAME = N'MyFileTableTest_Log',
    FILENAME = N'C:\FileTable\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 – easy peasy .

UPDATE MyDocumentStore
SET name = 'Geniiius.txt'
WHERE stream_id = '05A9F338-CF32-E111-BB43-080027F9EB26'

This is the end of the first blog post in this series – stay tuned new posts will be online very soon.