This post is about working with FileTables in SQL Server 2012. I’m not going to go over FileTables in general or how to create them as there are many blogs walking through the procedure and setting up the prerequisites; I’ve put links to some of them in the Resources section at the bottom of this page.
I’m using a virtual server running the Developer Edition of SQL 2012 on Windows Server 2008 R2 called VMBI2012, hosted in Virtual Box. That’s not a requirement and you can duplicate everything here on the same box, but I wanted to point out an issue with certain file types when trying to access them directly. Also you don’t need the Developer Edition of SQL, Microsoft says that FileTables are supported in all editions.
The only part you may need to add is the Semantic database. It’s not an option when you install SQL but look for the SemanticLanguageDatabase.msi package on your installation media or download from Microsoft here. After you run the package attach the database (it’s placed in C:\Program Files\Microsoft Semantic Language Database\ by default but you can move it). Then just register it;
EXEC sp_fulltext_semantic_register_language_statistics_db @dbname = N’semanticsdb’;
OK, so I’ve configured my server to use Filestreaming, and I added the semantic database. I created a database that contains a FileTable called Documents and also set up a full text catalog. The script I used is as follows, if you want to follow along at home;
CREATE DATABASE DenaliDemoDB
ON PRIMARY
(
NAME = 'DenaliDemoDB_data',
FILENAME = 'C:\SQL2012\DATA\DenaliDemoDB_data.mdf',
SIZE = 512000KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 102400KB
),
FILEGROUP FSSecondary CONTAINS FILESTREAM
(
Name = FileTableData,
FILENAME = 'C:\SQL2012\FileTables\Data'
)
LOG ON
(
NAME = 'DenaliDemoDB_log',
FILENAME = 'C:\SQL2012\DATA\DenaliDemoDB_log.ldf',
SIZE = 102400KB,
MAXSIZE = 2048GB,
FILEGROWTH = 25600KB
)
WITH FILESTREAM
(
NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = 'MyScripts'
);
USE DenaliDemoDB;
CREATE TABLE dbo.Documents AS FILETABLE
WITH
(
FILETABLE_DIRECTORY = 'DocumentsFT',
FILETABLE_COLLATE_FILENAME = database_default
);
CREATE FULLTEXT CATALOG Documents_Catalog WITH ACCENT_SENSITIVITY = ON;
CREATE FULLTEXT INDEX ON dbo.Documents
(
name
LANGUAGE 1033
STATISTICAL_SEMANTICS,
file_type
LANGUAGE 1033
STATISTICAL_SEMANTICS,
file_stream
TYPE COLUMN file_type
LANGUAGE 1033
STATISTICAL_SEMANTICS
)
KEY INDEX PK__Document__5A5B77D5962E5D8F
ON Documents_Catalog
WITH CHANGE_TRACKING AUTO, STOPLIST = SYSTEM;
There’s a few things to point out here. First, notice the name of the KEY INDEX. This is the name SQL assigned when I created the FileTable, your name will most likely be different. You can always rename it before you create the catalog. Second, that for each column I’m declaring STATISTICS_SEMANTICS. This is to take advantage of the semantic searching, but it’s optional. If you create it in SSMS it’s just a checkbox, like so.
After that I created 4 .txt files called text1.txt through text4.txt. The content in each is simple:
- This is text one. Magic
- This is text 2. French Roast
- This is text 3. French Roast and Columbian
- This is text four. Magic and Wizards
If you use T-SQL to query the table you’ll see 4 records, one for each file. If you browse to the shared folder you’ll see the same files. However if you try to open any of them you get this error . 
According to Books Online that’s because FileTables don’t support memory-mapped files. Paint files are another. However you probably don’t want users editing files directly on the server anyway. Instead use another computer and map a drive to the shared folder and edit it that way. I used my host machine to map to the vm’s shared director to edit text4.txt. Now it says “What happens behind the scenes when we manipulate this file inside SQL?”
Now we’re set to work with the FileTable. First, to find the name of the shared directory use the FILETABLEROOTPATH function with the name of the FileTable as a parameter.
SELECT FILETABLEROOTPATH('dbo.Documents')
Use the GetFileNamespacePath method of the file_stream column to return the path of each file in the FileTable. It takes two parameters. The first is is_full_path which returns the file’s path, with 0 (the default) returning the relative path and 1 returning the UNC path. The second is @option, where 0 (the default) shows the NetBIOS name, 1 returns the server name, and 2 returns the complete path.
SELECT file_stream.GetFileNamespacePath(1, 2) as FileLocation
FROM dbo.Documents
Finally, I can use the full text catalog I set up earlier to search the file_stream column. If I search for the word “french” I should get back text2.txt and text3.txt.
SELECT file_stream.GetFileNamespacePath() as FileLocation
FROM dbo.Documents
WHERE CONTAINS(file_stream, 'french')
Semantic Search builds on the capabilities of the full text catalog. Not only can you search for matches but also by key phrases. Using the SEMANTICKEYPHRASETABLE function can show documents by searching for a keyphrase and the score of a likely match.
SELECT FILETABLEROOTPATH('dbo.Documents') + '\' + d.name AS FilePath, s.keyphrase, s.score
FROM dbo.Documents AS d
INNER JOIN SEMANTICKEYPHRASETABLE(dbo.Documents, *) AS s
ON d.path_locator = s.document_key
WHERE s.keyphrase LIKE '%r%'
ORDER BY s.score DESC;
SEMANTICSSIMILARITYDETAILSTABLE compares two documents and shows the similarity between the two…
DECLARE @Document1 hierarchyid;
DECLARE @Document2 hierarchyid;
SET @Document1 = (SELECT path_locator FROM dbo.documents WHERE name = 'text2.txt');
SET @Document2 = (SELECT path_locator FROM dbo.documents WHERE name = 'text3.txt');
SELECT *
FROM SEMANTICSIMILARITYDETAILSTABLE
(
dbo.Documents,
file_stream, @Document1,
file_stream, @Document2
)
ORDER BY score DESC;
Finally, SEMANTICSIMILARITYTABLE can be used to compare all documents to a single file.
DECLARE @Document3 hierarchyid;
SET @Document3 = (SELECT path_locator FROM dbo.documents WHERE name = 'text1.txt');
SELECT s.source_column_id, s.matched_column_id, FILETABLEROOTPATH('dbo.Documents') + '\' + d.name AS FilePath, s.score
FROM dbo.documents AS d
INNER JOIN SEMANTICSIMILARITYTABLE
(
dbo.Documents,
file_stream, @Document3
) AS s
ON d.path_locator = s.matched_document_key
ORDER BY score DESC;
References
To get more information on FileTables or Semantic Searches start with Books Online
FilesTables in Books Online
http://msdn.microsoft.com/en-us/library/ff929144.aspx
Semantic Search on BOL
http://msdn.microsoft.com/en-us/library/gg492075.aspx
Bob Beauchemin has some very informative posts on filestreaming and filetables
http://sqlskills.com/BLOGS/BOBB/category/Filestream-Storage.aspx
Finally, here’s a few posts that walk through the process of setting up file tables.
Beyond Relational – File Table in SQL Server 2012 – Great flexibility to manage filestream data
http://beyondrelational.com/modules/24/syndicated/389/Posts/11829/file-table-in-sql-server-2012-great-flexibility-to-manage-filestream-data.aspx
SQL Server Central
http://www.sqlservercentral.com/blogs/sqlservernotesfromthefield/2012/01/03/sql-server-2012-filetable-part-1/
basics of database design and T-SQL. There’s a very good chapter covering backup and recover of databases. 
