Using Full Text Search in SQL Server 2008
Populate a Full-Text Index
The final step before you can start using Full-Text Search Queries is to Populate the Full-Text Index. This can be done by right clicking the HumanResources.Employee table and selecting Full-Text Index > Start Full Population as shown below.
You will be shown a success message once the Full-Text Index Population is completed successfully:
Once the Full-Text Index is successfully created and populated the next step is to run full-text search queries. SQL Server 2008 provides a range of full-text predicates such as CONTAINS and FREETEXT as well as row set valued functions such as CONTAINSTABLE and FREETEXTTEABLES for writing full-text search queries.
Use the FREETEXT predicate in a WHERE clause to search columns containing character based data types, this find matches for the meaning of the word(s) and not just the exact text in the search condition. When FREETEXT is used, the SQL Server Query engine internally assigns each term a weight and then finds the matches.
Use the CONTAINS predicate in a WHERE clause to search columns containing character based data types for precise or less precise matches to a single words or phrases. If you want to combine different words within a search, then you need to use conditions such as AND, OR etc within the search queries.
Below are some of the examples of full-text search queries which can be run against theHumanResources.Employee table in AdventureWorks.
SELECT EmployeeID, Title
WHERE FREETEXT(*, ‘Recruiter’)
SELECT TOP 5 EmployeeID, Title
WHERE CONTAINS ([Title], ‘Recruiter OR Manager’)
In this article you have seen how easily you can configure and use the Full-Text Search Feature of SQL Server 2008. Database developers can use this feature to perform complex queries against character data in SQL Server 2008 tables.