Implementation of Full-text Search

With a full-text query, you can perform a linguistic search of character data in tables enabled for full-text search. A linguistic search operates on words and phrases, unlike the LIKE predicate, which is used to search character patterns. Also, the Full-Text Search feature can weigh query terms and report how well a match scored or ranked against the original search term.

Implementing a full-text search in a given database involves these tasks:

1. Identify the tables and columns that are to be registered for full-text search.
2. Index the data in the registered columns and populate full-text indexes with the nonextraneous words.
3. Issue queries against the registered columns for populated full-text indexes.
4. Ensure that subsequent changes to the data in registered columns get propagated to the index, thus keeping the full-text index synchronized with the data.

Tasks 1, 2, and 4 are accomplished using graphical tools and wizards, available through SQL Server Enterprise Manager or built-in procedures. For more information about administering full-text indexes, see Creating and Maintaining Full-text Indexes.

Information about issuing queries against registered columns for populated full-text indexes (task 3) is the primary subject of the full-text topics in Accessing and Changing Data.

Unlike standard relational database indexes, full-text indexes are not instantly modified when values in full-text registered columns are updated, when rows are added to full-text registered tables, or when rows are deleted from full-text registered tables. Rather, full-text indexes are repopulated asynchronously because:

• It typically takes significantly more time to update a full-text index than a standard index.
• Full-text searches are usually less precise than standard searches in that the search result is a set of rows that contain the word or phrase being searched no matter where they appear in the character stream. For example, using a standard index, a search returns a precise character pattern or number that exactly matched the original query. For full-text search, you can retrieve close approximations of the data, such as the plural forms of a noun, the various forms that a verb may take, or the upper- or lowercase forms of the original search condition.

  Full-text indexes are used for a different purpose than regular indexes, which must be immediately updated when data in its associated table changes. Full-text indexes can be synchronized with its table data. However, because full-text index population can take time, these updates should be scheduled during periods of low database access, such as in the evenings or on weekends.

When you repopulate an index, after changes to data have been made, the unique key column values are passed to the index engine to identify those items that need to be reindexed.