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.

FREETEXT Predicate
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.

CONTAINS Predicate
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.

USE AdventureWorks

GO

SELECT EmployeeID, Title

FROM HumanResources.Employee

WHERE FREETEXT(*, ‘Recruiter’)

GO

SELECT TOP 5 EmployeeID, Title

FROM HumanResources.Employee

WHERE CONTAINS ([Title], ‘Recruiter OR Manager’)

GO

Conclusion

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.