Using Full-text Predicates to Query image Columns

CONTAINS and FREETEXT predicates may be used to search indexed image columns.

Many document types can be stored in a single image column. Microsoft® SQL Server™ supports certain document types and provides a filter for these types. This release provides filters for Office documents, text files, and HTML files.

When an image column participates in a full-text index, the full-text service looks at the extensions of the documents in the image column and applies a corresponding filter to interpret the binary data and extract the textual information needed for indexing and querying.

Thus, when you set up full-text indexing on an image column in a table, you must create a separate column to hold information about the document. 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. If the type column is NULL, the full-text service will assume the document is a text file.

• In the Full-Text Indexing Wizard, if you select an image column for indexing, you must also specify a Binding column to hold the document type.
  
  
• The sp_fulltext_column stored procedure also accepts an argument for the column to contain the document types.
  
  
• The sp_help_fulltext_columns stored procedure also returns column name and column id of the document type column.

For more information about setting up full-text indexes and searches on image columns, see Filtering Supported File Types.

Once indexed, the image column can be queried like any other column in a table, using the predicates CONTAINS and FREETEXT.