Microsoft always includes a bunch of new features when they release a new version of SQL Server and in SQL Server 2012 one of those new features is FileTable. The name says it all.
For applications or users that require file and directory storage in the database, SQL Server 2012 facilitates in the form of filetable, a special table that represents the hierarchy of directories. It has a pre-defined schema/structure that stores the FILESTREAM data.
In order to use FileTable some pre-requisites should be met. They are:
- FILESTREAM should be enabled at the Instance level
- FILESTREAM FILEGROUP should be provided at the database level
- Enable Nontransaction access at the database level
- Specify directory for FileTable at the database level
Let us enable FILESTREAM on the instance level. Execute the following command and see if the FILESTREAM feature is already enabled.
select value,value_in_use from sys.configurations where name like 'filestream access level'
If the value is 0, then FILESTREAM support is disabled for that instance. If the value is 1 then FILESTREAM is available for TSQL access. If the value is 2, then FILESTREAM access is enabled both for Transact SQL access and Win32 streaming access.
Create folder E:\MyFiles.
It is easier to enable the next three pre-requisites in a script instead of creating a database and altering the properties. So, let us create the database with FILESTREAM File group and enable it in Non Transaction level. Also we could provide the folder location for the FILESTREAM folder.
USE [master] GO /****** Object: Database [MyDB] Script Date: 5/23/2012 4:25:56 PM ******/ CREATE DATABASE [MyDB] CONTAINMENT = NONE ON PRIMARY ( NAME = N'MyDB_Data', FILENAME = N'E:\Data\MyDB_Data.mdf' , SIZE = 2112KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [MyFilestreamFG] CONTAINS FILESTREAM DEFAULT ( NAME = N'MyFileStreamData', FILENAME = N'e:\myfiles\Data' , MAXSIZE = UNLIMITED) LOG ON ( NAME = N'MyDB_Log', FILENAME = N'E:\Data\MyDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO ALTER DATABASE [MyDB] SET COMPATIBILITY_LEVEL = 110 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [MyDB].[dbo].[sp_fulltext_database] @action = 'enable' end GO
Now, let us create the table as Filetable, as shown below, by using the following Transact SQL statement.
Use MyDB go CREATE TABLE MyDataFiles AS FileTable WITH ( FileTable_Directory = 'MyDataFiles', FileTable_Collate_Filename = database_default ); GO
This creates the folder MyDataFIles under the default filestream location