Filetable is a new feature in SQL Server 2012 which allows application to access files that’s stored in disk via SQL Server, that’s the main function. In other words you can store files and directories to windows NTFS folders and these files can be accessed non transactional from application like regular file system. FileTables act as a normal table however it requires FILESTREAM to be enabled. You can query & update this table to get information about the directories and files stored in the location and also it’s hierarchy.
It’s called as special table as you will be able to store the Filestream data and allows the application to access the files through Windows API. Each row in this table represent files or folder in the directory specified. You can also perform full text search and semantic search on unstructured data.
Couple of pre-requisites are there before creating Filetables. As I said earlier FILESTREAM need to be enabled at instance level and a new filegroup need to be created under database level. In this article we are going to see how to satisfy the pre-requisite for Filetables
Enabling FILESTREAM at Instance Level
This option can also be set during SQL Server installation so if you have enabled FILESTREAM during installation please ignore this step.
- Go to SQL Server Configuration manager
- Right click on SQL Server instance and the click on properties
- Click on Filestream tab
- Enable Filestream as shown in the below screenshot
*) If you want to grant read\write access from window then enable “Enable FILESTREAM for file I/O access”, you need to provide a windows share which is not already in use.
*) If you want to grant access for remote machines to this share then click on “Allow remote client access to FILESTREAM data”
- Now let’s enable filestream in server property, execute the tsql below
EXEC sp_configure 'filestream access level',2 GO RECONFIGURE
Where 0 – Disables FILESTREAM support for this instance.
1 – Enables FILESTREAM for Transact-SQL access.
2 – Enables FILESTREAM for Transact-SQL and Win32 streaming access.
Enabling FILESTREAM at Database Level
To create Filetables we require the db to be enabled for FILESTREAM. To create a FILESTREAM enabled db it need to have a filegroup for filestream. You can use below command below to create filestream enabled db
CREATE DATABASE FileTables ON PRIMARY ( NAME = FT_Data, FILENAME = 'C:\Temp\SQLDataFiles\FT_Data.mdf'), FILEGROUP FileStream_FT CONTAINS FILESTREAM( NAME = FT_Data_FS, FILENAME = 'C:\Temp\SQLDataFiles\FT_Data_FS') LOG ON ( NAME = FT_Log, FILENAME = 'C:\Temp\SQLDataFiles\FT_Log.ldf') WITH FILESTREAM (DIRECTORY_NAME = N'FTFSData',NON_TRANSACTED_ACCESS = FULL) GO
If you want to create it in GUI then go to filestream tab on new db creation window, there you can fill the details for filestream. Once you are done with Filestream enabled database then you are good to go create filetables, you need to satisfy these two pre-requisites to work on Filetable.