Creating a Full-Text Index on a FileTable in SQL Server 2012

This post describes how to select the appropriate indexes, columns, and options when creating a full-text index over the documents and files stored in a FileTable in SQL Server 2012.

The FileTable feature in SQL Server 2012 makes it easier than ever to store documents and files in the database – you can simply drag them into the folder that represents the table in Windows Explorer, or you can save them directly from Windows applications like Microsoft Word or Excel. At the same time, SQL Server 2012 enhances the value of saving documents in the database – you gain improved performance and new features from Full-Text Search, along with the new capabilities of Semantic Search.

Let’s take a look at the appropriate values to specify when you’re creating a new full-text index over a FileTable. We’ll consider these two screens of the Full-Text Indexing Wizard:

  • Select an Index
  • Select Table Columns

Select an Index

A full-text index requires an existing unique index on single column in the table to be indexed. This selection corresponds to the KEY INDEX clause in the Transact-SQL CREATE FULLTEXT INDEX statement.

When you create a new FileTable, three indexes are automatically created. Only two of these are single-column indexes, so only the first two below are candidates to be considered for the full-text key column. (See “Indexes that are created when you create a new FileTable” in Create, Alter, and Drop FileTables.)

Type of index Column(s)
Primary key (name begins with PK_) path_locator (hierarchyid)
Unique index (name begins with UQ_) stream_id (rowguidcol)
Unique index (name begins with UQ_) parent_path_locator (hierarchyid)
name (nvarchar(255))


We prefer to choose the unique index on the rowguidcol stream_id column in our own demos and samples. The stream_id column is the equivalent of an ID for the stored document, so it seems to be the natural choice. It’s easy to identify this index by its UQ_ prefix in the dropdown list in the on the Select an Index page of the Full-Text Indexing Wizard.

In Microsoft’s own internal functional testing, there seems to be no significant difference between choosing the primary key index or the unique index.

Select Table Columns

A FileTable has a fixed schema, so we know in advance the list of available columns and their data types.

Available Columns

As you can see in the screen shot below, a FileTable has three nvarchar or varbinary columns that support full-text indexing:

  1. You almost certainly want to index the varbinary(max) file_stream column that contains the documents themselves.
  2. You may want to index the nvarchar name column that contains the file name.
  3. It’s not useful to index the file_type column.

Type Column

Use the file_type column to provide the file extension to the full-text indexing filters. A typical value in this column is “doc” or “docx”.

Statistical Semantics

Check the checkbox in this column (on the file_stream row) to build semantic indexes on your documents in addition to the full-text indexes. Read more about the new types of queries that this enables at Semantic Search. This blog post also provides a good introduction to the capabilities of semantic search in SQL Server 2012.

I hope you find these new features in SQL Server 2012 useful!