SQL Server 2012 allows you to store file/directories in a special table called FileTable that builds on top of SQL Server FILESTREAM technology. As per Microsoft BOL, “FileTable lets an application integrate its storage and data management components, and provides integrated SQL Server services – including full-text search and semantic search – over unstructured data and metadata.”

FileTable has a fixed schema and each row of this table represents a file or a directory. The main advantage of FileTable is that it supports Win32APIs for file or directory management which mean we can access file and directory hierarchy through a Windows Share and database storage is transparent to Win32 application. Files can be bulk loaded, updated as well as managed in T-SQL like any other column. SQL Server also supports backup and restore job for this feature.

In this tip we will take a look at how to use FileTable feature of SQL Server 2012.

To use FileTable feature, execute the following query to verify that FILESTREAM is enabled at the instance level:

Note: 0 in value_in_use column indicates that FILESTREAM support s disabled for that instance, 1 indicates that FILESTREAM is available for Transact-SQL access and 2 indicates that the FILESTREAM access is enabled both for Transact SQL access and Win32 streaming access.

You can enable FILESTREAM feature at the instance level if it is disabled on your instance by following the instructions listed in the step 1 below:

Step-1: Enable FILESTREAM at the instance level
Open SQL Server Configuration Manager, Right click SQL Server service and choose Properties, Click on FILESTREAM tab and then tick Enable FILESTREAM for Transact-SQL access, Enable FILESTREAM for file I/O access, specify Windows Share name, and tick Allow remote clients access to FILESTREAM data as shown below:

Now open SQL Server Management Studio, click New Query to display the Query Editor. In Query Editor, enter the following Transact-SQL code:

USE [master]
GO
-- Enabling Filestream
EXEC sp_configure filestream_access_level, 2
RECONFIGURE

Click Execute and then restart the SQL Server service.

Microsoft Reference: Enable the FILESTREAM at instance level

MSDN – http://msdn.microsoft.com/en-us/library/cc645923.aspx

Step 2: Create Database with FILESTREAM enabled

Now executed the following Transact-SQL code to create a database with FILESTREAM File group, specify folder location for FILESTREAM folder and enable non-transaction access at the database level as follow:

USE [master]
GO
CREATE DATABASE SQLDocumentStoreDB
ON PRIMARY
(NAME = SQLDocumentStoreDB
,FILENAME = 'D:\Program Files\Microsoft SQL Server\DBData\SQLDocumentStoreDB.mdf'),
FILEGROUP FileStreamFG CONTAINS FILESTREAM
(NAME = SQLDocumentStoreFileTable
,FILENAME = 'D:\FileTable_DocumentStore' ) --Folder location
LOG ON
(NAME = SQLDocumentStoreDB_Log
,FILENAME = 'D:\Program Files\Microsoft SQL Server\DBLogs\SQLDocumentStoreDB_Log.ldf')
WITH FILESTREAM
--Gives full non-transactional access to the share/ directory
(NON_TRANSACTED_ACCESS = FULL
,DIRECTORY_NAME = N'DocumentStore');
GO

 
Step 3: Create FileTable

Now create a FileTable by executing the following Transact-SQL statement against your FILESTREAM enabled database in SQL Server Management Studio:

USE [SQLDocumentStoreDB]
GO
CREATE TABLE MyDocuments AS FILETABLE
WITH (FileTable_Directory = 'MyDocumentStore'
,FileTable_Collate_Filename = database_default);

This can also be accesed at:
\\<ServerName>\<FILESTREAM Share Name>\<DirectoryName>\<FileTable Directory>\

For this example share path is:
\\Machine-LT01\DEV01\DocumentStore\MyDocumentStore

Step 4: Examine your newly created FileTable

Examine the FileTable and its structure in Object Explorer:

Now query your FileTable as follow:

USE [SQLDocumentStoreDB]
GO
SELECT [stream_id]
,[file_stream]
,[name]
,[path_locator]
,[parent_path_locator]
,[file_type]
,[cached_file_size]
,[creation_time]
,[last_write_time]
,[last_access_time]
,[is_directory]
,[is_offline]
,[is_hidden]
,[is_readonly]
,[is_archive]
,[is_system]
,[is_temporary]
FROM [dbo].[MyDocuments]
GO

It will return no data because the FileTable is empty (see below):

Step 5: Copy files to your FileTable

This can be done by copying files to your FileTable directory.

For this example FileTable directory name is MyDocumentStore and the full share path is
\\Machine-LT01\DEV01\DocumentStore\MyDocumentStore:

Step 6: Examine the data again in your FileTable

Its time to query your FileTable again in SQL Server Management Studio as you have just copied files to your FileTable directory:

USE [SQLDocumentStoreDB]
GO
SELECT [stream_id]
,[file_stream]
,[name]
,[path_locator]
,[parent_path_locator]
,[file_type]
,[cached_file_size]
,[creation_time]
,[last_write_time]
,[last_access_time]
,[is_directory]
,[is_offline]
,[is_hidden]
,[is_readonly]
,[is_archive]
,[is_system]
,[is_temporary]
FROM [dbo].[MyDocuments]
GO

As you can see this query is now returning the rows related to the files which I’ve just copied to FileTable directory:

Step 7: Explore FileTable directory via SSMS

You can also explore the FileTable directory via SQL Server Management Studio as follow:

Expand Tables > FileTables > Right-click your FileTable > Choose option Explore FileTable directory option as follow:

Step 8: View FILESTREAM Database Options

You can execute the following query to view the FILESTREAM database option as follow:

USE [master]
GO
SELECT DB_NAME(Database_id) AS [Database]
,[database_id] AS [DatabaseID]
,[non_transacted_access] AS [NonTransactedAccess]
,[non_transacted_access_desc] AS [NonTransactedAccessDesc]
,[directory_name] AS [DirectoryName]
FROM [sys].[database_filestream_options]
WHERE DB_NAME(Database_id) = <Databse_Name>

For example:

You can also Enable/Disable the FileTable namespace as follow:

USE [<Database_Name>]
GO
ALTER TABLE <FileTable Name> DISABLE FILETABLE_NAMESPACE

For example:

USE [SQLDocumentStoreDB]
GO
ALTER TABLE MyDocuments DISABLE FILETABLE_NAMESPACE

This will disable all system-defined constraints and Win32 access to FileTable. This is useful when doing bulk-loading or re-organization of your data.

Key information for FileTables

1) DML\DDL triggers are supported on FileTable but DML trigger on a FileTable cannot update any FileTables.

2) Normal Insert/Update/Delete are allowed for the FileTable manipulation.

3) Built-infunctions:

+ GetFileNamespacePath()–UNC path for a file/directory.

+ FileTableRootPath()–UNC path to the FileTable root

+ GetPathlocator()–path_locator value for a file/directory.

4) FileTable can be dropped similar to any other table.

5) Database Backup/Restore operations include FileTable data.

+ Point in time Restore may contain more recent FILESTREAM data due to non-transactional updates during backups.

6) FileTables are secured similar to any other user tables. Also same security is enforced for Win32 access.

7) Windows tools like xcopy/robocopy OR drag-drop operations through WindowsExplorer can be used BCP operations are supported for direct T-SQL data inserts.

Conclusion

FileTable is a cool feature of SQL Server 2012 that allows applications or users to store files/directories inside database. This feature is built on of SQL Server FILESTREAM technology and is very simple to use.

References

• http://www.slideshare.net/MichaelRys/filetable-and-semantic-search-in-sql-server-2012
• http://msdn.microsoft.com/en-us/library/ff929144.aspx