AboutMicrosoft 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
Step 7:
Let's copy some files to the MyDataFiles folder under the FILESTREAM share.
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
I try to access it using "TYPE" DO command, just to check if the file has some data etc.
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
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
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.