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.