Beyond Relational - Working with SQL Server FileTable

In my last post I setup FileTable in SQL Server Denali, and now I ma ready to start using it. Just to recap before I do that FileTable is a way of storing files in what appears to be a normal set of folders under a UNC share but in such a way that  these files are under the control of SQL Server.  The instance is the share name, and then the database has a folder associated with it underneath which there is a folder associated with each FileTable as you can see see from this short screencast..

This all works because under the covers you are actually using Filestream - when you create a database that you want to use for FileTable the database must have a special Filestream file group, and this has a directory associated with it which is actually where the files reside on disk.  However as with previous version of SQL Server which have the Filestream feature, the files themselves have random GUID filenames and live in folders with GUID names (as you can see in the screencast).

Another interesting thing about FileTable is the interlay between relational command on the FileTable and what happens to the files – in my screencast I deleted all of the files that weren’t 2007 or later PowerPoint decks ( file type <> pptx) and while the rows in the table were instantly deleted, it took a couple of minutes for them to disappear from the FileTable folder.  However this doesn’t affect referential integrity because if I issued a backup command after the delete only the files that correspond to rows that remained in the table would be backed up.  This is exactly the same as happens with Filestream it’s just that in Filestream you weren’t exposed to the files (with the GUID filenames in the directory specified for the FileStream File Group).

So hopefully this has helped get your head around what FileTable is all about but how useful will it be? Although you can control the files in FileTable from SQL Server you can’t really interact with the contents, you can’t look inside those files. Actually you can and in my next screencast I’ll hook up full text search which has also been getting better and better in each release of SQL Server.

In the meantime if you want to try this yourself you’ll need to pull down SQL Server Denali ctp 3