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.
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.
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:
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.
Setting up the new FileTable feature in SQL Server 2012 is not difficult to do.