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

Geoff Ballard

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).

 

Our Technologies

HAVE YOU SEEN OUR NEWSLETTER?

ABOUT US

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

01342 410223

[email protected]

 

CONTACT US






This form collects your name and details so that we may answer you. Check out our Privacy Policy for full details on how we protect and manage your data.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

© 2020 Ballard Chalmers Ltd. All Rights Reserved

Back To Top