New FileTables Feature in SQL Server 2012

FileTables is a new feature introduced in SQL Server 2012. A FileTable is a specialized user table with a pre-defined schema that stores FILESTREAM data, as well as file and directory hierarchy information and file attributes. The file attributes can then be modified either through SQL queries or via Windows Explorer. SQL Server provides a special table of files, also referred to as a FileTable, for applications that require file and directory storage in the database, with Windows API compatibility and non-transactional access. So let's have a look at a practical example of how to use the new feature FileTables in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

The FileTable feature in SQL Server 2012 builds on Filestream and you must enable this feature for FileTable to work.

In the previous article I explained how to enable FILESTREAM using SQL Server Configuration Manager; see:

http://www.c-sharpcorner.com/UploadFile/rohatash/enabling-filestream-in-sql-server-2012/

Creating a Database

Now create a database containing a file stream. First I created a DemoFileTables folder in Windows. The following SQL Script creates the database with the file stream.

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

CREATE DATABASE DemoFileTableDB

ON PRIMARY

(Name = DemoFileTableDB,

FILENAME = 'D:\DemoFileTables\DemoFTDB.mdf'),

FILEGROUP FTFG CONTAINS FILESTREAM

(NAME = FileTableFS,

FILENAME='D:\FileTable\FSt')

LOG ON

(Name = FileTableDBLog,

FILENAME = 'D:\DemoFileTables\DemoFTDBLog.ldf')

WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL,

DIRECTORY_NAME = N'DemoFileTableDB');

Now press F5 to execute it and we can proceed with creating a File Table now.

Creating a FileTable

Now to create a table as a File Table. Press F8 to open Object Explorer and select "New FileTable", as in:

Syntax

USE <database, sysname, AdventureWorks>
GO

CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_filetable> AS FILETABLE

WITH

(

FILETABLE_DIRECTORY = '<file_table_directory_name, sysname, sample_filetable>',

FILETABLE_COLLATE_FILENAME = <file_table_filename_collation, sysname, database_default>

)

File Table

USE DemoFileTableDB

CREATE TABLE DemoFileTableTb AS FileTable

WITH

(FileTable_Directory = 'FileTableTb_Dir');

Now press F5 to execute it.

Now refresh the file table and you will see DemoFileTableTb file table detail within Object Explorer.

Now If you right-click the File Table DemoFileTableTb and choose "Explore FileTable Directory" then you will get a Windows-based directory view and here you can drag and drop your files.

Now click on "Explore FileTable Directory", as in:

Creating Folder for DemoFileTables directory

Now create two folders for this directory, as in:

INSERT INTO DemoFileTableTb(Name, is_directory)

values('Rohatash',1)

INSERT INTO DemoFileTableTb(Name, is_directory)

values('Rahul',1)

Now press F5 to execute it.

Now refresh the file table and you will see file table DemoFileTableTb. If you right-click the File Table documents and choose "Explore FileTable Directory" then you will get a Windows-based directory view with folders.

Now you can drag and drop a Rich Text Format (RTF) document file to this directory and under folder Rohatash.

Now use a select Statement to see that the file is now available in the database.