Using SQL Server FileTables Part 2

SQL Server 2014’s FileTable provides an easy way to integrate the Windows Server file system with SQL Server’s relational database. In part 1, I covered the basic requirements and limitations of the FileTable feature as well as showing you how to create a FileTable. In this article, you’ll see how you can load data into a FileTable as well as how you can work with a FileTable from both SQL Server and Windows.

Keys and Constraints

As you saw in Part 1, when you create FileTable you get a predefined schema. In other words, you don’t create the columns for a FileTable. Likewise, there are several predefined keys and constraints that SQL Server creates when you create a FileTable. To see the different database objects associated with a FileTable you can run the following query.

SELECT OBJECT_NAME(parent_object_id) AS 'FileTable', 
OBJECT_NAME(object_id) AS 'System-defined Object'
    FROM sys.filetable_system_defined_objects
    ORDER BY FileTable, 'System-defined Object';
GO

Since a FileTable has a pre-defined schema you cannot add or change the columns in a FileTable. However, you can add custom indexes, triggers, and constraints to a FileTable. For information about using the ALTER TABLE statement to enable or disable the FileTable namespace or make other changes you can check out Manage FileTables

Loading Data into the FileTable

Loading data into a FileTable is very easy. Using Windows File Explorer you can simply open the share on the local system at \\127.0.0.1\mssqlserver then navigate to the directory where you created your FileTable. In part 1, I used the name of FileTableDB for the database directory and MyFileTableDir for the FileTable that was call MyFileTable. In Figure 1, you can see where I’ve added a number of .docx file to the FileTable directory by dragging and dropping them from Windows File Explorer. You can also use any of the other Windows file copy methods including the PowerShell copy-item and the command shell move, copy, xcopy, or robocopy commands.

Figure 1 – Copying file to a FileTable with Windows File Explorer

You can also view the folder where SQL Server stores the FileTable data using SQL Server Management Studio (SSMS). Open Object Explorer and expand the Databases node then navigate to your FILESTREAM enabled database. In this example, the database was called MyFileStreamDB. Expand MyFileStreamDB, Tables, FileTable and then right click Explore FileTable Directory. This will open up Windows File Explorer with the folder where the FileTable data is stored.

You can also add data into a FileTable using the standard T-SQL INSERT statement. The following INSERT statement demonstrates how you can use the OPENROWSET function to add a new row to MyFileTable which will also add a file to the MyFileTableDir directory.

INSERT INTO [dbo].[MyFileTable]
([name],[file_stream])
SELECT
               'Writing Perfmon to SQL Server.docx',
               * FROM OPENROWSET(BULK N'c:\temp\Writing Perfmon to SQL Server.docx',
               SINGLE_BLOB) AS FileData
GO

Here the T-SQL INSERT INTO statement is used in combination with the OPENROWSET function to add a row to the FileTable. The name column will be added with the value of ‘Writing Perfmon to SQL Server.docx’ and the OPENROWSET function will populate the file-Stream column with the contents of the file ‘c:\temp\Writing Perfmon to SQL Server.docx’.

Querying the FileTable

SQL Server automatically synchronizes this directory with the MyFileTable database object. After moving data files into the FileTable you can query it just like any other table. The following example shows how you can do a quick SELECT on the FileTable named MyFileTable which will retrieve the values stored in the MyFileTableDir in the Windows file system.

USE [MyFileStreamDB]
GO

SELECT name, file_type, path_locator, file_stream
FROM MyFileTable
GO

A partial view of the query results for MyFileTable are shown in Figure 2.

Figure 2 – Querying the FileTable

The name column contains the name of the file and the file_type column contains the file extension, path_locator is a hierachyid data type containing the path to the file and the file_stream column contains the contents of the data file.

You can also retrieve the full Windows file system path to the file represented by a row in the FileTable. The following query shows how you can use the FileTableRootPath function to retrieve the path to the FileTable root which is defined when you create the database and the GetFileNamespacePath to return the path that’s defined to create the FileTable and the name of the file.

USE MyFileStreamDB

DECLARE @filetableroot varchar(256)
DECLARE @filepath varchar(1000)

SELECT @filetableroot = FileTableRootPath();

SELECT @filepath = @filetableroot + file_stream.GetFileNamespacePath()
FROM MyFileTable
WHERE Name = N'Linked Servers.docx';

PRINT @filepath

The previous query returns the following results with the MyFileTable example for the file named Linked Servers.docx.

\\SQL2014-1\MSSQLSERVER\FileTableDB\MyFileTableDir\Linked Servers.docx

Using Full-Text Search with the FileTable

You can also use Full-Text Search to search for values inside the file_stream column of the FileTable. Unfortunately, Full-Text Search doesn’t support every file type that you may want to search. It does support searching in about 50 different file types. You can run the following query on the sys.fulltext_document_types DMV to see the different file types that are supported by Full-Text Search.

select * from sys.fulltext_document_types

Notably, Full-Text Search does not support the more recent Office file types like .docx but it does support the earlier .doc file type. To use Full-Text Search you first need to create a unique index for the table that you will be searching. Next, you need to create a Full-Text catalog and then a Full-Text index on the column that you want to search. While full coverage of using Full-Text Search is beyond the scope of this article the following T-SQL code shows how you can create a Full-Text Catalog for searching the file_stream column in a FileTable.

CREATE UNIQUE INDEX UQ_StreamID ON MyFileTable(stream_id);
GO

CREATE FULLTEXT CATALOG MyFileTableFT AS DEFAULT
GO

CREATE FULLTEXT INDEX ON MyFileTable(file_stream TYPE COLUMN file_type)
  KEY INDEX PK__MyFileTa__5A5B77D5F9C6F5DE ON MyFileTableFT;
GO

The following query shows how you can return results from the FileTable named My FileTable where the file_type column equals ‘txt’ and the file_stream column contains the value of ‘SQL2014-1’.

SELECT name FROM [dbo].[MyFileTable]
WHERE file_type = 'txt' and CONTAINS(file_stream,'SQL2014-1')