SQL Server 2012 introduced a new feature named as FileTable that allows you to store file, directory, and documents in a special table that builds on the top of SQL Server FILESTREAM technology. The benefit of this feature is that it provides Windows API compatibility for file data stored within a SQL Server database & also compatible with SQL Server features including management tools, services and file attribute data. SQL Server also supports backup and restore job for this feature.
Below three steps must be completed, in order to creating and using FileTable:
1. Enable FileStream at the instance Level
2. Create a FILESTREAM-Enabled Database
3. Create a FileTable
1. Enable FileStream at the instance level Via SQL Server Configuration Manager:
- Open SQL Server configuration Manager.
- Locate SQL Server Instance on which, you need to enable file stream on it.
- Right click on instance and select Properties
- Select Filestream and Click on the checkboxes 'Enable FILESTREM for transact SQL access' and 'Enable FILESTREM for file I/O access' and enter windows share name as well.
- Click apply
2. Create a FILESTREAM-Enabled Database Via SQL Server Management Studio:
- Create a new database in SQL Server Management Studio by right click on the database.
- Now enter all the information required to create a simple database.
- Click on options and set two properties mark as red circle in the image:
- Now click Ok. Finally database is created.
3.Create a FileTable Via SQL Server Management Studio
In Object Explorer, Right click on the Tables folder, and then select New FileTable.
Now a new script window will open. It contains a T-SQL template that you can customize and run to create a FileTable.
I will recommend this feature to all users who want to store files/directories inside database.