When you perform a full text search, you are searching a text index (not table rows). So, before you can perform a full text search, you must create a text index on the columns you want to search. A text index stores positional information for terms in the indexed columns. Queries that use text indexes can be faster than those that must scan all the values in the table.

When you create a text index, you can specify which text configuration object to use when creating and refreshing the text index. A text configuration object contains settings that affect how an index is built. If you do not specify a text configuration object, the database server uses a default configuration object.

You can also specify a refresh type for the text index. The refresh type defines how often the text index is refreshed. A more recently refreshed text index returns more accurate results. However, refreshing takes time and can impede performance. For example, frequent updates to an indexed table can impact performance if the text index is configured to refresh each time the underlying data changes.

You can use the VALIDATE TEXT INDEX statement to verify that the positional information for the terms in the text index is intact. If the positional information is not intact, an error is generated.

To view settings for existing text indexes, use the sa_text_index_stats system procedure.

 See also