Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted June 1, 2012

FileTable in SQL Server 2012

By Muthusamy Anantha Kumar aka The MAK

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

\\Ustestsql03\sqleng\FileTable\MyDataFiles

Note: USTESTSQL03 is the hostname. SQLENG is the SQL Server instance name. That is also the filestream share.

Step 5:

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

 

Result

DBName

database_id

non_transacted_access

non_transacted_access_desc

directory_name

master

1

0

OFF

NULL

model

3

0

OFF

NULL

NULL

32767

0

OFF

NULL

tempdb

2

0

OFF

NULL

MRM

5

0

OFF

NULL

MyDB

6

2

FULL

FileTable

msdb

4

0

OFF

NULL

Step 6:

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

Use MyDB
go
 
select * from MyDataFiles

Query the Table
Query the Table

Step 7:

Let's copy some files to the MyDataFiles folder under the FILESTREAM share.

 Copy some files to the MyDataFiles folder
Copy some files to the MyDataFiles folder

Step 8:

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

stream_id

name

file_type

last_access_time

4F464AB6-18A5-E111-BDE0-00505694001D

DATA2.CSV

CSV

2012-05-23 16:48:56.4559849 -04:00

51464AB6-18A5-E111-BDE0-00505694001D

data.csv

csv

2012-05-23 16:48:56.4862564 -04:00

Note: Don't try to open the csv file using a text editor. Usually you will get the following message.

 The request is not supported
The request is not supported

I try to access it using "TYPE" DO command, just to check if the file has some data etc.

 Access using "TYPE" DO command
Access using "TYPE" DO command

Step 9:

Let us manipulate the file directly via the simple TSQL DML command, 'Update'. Here we are going to rename the file from Data.csv to Bloomberdata.txt.

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

Now let us 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

stream_id

Name

file_type

last_access_time

4F464AB6-18A5-E111-BDE0-00505694001D

BloombergData.txt

txt

2012-05-23 16:48:56.4559849 -04:00

51464AB6-18A5-E111-BDE0-00505694001D

data.csv

csv

2012-05-23 16:48:56.4862564 -04:00

It will update the file physically as well. You need to refresh Windows Explorer.

 Refresh Windows Explorer
Refresh Windows Explorer

If I delete a row from the table, it will delete the actual physical file as well. Execute the following TSQL command.

delete MyDataFiles where name='data.csv'

 Delete a row from the table deletes the actual physical file
Delete a row from the table deletes the actual physical file

Conclusion

As mentioned in the beginning of the article, if applications or users require file and directory storage in the database, SQL Server 2012 facilitates in the form of FileTable.

See all articles by MAK



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date