In September 2009, I introduced you to a Microsoft SQL 2008 feature called the FileStream http://emoreau.com/Entries/Articles/2009/09/Microsoft-SQL-Server-2008--The-FileStream-feature.aspx which lets you handle files in your database (store, search, backup, …) much more easily. If you haven’t read it yet, you should take 20 minutes to do it now as the FileTable is using many of the same feature. If you already read it, you are encouraged to take 10 minutes to review it as I won’t repeat everything here.

This month, I want to introduce you to the FileTable feature which is a great enhancement over the FileStream. In short, the FileTable feature let users store files within a special table in a SQL Server database straight from Windows (not having to do anything special). Most users won’t even know the file is in fact stored in a database.

What you need

If you want to test the queries shown here, of course you will need Microsoft SQL Server 2012. Don’t even try this on older versions, it just won’t work.

Configuration of the FileStream

The FileTable feature relies on the FileStream to work. So the first thing we need to do is to enable the FileStream feature exactly like it used to be done in SQL Server 2008.

First, notice that you need to be a member of the SysAdmin or the ServerAdmin roles to enable this feature.

Because this feature is off at installation time, chances are that it is still off. To enable, you have to enable it first. The first step is to open the SQL Server Configuration Manager (as shown in figure 1), click SQL Server Services from the left panel and right-click your instance of SQL Server from the right-pane (SQL Server (MSSQLServer) on my laptop). Finally, open the FileStream tab from the properties dialog and check the 3 checkboxes.

Figure 1: SQL Server Configuration Manager

Once the configuration manager is closed, start SSMS (SQL Server Management Studio) and execute these statements to complete the installation:

EXEC sp_Configure FileStream_Access_Level, 2
go
RECONFIGURE
Go

Your server should now be ready for the FileStream feature.

Configuration of the FileTable

Now that the FileStream is enabled, we need to execute some extra steps to configure the FileTable feature.

The first step is to create a folder to store the FileStream data (C:\_FTDemo in my case). You can do it by executing these statements:

USE master
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO
EXEC xp_cmdshell 'IF NOT EXIST C:\_FTDemo MKDIR C:\_FTDemo';
GO

We can now create a new database supporting FileStream and which will make use of this folder. This is exactly what these statements are doing:

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'FTDemo') 
BEGIN
	ALTER DATABASE FTDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE FTDemo;
END;

CREATE DATABASE FTDemo
WITH FILESTREAM
	( 
	NON_TRANSACTED_ACCESS = FULL,
	DIRECTORY_NAME = N'FTDemo'
	);
GO

/* Add a FileGroup that can be used for FILESTREAM */
ALTER DATABASE FTDemo
	ADD FILEGROUP FTDemo_FG
	CONTAINS FILESTREAM;
GO

/* Add the folder that needs to be used for the FILESTREAM filegroup. */
ALTER DATABASE FTDemo
	ADD FILE
		(
		NAME= 'FTDemo_File',
		FILENAME = 'C:\_FTDemo\FTDemo_File'
		)
	TO FILEGROUP FTDemo_FG;
GO

Now that we have a database, we can create a table. We don’t need to (and we cannot) specify the columns on this table as it is a special type of table (AS FILETABLE). One of the parameter to provide to this syntax is the name of another directory that will be automatically created. This is all shown in this script:

USE FTDemo;
GO
/* Create a FileTable */
CREATE TABLE FTDemoTable AS FILETABLE
	WITH
	( 
	FILETABLE_DIRECTORY = 'FTDemoFiles',
	FILETABLE_COLLATE_FILENAME = database_default
	);
GO

You should now have the database and the table created. If you refresh your Object Explorer in SSMS, you should be able to see something like what is shown in Figure 2.

Figure 2: The new file table shown in the Object Explorer

Some of you might be tempted to right-click on FileTables in the Object Explorer and select “New FileTable…” expecting to see a dialog like we normally see for a regular table. These people will be disappointed when they will realized that instead of a dialog, a script (like the ones we used above) is created in place. Maybe the next version will provide a better integration.

The Folders

You might now open the Windows Explorer and navigate to 2 folders that where created in the previous steps.

The first one is shown in figure 3. It is the first folder created by the scripts. You will probably get a security warning when you will try to access it. It contains a special and very important file named filestream.hdr. This files contains some metadata about the FileStream, be sure not to delete this file.

Figure 3: One of the folders

Another folder has been created. This second folder is the one that your users will use to magically store files into the database. While we created the database and the table, a share was automatically set up for us. You can access this share using this syntax:

\\ServerName\InstanceName\DatabaseName\FileTable_Directory

On my machine, with the scripts ran here above, the path is:

\\moer-x220t\MSSQLSERVER\FTDemo\FTDemoFiles 

After you copied a file in this location, you will find that you cannot always open the file with their editor. Editors using Memory-Mapped files like Notepad and Paint cannot edit the files in place. Other editors like Word can. If you need to modify one of these memory-mapped files, you can copy it from that share to another location and copy back the newly updated file to the share folder. SQL Server will detect the change and update the database automatically.

This share also support sub-folders without any problems.

Using T-SQL against the FileTable

Now that you have everything setup. Copy a couple of files to your share. Files dumped (created, updated, or deleted) in this share are automatically intercepted by the SQL Server engine and handled in the database.

After copying a couple of files, you can see the impact at 2 places. First reopen the first folder we explored. If you dig deep enough in the branch with GUID names, you will find 2 files created for each file you dumped into the share. This is a copy managed by SQL Server. The second place you can see a trace of your copied files is in the table itself. You can view it using a simple query like this one:

SELECT * FROM dbo.FTDemoTable

If you have text documents, there is no need to open the files, you can directly get the value of the files right from a query just by casting the file_stream field to a readable type like this:

SELECT *
, CAST(file_stream AS VARCHAR(max)) 
FROM dbo.FTDemoTable

And sometimes, you will probably want to get the folder where the physical file is persisted and even the level of deepness of your file (number of sub-folders). This would be the kind of queries you would use:

SELECT *
, CAST(file_stream AS VARCHAR(max)) 
, FileTableRootPath() AS RootFolder
, file_stream.GetFileNamespacePath() AS Path
, path_locator.GetLevel() AS Level
FROM dbo.FTDemoTable

But who said the FileTable is read-only. You can Insert, Update, and Delete records from a FileTable.

Here is an example of modifying the name of a file (which also change the name in the shared folder):

UPDATE dbo.FTDemoTable
SET name = 'newname2.txt'
WHERE stream_id = 'C3CB4761-D330-E211-BEAE-0050B649CCA8'

And of course you can handle all kind of data. My article of September 2009 regarding FileStream showed you a .Net application to read/write. You can reuse most of it by changing field names.

Inserting and deleting works like any other T-SQL queries. The files in the share folder are updated almost immediately.

What’s the advantage?

Maybe you haven’t found the advantage of this FileStream/FileTable feature. I’ll give you a couple I find useful and you will probably think of some other:

• Easy access to the files without having to go through the database
• Files related to an application now backed up with the database
• Restoring the files on another server brings back all the files
• Full text search is enabled on these files

Conclusion

The FileStream feature was already interesting but it wasn’t very useful for end users without the use of an application.

Now, using the FileTable feature, every user who know how to save a file to a shared folder can use it.