FileTable: SQL Server 2012’s little gasp-maker

When was the last time you looked at a SQL Server new feature list and said, “Ohhh, WOW!”?  Was it around March of last year? No? Then you might have missed something really, really cool.

Let me introduce you to my friend, SQL Server 2012’s FileTable.

A Brief History of Files

Before SQL Server 2008, if you wanted to store file information (“unstructured data”) in your database, you had two options: you could either store your file in the database as an IMAGE (or VARBINARY) column, which was good for management, but pretty damn bad for performance. Or, you could store the file in the file system, and store a link in a database…great for performance, lousy for managing your files and keeping them in sync with the database.

Microsoft addressed this in SQL Server 2008 with FILESTREAM, which lets you store files in the NT file system, and keep them transactionally consistent with the database. What’s more, you can search those files’ contents using Full-text search. Good stuff, that.

FileTable Defined

Now in SQL Server 2012, Microsoft has built FileTable on the FILESTREAM foundation.  FileTables are a special kind of table that stores files in the database – where they can be managed, accessed, backed up, and searched (via Full-text or the new and similarly awesome semantic search). AND! AND! AND! Those files can be accessed from Windows as if they were stored on the NT file system.  SQL Server exposes a “virtual file share”, which you can open up just like any UNC and look at, add, remove, and modify files all the day long.

You can access FileTable files just like any other file stored on the server: programatically, or via a directory window!

That’s really spectacularly cool, but you’ve got to see it in action.

Get Set Up for FileTable

Before you can use FileTable on an instance, get your prerequisites in order! Note: Standard warnings about testing this in a test environment apply. If you run out and do this on production without trying it out elsewhere first, it’s your own durn fault, kids.

Enable and configure Filestream:

  1. Open SQL Server Configuration Manager.
  2. Bring up Properties for your instance.
  3. Select Enable Filestream for T-SQL Access AND for File I/O Access
  4. And run this statement in SSMS:
    EXEC sp_configure filestream_access_level, 2
  5. Restart the SQL Server service.

Easy-easy. Now, for our example, we’ll create a database with Filestream:

( NAME = N'FileTableDemo',
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileTableDemo.mdf'),
( NAME = FilestreamData,
 FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileTableDemoFS' )
( NAME = N'FileTableDemoLog',
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileTableDemoLog.ldf')

As of this moment, your virtual directory is available, at \\{servername}\MSSQLSERVER\FileTable\. BUT you can’t do anything with it yet! You need an actual FileTable, to put your files into.

FileTable in Action

Now let’s create a FileTable:

CREATE TABLE JensAwesomeFiles AS FileTable
( FileTable_Directory = 'JenAwesomeFiles', 
 FileTable_Collate_Filename = database_default

Very simple, very easy. You don’t list any column names or attributes, because a FileTable has a fixed structure that SQL Server already knows. You can now SELECT * FROM JensAwesomeFiles (though it is currently empty), and access it at \\{servername}\MSSQLSERVER\FileTable\JenAwesomeFiles\.

Now, drop a file in that directory. Go ahead, any old file. Create a new text file and save it there, or copy a JPG in. Then when you select from JensAwesomeFiles in SSMS, it shows up:

Go ahead, copy a bunch of files in and select. It’s fun! Know what’s more fun? Changing file names, extensions, or attributes with T-SQL commands. For example:

UPDATE JensAwesomeFiles SET is_readonly = 1 WHERE file_type = 'docx'; -- Set the readonly attribute!
UPDATE JensAwesomeFiles SET NAME = 'NEW NAME_' + name WHERE creation_time > GETDATE()-1; -- Change file names, based on creation date!
DELETE JensAwesomeFiles WHERE is_readonly = 0; -- Delete files RIGHT OUTTA THERE!

After each change, go take a look at the virtual directory, just to Ooh and Ahh over the changes.

What’s it For?

Of course, like anything in computers, this feature is for whatever you can think of. I’ve used it to streamline and improve document handling and searching in companies with legacy applications – where we couldn’t alter the actual file-in-file-out process, but we could point the app to a new directory….and so all our files were dropped straight into SQL Server. Aww, yeah…

Oh, and you PowerShell freaks out there: Tell me you couldn’t have TONS of fun by combining POSH with FileTable. Go on, I dare ya.

Drop me a line, kids, with your “oh WOW”s, your questions, your corrections, and how you’ve used (or will use) FileTable.

Happy days,
Jen McCown

7 thoughts on “FileTable: SQL Server 2012’s little gasp-maker”

  1. Jen,

    Thank you for this article, I can see many happy days using this sort of thing to manage flat files.

    Tell me what is the file size limitation and what about compression?

  2. Hi Jen,
    I am currently rebuilding an old MS Forms project written in VB6, and I think sql server7. (Great fun….. NOT)
    It is based around Mobile telephone sites, so has “Country”, “Region”, “Site Number”, “Document Type” e.g. A lease or a survey or …..
    So many things that you could do 8 years ago are not allowed now !!! I am assuming for security reasons.
    Anyway, I have got to the form for documents I have been searching on line and came across FILESTREAM so have set up the sql documents table with filestream where the BLOB’s are stored in a folder on the C:\ drive. (Not tested yet as I have since found FILETABLE)
    Do I point the new FileTable to the old Filestream Folder @ C:\CellRKiveDocuments or do I have to change the code to save the document into the filetable??
    Can I then delete the CellRKiveDocuments folder from C:??
    If you have time, please be very precise on what I would need to do as to be honest I am getting totally confused with all these new limitations imposed in vs2013
    Thank you in advance

Leave a Reply

Your email address will not be published. Required fields are marked *