Querying varbinary(max) and xml Columns

CONTAINS and FREETEXT predicates may be used to search indexed varbinary(max) and image as well as xml columns.

Important

The image data type will be removed in a future version of Microsoft SQL Server. Avoid using this data type in new development work, and plan to modify applications that currently use them. Use the varbinary(max) data type instead.

Many document types can be stored in a single varbinary(max), or xml column. Microsoft SQL Server 2005 supports certain document types and provides a filter for these types. For a list of these document types, query the sys.fulltext_document_types catalog view.

When a varbinary(max) or an xml column participates in a full-text index, the full-text service looks at the extensions of the documents contained in the varbinary(max) column and applies a corresponding filter to interpret the binary data and extract the textual information needed for full-text indexing and querying. For an xml column, the xml filter is applied.

Once indexed, the varbinary(max) or xml column can be queried like any other column in a table, using the predicates CONTAINS and FREETEXT

For information on how to insert file data into a varbinary(max) or image column, see OPENROWSET (Transact-SQL).

See Also

Other Resources

CONTAINS (Transact-SQL)
FREETEXT (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006

New content:
  • Added a link to the sys.fulltext_document_types catalog view.