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
- Enabled Filestream on Server configuration and server property (can be enabled at the time of installation).
- Create a Database with Filestream enabled Filegroup.
- 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:
- Cannot be created on system or Tempdb databases
- Cannot be of temporary table.
- Cannot add/remove columns from it.
- Once created a FileTable that database cannot have “Directory_Name” as NULL.
- 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/