SQL SERVER – 2008 – Creating Full Text Catalog and Full Text Search

Full Text Index helps to perform complex queries against character data. These queries can include word or phrase searching. We can create a full-text index on a table or indexed view in a database. Only one full-text index is allowed per table or indexed view. The index can contain up to 1024 columns.
For creating Full-Text Index check reference link.

After Full-Text Index is created and populated, you can write the query and use in searching records on that table which provides better performance.

Example:
You have table name Books that has columns named BookTitle and Description. There is a full-text index on these columns. You need to return rows from table in which the word 'computer' exists in either column.
SELECT * FROM BOOKS WHERE FREETEXT(*, 'computer')

Reference:
http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/
http://msdn.microsoft.com/en-us/library/ms142571.aspx

Conclusion

Full text indexing is a great feature that solves a database problem, the searching of textual data columns for specific words and phrases in SQL Server databases. Full Text Index can be used to search words, phrases and multiple forms of word or phrase using FREETEXT() and CANTAINS() with “and” or “or” operators.