FileTable in SQL Server 2012

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.

Pre-requisite

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

Step1:

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.

Step2:

Create folder E:\MyFiles. 

Step 3:

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
 

Step 4:

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

Query the Table

Latest Articles