Filetable in SQL Server 2012

File Table is a new feature introducted in SQL Server 2012 Denali to manage files on a table. This is mainly used to maintain unstructured data. Filetable combine’s filestream with windows file system API to deliver enhanced BLOB capabilities in sql server. File table is a combination of table and file system. File table built on filestream.

Like all other tables in SQL Server file table is also an ordinary table with one difference ie. It has fixed schema, we can’t alter the filetable schema.

There are few limitations with the FileStream a) the files which are stored in the filestream column cann’t be read by the user directly. b) No extensions will show for those files if we view from the windows explorer, c) there is no method to create subdirectories in filestream folder d) by seeing the file it is not possible to know the path of the file, e) it doesn’t uses the win 32 API there are specific APIs for IO.

As a first step we need to enable filestream access

1) Open SQL Server Configuration Manager
2) Select SQL Server Service – properties
3) Click on FileStreamTab
4) Check Enable FileStream for Transact-SQL Access
5) Enable Filestream for file I/O Access
6) Windows Share Name – Type whichever name you want
7) Check Allow remote client access to FileStream Data

CREATE DATABASE FileTeableDemo

ON

PRIMARY ( NAME = FileTeableDemo_Data,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileTeableDemo.mdf'),

FILEGROUP FSFileTeableDemoGroup CONTAINS FILESTREAM(NAME = FSFileTeableDemo, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FSFileTeableDemo')

LOG ON ( NAME = FileTeableDemo_Log,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileTeableDemo_Log.ldf')

WITH FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = 'KalyanDocuments')

GO

SELECT * FROM sys.database_filestream_options WHERE database_id=DB_ID('FileTeableDemo')

USE FileTeableDemo

GO

CREATE TABLE MyDocuments AS FileTable

CREATE TABLE MyImages AS FileTable

If you run the above two create table statements then it will create two folders inside the shared folder which we specified at step 6 in the above. While creating any filetable we need not to specify any columns in the definition, it has its own standard.

We can directly explore the folder like other folders.

Select * from MyDocuments

Select * from MyImages

To explore file table
SSMS -- Databases -- Database -- Tables -- FileTables -- Righ click on object name and select explore file table directory.

We can directly drag and drop the files into those folders using normal windows explorer, then automatically SQL Server will insert records into the tables. It stores the file with all its attributes and file name with extension in the sql server. If you rename file in sql server automatically it get affected in the folder and vice versa.

Syntax to rename any file name

UPDATE MyImages set name='c3.jpg' where stream_id='E6FA8E76-3ACB-E111-A317-000FFEE7BCF9' 

Like other backups we can do a normal backup and restore this into other server. After restore you need to have access permission for that share path to explore the filetable contents.

Note - The base folder can't be deleted becoz it is referred by SQL Server, but the contents (files) inside the folders can be alerted / modified / deleted with appropriate permissions.