How to Import and Export Unstructured Data in SQL Server – FileTables

How to Import and Export Unstructured Data in SQL Server – FileTables

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
Good evening (or good morning) friends all around the globe! Wherever you are, whatever you do, wishing you a nice rest of the day (or night!) 🙂

It’s been more than a month since my last article but here’s a tip: I’m working on something really, really massive…and yes, it has to do with the In-Memory OLTP Engine of SQL Server 2014…more news early next month! 🙂

This is the last of a series of posts that deal with the task of importing and exporting unstructured data in SQL Server.

The first article of this series, explained how you can store and export binary files in earlier versions of SQL Server such as SQL Server 2005 with the use of the image datatype.

The second article presented the FILESTREAM technology which was first introduced in SQL Server 2008.

This article discusses the FileTables feature which builds on top of SQL Server FILESTREAM technology. FileTables was first introduced in SQL Server 2012.

The FileTables feature allows the user to store unstructured data (i.e. files, documents, images, etc.) in special tables in SQL Server called FileTables, but being able to access them from the file system. To this end, if you have an application that needs to access unstructured data, you can directly access them from the File System even though the data is stored into FileTables in SQL Server.

Enough with the talking, let’s see an example of using this great feature.

First, let’s enable FILESTREAM (this is a prerequisite – for more info, see the second article of this series):

Figure 1: Enable FILESTREAM.

Then, let’s create a FILESTREAM-enabled database (make sure to update the path “C:BlogSQLData” if you are using a different one):

CREATE DATABASE FileStreamDB 
ON
PRIMARY ( NAME = FileStreamDBData, FILENAME = ‘C:BlogSQLDatafilestreamDB_data.mdf’),
FILEGROUP FileStreamGroup_1 CONTAINS FILESTREAM( NAME = FileStreamDBFS,
    FILENAME = ‘C:BlogSQLDatafilestream1’)
LOG ON  ( NAME = FileStreamDBLogs, FILENAME = ‘C:BlogSQLDatafilestreamDB_log.ldf’);
GO

Then we need to enable non-transactional access at the database level as well as specify the directory for FileTables:

ALTER DATABASE FileStreamDB
    SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N’FileTablesDir’)
GO

Now it’s time to create the FileTable:

USE FileStreamDB;
GO

CREATE TABLE DocumentStore AS FileTable
    WITH ( 
          FileTable_Directory = ‘FileTablesDir’,
          FileTable_Collate_Filename = database_default
         );
GO

OK, it’s time for the magic to take place. Let’s check the contents of the FileTable:

Figure 2: The FileTable in SSMS Object Explorer.

As you can see, the FileTable “DocumentStore” is currently empty:
Figure 3: Checking the contents of the FileTable.

Now, let’s open the FileTable directory in Explorer, and by accessing it directly from the Windows File System, copy some files:

Figure 4: Access the FileTable directory in Explorer.
Figure 5: Copy files in the FileTable directory.

Now, let’s check again the contents of the FileTable from SSMS:

Figure 6: Checking the FileTable contents after copying the files from Windows Explorer.

And that’s it! As you can see in the above screenshot, by copying the two files from Windows Explorer directly into the FileTable directory, the corresponding two records have been created in the FileTable in the SQL Server instance! Now you can access the files either from the SQL Server Database Engine via T-SQL or directly from the Windows File System!

FileTables along with the FILESTREAM technology, enables the user with more options when it comes to storing binary objects in SQL Server. The seamless integration of FileTables with the Windows File System (NTFS) allows the user to store large binary objects in fast speeds and at the same time to be able to use the powerful features of SQL Server’s Database Engine to traverse this data (i.e. full-text search, semantic search, etc.).


My Latest Projects:


Recommended eBooks on SQL Server:

Developing with SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Developing SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.