Working with filetables in SQL 2012

 Filetable is an exciting new feature that was introduced in SQL 2012. It can be regarded as an extension of FILESTREAM data storage that got introduced from SQL 2008 onwards. Filetables enable us to create table type entity on file system and enables us to manage file system storage and access through SQL server t-sql queries in addition to normal file system access.

Setting the background

You've configure the server and your database before you start creating filetable in it.

The below outlines step by step procedures for setting up of your server and database for enabling filetable working and access.

Step 1: Enable server for FILESTREAM access

 Before we start configuring DB for filetable creation, we need to make sure server is enabled for the FILESTREAM access. This can be ensured by checking FILESTREAM tab under server properties of your SQL Server instance. The server properties can be accessed by launching the configuration manager from Start->Programs-> Microsoft SQL Server 2012->Configuration tools-> SQL Server configuring manager and expanding SQL Server services and selecting properties for MS SQL SERVER service of your instance. Under FILESTREAM tab you can see below properties

Based on what all ways you want to enable access for your filetable you can choose the appropriate properties. Transact SQL access allows accessing filetable only through t-sql queries whereas the latter option allows direct access through filke system as well. you can even configure remote access to filetable directory through last option.

Once this is done go to server level properties and set FILESTREAM access level as follows

Go to  advanced tab of server properties window in SQLServer management studio and set property value accordingly

If you want this to be done programatically use below T-SQL statement

-- Enable Filestream

EXEC sp_configure filestream_access_level, 2
RECONFIGURE
GO
more details here

http://msdn.microsoft.com/en-us/library/cc645956.aspx

STEP 2: configure files and filegroups in database for FileTables

The next step involves creating files and filegroups for Filetable inside your database.

this can be done either through wizards available in SSMS as below

Or if you want it to be done via  T-SQL use below syntax

ALTERdatabase Vtest

ADD FILEGROUP VtestFS

CONTAINS FILESTREAM

GO

ALTER database Vtest

ADD FILE

(

NAME= 'VtestFSfile1',

FILENAME = 'C:\Users\visakhmurukesan\Downloads\MyFS1.ndf'

)TO FILEGROUP VtestFS

GO



Once you do this you step, you can find your file in the specified path as below

Next part is to create the filetable on filegroup

Creating the Filetable

Now that we've created files and filegroups lets proceed to create filetable on the filegroup.

We can do it either through SSMS create table option to generate template or directly use below query

CREATE TABLE MyFileTable AS FILETABLE

WITH

(

FILETABLE_DIRECTORY = 'FileTableRepository'

)

GO

Once you create this table you will be able to view it on object explorer in SSMS

As you see from above the created filetable will have predefined schema with set of columns,keys and constraints. More details on them can be found at

http://msdn.microsoft.com/en-us/library/gg492084.aspx

On exploring the table you can see the physical filessytem directory it links to

On copying some sample files onto folder and running select on table you can observe the below

Please keep in mind that for capturing last_access_time shown above accurately you need to tweak some default OS settings as explained below

http://visakhm.blogspot.com/2012/07/last-access-time-in-filetable-schema.html

As you see from above a SELECT query on table lists you details of all files in that directory with their attribute details. I've shown only some indicative columns but in actual you've other columns also present in table like file_stream,path_locator etc

From above we've seen how t-sql as well as filestream access is possible for FileTables

Now lets see how we can perform DML operations on FileTable

Data Manipulations in FileTable

Insert values into FileTable

A FileTable can be populated either through t-sql INSERT query or through filesystem by directly copying files onto directory.

For illsutration I've copied a new image img1 onto FileTable directory

Running a select now gives you this

We get same effect through a INSERT t-sql query

Similarly you can do the modification and deletion of files through filesystem or by using UPDATE,DELETE t-sql statements in filetable

By this I hope I've explained the step by step procedure for setting up filetable as well as doing basic DML operations in it. Next blog will be on advanced programmability options on filetable.

Hope this will be helpful for people who are starting on hands on for FileTable.

Do let me know your valuable comments on this article.