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.
]]>