ballardchalmers - home

Contact us 01342 410223

Blog

Managing Files with FileTables

FileTables are a new feature in SQL Server 2012. They are layered on the existing FILESTREAM functionality.

FILESTREAM is designed to allow large objects, such as images and documents, to be stored logically in a column in a database table, but physically in a file in the operating system. This keeps large objects outside the database, keeping the size of the database down and also allows the files to be accessed directly by client applications reducing overhead.

FileTables in contrast allow SQL Server to manage a whole folder in the Windows file system including all the sub folders and files in that folder. The benefits of this is that from a client application perspective the files are exactly the same as those stored in the normal file system and can be directly accessed by standard programs such as Microsoft Word. From a SQL Server perspective the files and folders can be accessed and manipulated using T-SQL just as if there were stored in a table. Other advantages include the ability to directly index the contents of the folder using the full text indexing facility in SQL Server.

To enable a SQL Server Instance and database for FileTable use; requires a few simple steps:

  • First enable FILESTREAM on the SQL Instance

sp_configure 'filestream access level', 2

RECONFIGURE

  • Then create a database with FILESTREAM group. In this example the files will reside in folder C:\FileTableExample\FT               

CREATE DATABASE FileTableExample

ON PRIMARY ( NAME = FileTableExample,

       FILENAME = 'C:\FileTableExample\Data\FileTableExample.mdf'),

FILEGROUP FileTableExampleFS CONTAINS FILESTREAM( NAME =FileTableExampleFS,

       FILENAME = 'C:\FileTableExample\FT')

LOG ON  ( NAME = FileTableExampleLog,

FILENAME = 'C:\FileTableExample\Data\FileTableExample.ldf')

 

  • Then define the root folder to be used by FileTables. Note that setting NON_TRANSACTED_ACCESS to FULL exposes FileTables for use using the Windows file system, the other options are OFF and READ_ONLY. In this example the files will reside in folder C:\FileTableExample\FT\RootFolder           

ALTER DATABASE FileTableExample

       SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL,

DIRECTORY_NAME = N'RootFolder' );

 

Finally to create a FileTable just use:

CREATE TABLE FileTableExample1 AS FileTable

 

From a SQL Server perspective a FileTable is a special type of table. Unlike normal tables, FileTables have pre-set columns that represent the Meta data properties of the tables and folder they contain. These columns can be retrieved and updated using T-SQL allowing the file system data that the table represents to be managed. The columns of the FileTable created above are shown below in Object Explorer: 

Managing Files With Filetables 1

 

To manipulate files and folders using Windows Explorer first obtain the file share name using:

SELECT FileTableRootPath('dbo.FileTableExample1')

 

In this example the UNC path is \\SERVERNAME\mssqlserver\RootFolder\FileTableExample1which can be accessed in Windows Explorer in the normal way (Just drag and drop files):

Managing Files With Filetables 2

Files can be manipulated using T-SQL INSERT, UPDATE and DELETE (or any other T-SQL).

To rename a file:

UPDATE FileTableExample1

SET name='textfile1a.txt'

WHERE name='textfile1.txt'

 

To select the contents of a file. Note the file_stream column is varbinary(max) hence the CONVERT:

SELECT CONVERT(varchar(max), file_stream)

FROM FileTableExample1

WHERE name='textfile1a.txt'

 

To update the contents of a file:

UPDATE FileTableExample1

SET file_stream = CONVERT(varbinary(max),

CONVERT(varchar(max), file_stream) + 'new')

WHERE name='textfile1a.txt'

 

Folders are dealt with in a similar way.

Files and folders can be managed in T-SQL and using the normal File System in Windows. The best of both worlds.

By Geoff Ballard, Chief Technical Officer

Have a comment? Let us know below, or send an email to enquiries@ballardchalmers.com

Comment on this article

About

Ballard Chalmers is one of the UK’s leading enterprise software engineering companies, specialising in development for the Microsoft Enterprise Application Platform. We are principally dedicated to software development and integration for the Microsoft Cloud, as well as on-premises and hybrid systems. Our expertise is in Microsoft .NET, SQL Server, BizTalk Server and SharePoint Server.

Let's talk

  01342 410223

Find out more

Thank you for contacting us.

We will be in touch with you shortly.