Considerations when using full text search

There are some considerations to make when deciding whether to use full text indexes over regular indexes:

  • You cannot use aliases in a CONTAINS clause or a CONTAINS search condition.

  • When using duplicate correlation names in a query, a CONTAINS (FROM CONTAINS()) is only supported on the first instance of the correlation name. For example, the following syntax returns an error because of the second CONTAINS predicate involving A:

    SELECT *
    FROM CONTAINS(A contains-query-string) JOIN B ON A.x = B.x,
         CONTAINS(A contains-query-string) JOIN C ON A.y = C.y;

When using external term breaker and prefilter libraries, there are several additional considerations:

  • Querying and updating   The external library must remain available for any operations that require updating, querying, or altering the text indexes built using the libraries.

  • Unloading and reloading   The external library must be available during unloading and reloading of data of data associated with the full text index.

  • Database recovery   The external library must be available to recover the database. This is because the database can not recover if there are operations in the transaction log that involved the external library since the last checkpoint.

 See also

Terms dropped from the text index and CONTAINS queries
Scoring full text search results