Art of the DBA Rotating Header Image

April 10th, 2012:

T-SQL Tuesday #29 2012 Party: Dancing on the FileTable (#tsql2sday)

For this month’s edition of T-SQL Tuesday, Nigel Sammy(b|t) asks us to talk a bit about a feature of SQL 2012 that we’re excited about. Now, while my most favoritist SQL 2012 feature is the ability to install on Server Core, I’ve already written about that and I want to talk about something that hasn’t gotten quite so much publicity. Enter the File Table, new functionality that exposes more of the Windows file system to SQL Server. With it, we get some additional tools for file handling that before we had to do with other applications and workarounds like xp_cmdshell.

A File Table is a special type of table in SQL 2012. It’s got a fixed set of columns and points at a directory managed by SQL’s FILESTREAM technology. If you do a SELECT from the File Table, you’ll get a listing of all the files within that directory. What’s cool is that “inserting” rows into this table is as copying files into the directory. Once a file is in there, we can use use SQL to do other operations, like copying files between two different File Tables, or cleaning up old files based on the file dates or archive flags.

Setting up a File Table doesn’t take much, but since it uses FILESTREAM it can be a little tricky. The first thing you need to do is actually enable FILESTREAM for your SQL Server instance. To do so, just follow the steps from MSDN. For the purposes of this, I’m going to set up my share name as my instance name, SQL2012. This means that to get to my FILESTREAM objects and my File Tables, I’m going to be using the UNC path of \\SHION\SQL2012 for my laptop.

Now that we’ve got FILESTREAM going, we can go ahead and create our File Table. The first step is to set our database options to allow non-transactional access and a default virtual directory for the FILESTREAM.

--Set database specific options for FILESTREAM
ALTER DATABASE demoFileStream
SET FILESTREAM (non_transacted_access=FULL,directory_name=N'FSDemo')

Next, we need to add a FILESTREAM filegroup to the database, which defines the physical directory where our file tables will exist. Note, this directory will be a physical directory, but SQL Server must create it so it cannot already exist.

--Filegroup for the filestream
ALTER DATABASE demoFileStream
ADD FILEGROUP fgFileStream CONTAINS FILESTREAM

--Directory for the filestream filegroup
ALTER DATABASE demoFileStream
ADD FILE (NAME=flFileStream,FILENAME=N'C:\FileStream')
TO FILEGROUP fgFileStream

Finally, we can now create our file table with a simple CREATE TABLE statement. Note, we won’t declare any columns as those are pre-defined for the file table structure.

CREATE TABLE FT_Test
AS FILETABLE

And we’re done! We can now browse to the following UNC filepath: \\SHION\sql2012\FSDemo\FT_Test. This directory is the physical location of our file table. For now, I’ll put a couple test files in there.

At this point, we’ll just run a simple select and, bam! There’s a listing of our files.

SELECT
  name,
  file_type,
  creation_time,
  last_write_time
FROM FT_TEST


Voila! A happy little file table, ready for our use. I’ll be honest, there’s some limited use here and having file tables doesn’t solve a lot of problems. It’s very useful, though, for certain use cases and can be a very powerful tool in your tool kit. If you’re trying to think of situations where you need it, consider times where you need to work with files on the file system:

  • Managing backup files: If you’re like me, you write your backups to a unique filename for each backup. This means I usually need some other tool to clean up my old files. With File Tables, I can manage that clean up completely within SQL.
  • Restores: If you’ve got a lot of transaction logs to restore and you want an easy way to parse through files to find the ones you need, you can now do that using File Tables.
  • Basic ETL: If you need a simple ETL solution, File Tables can give you some additional leverage so you can keep your entire solution within SQL.

Thanks again to Nigel Sammy for hosting this month’s T-SQL Tuesday. There’s so much new with SQL 2012, it’s good for Nigel to give the community a chance to share some of the new features that are now out. Enjoy the rest of T-SQL Tuesday #29!