Full Text Search in SQL Server

Full text search refers to a technique for searching a computer-stored document or database. In a full text search, the search engine examines all of the words in every stored document as it tries to match search words supplied by the user. Full-text searching techniques became common in online bibliographic databases in the 1970s. Most Web sites and application programs (such as word processing software) provide full text search capabilities. Some Web search engines, such as AltaVista employ full text search techniques, while others index only a portion of the Web pages examined by its indexing system

Full-text Index
* Regular SQL indexes stored under control of the database which they are defined. But full-text indexes stored in the file-system, but administered through the database.
* Only one full-text index can be defined per table.
* Regular SQL indexes, will be updated whenever the new rows has been added, modified, or deleted. But, Full-text index is based on population. Because, full-text service will use more system resources.

Full-text Catalogue
A full-text Catalogue contains full-text indexes in a database. Each catalogue can serve the indexing needs to one or more tables in within a database. A maximum of 256 full-text catalogs can be created on each server.

Full-text enabled Database
A database must be enabled for the full-text service. More than one full-text catalogues can be created and dropped in an enabled full-text database.


Full-text enabled Table
A table should be enabled for full-text support. While creating full-text index, the table will be associated with full-text catalogue. After the table is enabled, you can populate it with the data in columns enabled for the full-text support.