Using FileTable in SQL Server 2012

FileTable is a new concept in SQL Server 2012, using which you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications. File Table deals with directories as it stores all the hierarchies of the directory which is configured as root directory for a given FileTable.

A FileTable represents a hierarchy of directories and files. It stores data related to all the nodes in that hierarchy, including both directories and the files. This hierarchy starts from a root directory that you specify when you create the FileTable. When the database is configured for non-transactional access, the file and directory data stored in a FileTable is exposed through a Windows share for Windows API based applications. For a Windows application, this looks like a normal share with its files and directories.

Lets get started on how to get our first FileTable in SQL Server –

First lets check whether our database is configured for FileStream, execute the following TSQL to check –

/*********************************** Check FileStream Config **************************************/
USE master
GO
 
SELECT [value],[value_in_use] FROM [sys].[configurations] WHERE name LIKE 'filestream access level'
 

Which gave me following output –

filetable1

Value 0 tells us that database engine not configured to handle FileStreams. Lets make a small config change to make database engine handle filestream –

/*********************************** Change FileStream Access *************************************/
USE [master]
GO

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

 

Output –

filetable2

 

Before we move on to create a database, we need to enable filestream share in SQL Server Configuration Manager. To do that open SQL Server Configuration Manager and navigate to SQL Server Services –> right click SQL Server (SQL Server Service Type) –> FIELSTREAM. Enable settings as shown below, if you want remote clients to have access to stream data, then enable last checkbox too as shown in below figure. Finally click apply and OK to save settings. IMP – Restart of the same service might be required.

 

filetable3

 

Now lets create a database which supports FileStream, make sure all paths exist –

/*********************************** Create Database *************************************/
CREATE DATABASE FileStreamDB
ON PRIMARY
(
    NAME = FileStreamDB,
    FILENAME = 'C:\DB\FileStreamDB.mdf'
),
FILEGROUP FileStreamFG CONTAINS FILESTREAM
(
    NAME = FileStreamFileTable,
    FILENAME = 'C:\DB\FileTable_FileStreamDocumentStore' 
)
LOG ON
(
    NAME = FileStreamDB_Log,
    FILENAME = 'C:\DB\FileStreamDB_Log.ldf'
)
WITH FILESTREAM
(
    NON_TRANSACTED_ACCESS = FULL,
    DIRECTORY_NAME = N'DocumentStore'
);
GO

 

Now lets create FileTable –

/*********************************** Create FileTable *************************************/
USE [FileStreamDB]
GO
 
CREATE TABLE 
    Documents 
AS FILETABLE
WITH 
(
    FileTable_Directory = 'PrimaryDocumentStore',
    FileTable_Collate_Filename = database_default
);

 

Lets quickly check what is the default schema SQL Server is offering us in FileTable –

filetable4

To access this file table from a windows application, we can simply use the following share path –

\\1\2\3\4

1 –> You server or local computer name.

2 –> your FILESTREAM Windows Share name which we configured in SQL Server Configuration Manager.

3 –> Directory Name which we set at database creation time.

4 –> FileTable directory name which we give at FileTable creation time.

 

Drag and drop dome files into this share (some sample files) –

filetable5

Check the Table in database –

filetable6

All files are reflected in database filetable. Now they can be accessed by TSQL.

Lets create a directory in TSQL –

/*********************************** Create Directory *************************************/
USE [FileStreamDB]
GO

INSERT INTO Documents(name, is_directory, is_archive) VALUES ('CustomDirectory', 1, 0);
GO

 

Lets create a file –

/************************************* Create File ***************************************/
USE [FileStreamDB]
GO

INSERT INTO Documents(name, file_stream) 
VALUES ('SampleImage.png', (SELECT * FROM OPENROWSET(BULK N'C:\file.png', SINGLE_BLOB) as Data));
GO

 

Check the FileTable for newly added data –

filetable7

 

In the same way we can perform all CRUD operation on a FileTable type. There are lot other benefits with FileTable in SQL Server 2012. I am going to cover them in my next tutorials. Stay tuned.

You may also like...