skip to Main Content

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 subfolders and files in that folder. The benefit 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 Metadata 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 [email protected]

About the Author

As Co-Founder of Ballard Chalmers, Geoff is the company’s CTO, directing technical strategy, overseeing technical consultants, managing larger development projects and ensuring technical delivery quality standards.

Education, Membership & Awards

Geoff graduated from the University of London with a BSc (Hons) in Mathematics and computing and a Masters degree in Database Management Systems. Additionally, he is a certified SQL Server Development, Microsoft Certified IT Professional (MCITP) and Microsoft Certified Technical Specialist (MCTS).

His professional memberships include Charted Engineer (Software), Member of the British Computer Society (MBCS), Member of European Federation of Engineers (FEANI) and Fellow of the Institute of Analysts and Programmers (FIAP).

 

Previous Post
Next Post
Back To Top
Contact us for a chat