How to manage text indexes

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. See How to manage text configuration objects.

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. See Text index refresh types.

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


Text index refresh types
How to create a text index
How to refresh a text index
Altering text indexes overview
How to alter a text index
How to view text index info in the database