SQL Server: FileTables

Today, after attending a Session from Aasim Abdullah I have learned about FileTable, a new SQL Server 2012 feature based on FileStream. With FileTable, we can manage documents in a more professional way.

Filetable is to Store, Manage and Represents a Hierarchy of Files and Directories with their General Meta data (10 file attributes) in special tables of SQL server. Filetable NON_TRANSACTED_ACCESS, Windows API Compatibility allow files access from windows explorer Filetable is Fixed structured table and allow only two options with table creation                 1. Directory                  2. Collation SQL Server automatically creates fixed number of triggers, indexes and constraints on table with the creation of 'Filetable'

No. Column Name Data type 1 path_locator rowguidcol 2 stream_id varbinary(max) 3 file_stream filestream 4 file_type nvarchar(255) 5 Name nvarchar(255) 6 parent_path_locator hierarchyid 7 cached_file_size bigint 8 creation_time datetime2(4) 9 last_write_time datetime2(4) 10 last_access_time datetime2(4) 11 is_directory bit 12 is_offline bit 13 is_hidden bit 14 is_readonly bit 15 is_archive bit 16 is_system bit 17 is_temporary bit

Following are basic important figures of Filetable

Prerequisite for Filetable are

•       Instance Level

•       FILESTREAM enabled

•       Database Level

•       FILESTREAM Filegroup defined

•       Non-transnational ( None, Read Only, Read\Write) option enabled

•       Valid directory (NTF Data Container) specified

Methods to load files in Filetable are

                  1.  Copy-Paste in Windows Explorer

                  2.  T-SQL  (Bulk Copy Operation)

Operations that we can perform on Filetable are

•       Insert

•       Update

•       Delete

•       User defined indexes, triggers and constraints

Security:

•       Filetable supports only SQL Server T-SQL Level Security

File Table DMVs are

•       sys.database_filestream_options

•       sys.filetables

•       sys.filetable_system_defined_objects  (System objects related to file table)

•       sys.dm_tran_locks

Kill File Handles for urgent tasks are also allowed

EXEC sp_kill_filestream_non_transacted_handles @table_name = 'filetable_name';

Limitations

•       Memory-mapped files (like Notepad and Paint) are not supported with NON_TRANSACTED_ACCESS, only way to get access to those files is remote access.

•       Partition is not allowed on Filetable

•       ‘Select Into’ statement can’t be used for Filetable creation  

•       Up-to 15 levels Sub-Directory allowed

•       Filetable ‘s File name limit doesn't match with Windows Explorer limit

Reference Links

I have found following links more use full

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

http://visualstudiomagazine.com/articles/2012/10/25/using-filetables-in-sql-2012.aspx

http://technet.microsoft.com/en-us/library/gg509097.aspx

and a video of Technet is more easy to understand File Tables.
http://channel9.msdn.com/Events/TechDays/Techdays-2012-the-Netherlands/2270