Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


Using SQL FileTables

Using a FileTable in SQL Server 2012

This tutorial describes how to create a FileTable in SQL Server 2012 starting from Syntax to create the database, set the Filestream, creating the table, Dropping Files into the Folder location, creating the FullText Catalog and FullText Index, and finally Querying the FileTable for text in the file_stream field.

Configure the SQL Server Filestream_Access_Level

Using the master database, use the sp_configure command to set up the SQL Server filestream for T-SQL and Win32 access (2).

EXEC sp_configure filestream_access_level, 2
RECONFIGURE
Go

The message will confirm that we are ready to create the FileTable database:

Configuration option 'filestream access level' changed from 0 to 2.
Run the RECONFIGURE statement to install.

T-SQL Code To Create the FileTable Database:

Write the code to create the database with the location for the .mdf\database and .ldf\Log files as well as a Filegroup with a filestream location.

CREATE DATABASE SqlFiles ON PRIMARY (NAME = SqlFiles_data, FILENAME = 'C:\Data\SqlFiles.mdf'),
FILEGROUP SqlFilesFSGroup CONTAINS FILESTREAM (NAME = SqlFiles_FS, FILENAME = 'C:\Data\SqlFileStream')
LOG ON (NAME = 'SqlFile_log', FILENAME = 'C:\Data\SqlFiles_log.ldf');
GO

Set the Filestream access and directory name:

Write a T-SQL statement to set the folder for the Filestream to all Non-Transactional access and set a Directory_Name so that you can create the FileTable. You will not be able to create the fileTable without a Directory_Name.

ALTER DATABASE SqlFiles
SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'SqlFiles_FS');

Create the FileTable in the Database

Create the FileTable in the database using the T-SQL Create Table syntax.

Use SqlFiles
Go

CREATE TABLE SqlFileTable AS FileTable
GO

Create the FullText Catalog

Write the code to create the FullText Catalog

Create FullText Catalog SqlFilesFullTextCatalog as Default
GO

Create the FullText Index on the FileTable

Write the code to create the fulltext search index on the file_stream column - the column that will store the text of the files copied to the Directory_Name specified earlier. Use the Primary Key of the new FileTable as the Index.

Create FullText Index on dbo.SqlFileTable
(name Language 1033, File_stream type column file_type Language 1033)
key Index [PK__SqlFileTable__5A5B77D5FB0368BF]
on SqlFilesFullTextCatalog
with Change_Tracking Auto, StopList=system
Go

Enable the Full-Text Index

In SSMS, right click the filetable, select Full-Text Index and Enable Full-Text Index.

File Table Full Text Index

Add Documents to the FileTable folder for Indexing

In SSMS, Right click the fileTable and select Explore FileTable Directory. A Windows Explorer window will pop up with the network location of the SQL server database and directory. I have put a few files into this directory which were indexed. I can verify this with a quick query of the FileTable.

Explore FileTable Directory

FileTable Output to Txt

Select Statement for FileTable

Query the FileTable for Text in the Files

In SSMS, I can query for the contents of the my documents by using the Contains T-SQL statement in my Where clause. I created the SQLTextFile.txt and SQLfulltextSearch.doc files with a sentence about my little bird who is a cockatiel. Another query will verify that document contents are searchable.

Text and Word document data to search:

Text document data Word Document data

select * from SqlFileTable where contains(file_stream, 'cockatiel')

I added a convert statement to read the file_stream field of the FileTable. The Word document data does not convert, but the Text file was clear as a varchar type. Results of SQL Statement on FileTable

Setting up the new FileTable feature in SQL Server 2012 is not difficult to do.