Filetable – SQL Server 2012

With the new SQL server 2012 , Microsoft has packed a whole new bunch of features ! One among them is the ‘FileTable’ . The name it self suggest it all ! As per MSDN :

SQL Server provides a special table of files, also referred to as a FileTable, for applications that require file and directory storage in the database, with Windows API compatibility and non-transactional access. A FileTable is a specialized user table with a pre-defined schema that stores FILESTREAM data, as well as file and directory hierarchy information and file attributes.

But there are some PER-REQUISITES for using this FileTable :

  • 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

Below mentioned steps would enable you to start using FILETABLE:

Now,  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.

Now we need to enable the next three per-requisites, its easy to do it 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.

/****** 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, we shall create the table as Filetable,  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 will cretae a folder MyDataFIles under the default filestream location :

\\hostname\sqlinstance\FileTable\MyDataFiles

You can query the filestream options defined for every database using the following TSQL query.

select db_name(Database_id) as DBName,* from sys.database_filestream_options

These are the things you could experiment with initially :

Now let us try to query the table that we created as File Table, using the following query.

Use MyDB
go
select * from MyDataFiles

Let’s copy some files to the MyDataFiles folder under the FILESTREAM share. (* the path i had specified above)

Now let’s try to query the table that we created as File Table using the following query.

select stream_id, name,file_type, last_access_time  from MyDataFiles

This query will list all the files that you have copied to the folder. If you want to update these physical files, you could do even that. For instance renaming of a file could be done like this :

UPDATE
MyDataFiles SET
name = 'RenameTextFile.txt' WHERE
stream_id = '4F464AB6-18A5-E111-BDE0-00505694001D'

If you  delete a row from the table, it will delete the actual physical file as well. Execute the following TSQL command and check it out:

delete MyDataFiles where name='data.csv'

Thus an entire new concept / feature has been introduced in SQL Server 2012 to deal with files and directories.

You could explore more from MSDN articles here. Happy exploring !

Advertisements

About mytechlifedays

Its been a wonderful learning period over the last 6 years in the IT industry , getting exposed to whole lot of technologies and ideas. The hurdles and the crisis that came along have been wonderful experience ... And now its time to pen them down so that let some others execute faster and easily with these information .....
This entry was posted in Database and tagged , . Bookmark the permalink.

Leave a Reply ! It would be always appreciated ! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s