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:
- Identify the tables and columns that are to be registered for full-text search.
- Index the data in the registered columns and populate full-text indexes with the nonextraneous words.
- Issue queries against the registered columns for populated full-text indexes.
- 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 Maintaining Full-Text Indexes.
Note SQL Server does not support full-text search over linked servers.
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 uppercase or lowercase forms of the original search condition.
Full-text indexes are used for a different purpose than regular indexes, which must be updated immediately when data in its associated table changes. Full-text indexes can be synchronized with its table data. Although full-text index population can take time, these updates need not be disruptive. They can be scheduled in the background using the SQL Server Agent job scheduler, the sp_add_job stored procedures, or the Full-text Indexing Wizard.
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.