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

Below   are some of the examples of  full-text search
queries which can be run against theHumanResources.Employee table in



EmployeeID, Title


FREETEXT(*, ‘Recruiter’)


TOP 5 EmployeeID,


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.

Pages: 1 2 3


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |