Managing Files with FileTables

FileTables are a new feature in SQL Server 2012. They are layered on the existing FILESTREAM functionality.

FILESTREAM is designed to allow large objects, such as images and documents, to be stored logically in a column in a database table, but physically in a file in the operating system. This keeps large objects outside the database, keeping the size of the database down and also allows the files to be accessed directly by client applications reducing overhead.

FileTables in contrast allow SQL Server to manage a whole folder in the Windows file system including all the sub folders and files in that folder. The benefits of this is that from a client application perspective the files are exactly the same as those stored in the normal file system and can be directly accessed by standard programs such as Microsoft Word. From a SQL Server perspective the files and folders can be accessed and manipulated using T-SQL just as if there were stored in a table. Other advantages include the ability to directly index the contents of the folder using the full text indexing facility in SQL Server.

To enable a SQL Server Instance and database for FileTable use; requires a few simple steps:

·         First enable FILESTREAM on the SQL Instance

sp_configure 'filestream access level', 2

RECONFIGURE

·         Then create a database with FILESTREAM group. In this example the files will reside in folder C:\FileTableExample\FT               

CREATE DATABASE FileTableExample

ON PRIMARY ( NAME = FileTableExample,

       FILENAME = 'C:\FileTableExample\Data\FileTableExample.mdf'),

FILEGROUP FileTableExampleFS CONTAINS FILESTREAM( NAME = FileTableExampleFS,

       FILENAME = 'C:\FileTableExample\FT')

LOG ON  ( NAME = FileTableExampleLog,

FILENAME = 'C:\FileTableExample\Data\FileTableExample.ldf')

·         Then define the root folder to be used by FileTables. Note that setting NON_TRANSACTED_ACCESS to FULL exposes FileTables for use using the Windows file system, the other options are OFF and READ_ONLY. In this example the files will reside in folder C:\FileTableExample\FT\RootFolder           

ALTER DATABASE FileTableExample

       SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL,

DIRECTORY_NAME = N'RootFolder' );

Finally to create a FileTable just use:

CREATE TABLE FileTableExample1 AS FileTable

From a SQL Server perspective a FileTable is a special type of table. Unlike normal tables, FileTables have pre-set columns that represent the Meta data properties of the tables and folder they contain. These columns can be retrieved and updated using T-SQL allowing the file system data that the table represents to be managed. The columns of the FileTable created above are shown below in Object Explorer:

To manipulate files and folders using Windows Explorer first obtain the file share name using:

SELECT FileTableRootPath('dbo.FileTableExample1')

In this example the UNC path is \\SERVERNAME\mssqlserver\RootFolder\FileTableExample1 which can be accessed in Windows Explorer in the normal way (Just drag and drop files):

Files can be manipulated using T-SQL INSERT, UPDATE and DELETE (or any other T-SQL).

To rename a file:

UPDATE FileTableExample1

SET name='textfile1a.txt'

WHERE name='textfile1.txt'

To select the contents of a file. Note the file_stream column is varbinary(max) hence the CONVERT:

SELECT CONVERT(varchar(max), file_stream)

FROM FileTableExample1

WHERE name='textfile1a.txt'

To update the contents of a file:

UPDATE FileTableExample1

SET file_stream = CONVERT(varbinary(max),

CONVERT(varchar(max), file_stream) + 'new')

WHERE name='textfile1a.txt'

Folders are dealt with in a similar way.

Files and folders can be managed in T-SQL and using the normal File System in Windows. The best of both worlds.