Full-Text Search Filters
When a cell in a varbinary(max), or image column contains a document with a supported document-file extension, full-text search uses a filter to interpret the binary data. The filter, which implements the iFilter interface, extracts the textual information from the document and submits it for indexing. To identify the filters included in an instance of SQL Server, use the sp_help_fulltext_system_components (Transact-SQL) stored procedure, which returns information for the registered word-breakers, filter, and protocol handlers..
Many document types 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 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 FileExtension column is of type nvarchar(8). For more information on the schema of this table, see Document Table (AdventureWorks).
When creating a full-text index on a varbinary(max), or image column you must identify a corresponding type column that has the extension information so that SQL Server knows which filter to use. The IDs of the full-text indexed column and its associated type column can be found using the sys.fulltext_index_columns catalog view.
After the varbinary(max), or image column is full-text indexed, it can be queried using the search predicates CONTAINS and FREETEXT.
Note
A filter might be able to handle objects embedded in the parent object, depending on its implementation. SQL Server does not configure filters to follow links to other objects.
See Also
Concepts
Other Resources
sys.fulltext_index_columns (Transact-SQL)