Denali – Day 12: FileTable

“FileStream” is introduced in sql server 2008, for storing BLOB data like varbinary(max), it is integrated with NTFS file system by creating a separate FileGroup to stored BLOB data, and make it more efficient and stable in sql server”

FileTable is a new feature to “FileStream” and dependent on Filestream so Filestream should be enabled.

From MSDN -”you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your windows applications”

FileTable is another table on Filestream enabled database, which can contains information about the directory information and files associated with that directory. It will create a network location with “directory_name” directory in it where you can store the files, this could replace your fileserver.

FileTable is a table which has fixed columns in it you cannot add/remove any column from this table.

stream_id

file_stream

name

path_locator

creation_time

last_write_time

last_access_time

is_directory

is_offline

is_hidden

is_readonly

is_archive

is_system

is_temporary

How to Work on FileTable

1. Enabled Filestream on Server configuration and server property (can be enabled at the time of installation).
   
2. Create a Database with Filestream enabled Filegroup.
   
3. Create a FileTable on that database which contains information about directory specified by FileStream or custom directory.
   

CREATE DATABASE FileTableDB1

(NAME = FileTableDB1_data, FILENAME = ‘E:\FS\FileTableDB1_data.mdf’),

FILEGROUP FSGroup CONTAINS FILESTREAM

(NAME = FS1, FILENAME = ‘E:\FS\’) ,

With FILESTREAM( (DIRECTORY_NAME=’MainDir’,NON_TRANSACTED_ACCESS=FULL)

LOG ON (NAME = FileTableDB1_log, FILENAME = ‘E:\FS\FileTableDB1_log.ldf’)

Create Table FileTable1 as FileTable.

You can specify the Directory_Name as well

You have to only provide directory_name, no path, and it will create a virtual directory, you cannot access this directory directly you have to open it with

Management Studio->Object Explorer->Databases->FSEnabledDB->tables->FileTables->”ExploreFileTableDirectory“

This is the easiest way to access.

FileTable has some limitations which FileStream has:

1. Cannot be created on system or Tempdb databases
   
2. Cannot be of temporary table.
   
3. Cannot add/remove columns from it.
   
4. Once created a FileTable that database cannot have “Directory_Name” as NULL.
   
5. You cannot create FileTable using Management studio, it will again goes back to Query window with template.
   

Dropping a fileTable will drop the folder and files from the directory_name directory

It’s a big topic and I am new to it, so would blog more as I learn.

Reference:

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

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

http://blog.sqlauthority.com/tag/filestream/