Configure and Manage Filters for Search

Applies to: SQL Server Azure SQL Database

Indexing documents in a varbinary, varbinary(max), image, or xml data type column requires extra processing. This processing must be performed by a filter. The filter extracts the textual information from the document (removing the formatting). The filter then sends the text to the word-breaker component for the language associated with the table column.

Filters and document types

A given filter is specific to a given document type (.doc, .pdf, .xls, .xml, and so forth). These filters implement the IFilter interface. For more information about these document types, query the sys.fulltext_document_types catalog view.

Binary documents can be stored in a single varbinary(max) or image column. For each document, SQL Server chooses the correct filter based on the file extension. Because the file extension is not visible when the file is stored in a varbinary(max) or image column, the file extension (.doc, .xls, .pdf, and so forth) must be stored in a separate column in the table, called a type column. This type column can be of any character-based data type and contains the document file extension, such as .doc for a Microsoft Word document. In the Document table in Adventure Works, the Document column is of type varbinary(max), and the type column, FileExtension, is of type nvarchar(8).

To view the type column in an existing full-text index

Note

A filter might be able to handle objects embedded in the parent object, depending on its implementation. However, SQL Server does not configure filters to follow links to other objects.

Installed filters

SQL Server installs its own XML and HTML filters. In addition, any filters for Microsoft proprietary formats (.doc, .xdoc, .ppt, and so on) that are already installed on the operating system are also loaded by SQL Server. To identify the filters that are currently loaded on an instance of SQL Server, use the sp_help_fulltext_system_components stored procedure, as follows:

EXEC sp_help_fulltext_system_components 'filter';   

Note

Even with the latest version of the Office Filter Pack that provides .xlsx support, SQL Server does not support Strict Open XML Spreadsheets. No error will be returned, SQL Server will simply fail to index the contents of any Strict Open XML Spreadsheets.

Non-Microsoft filters

Before you can use filters for non- Microsoft formats, however, you must manually load them into the server instance. For information about installing additional filters, see View or Change Registered Filters and Word Breakers.

See Also

sys.fulltext_index_columns (Transact-SQL)
FILESTREAM Compatibility with Other SQL Server Features