Microsoft® SQL Server™ 2000 Unleashed, Second Edition

Full-Text Searches

You hook into the Microsoft Search Server by using four special SQL commands: CONTAINS, CONTAINSTABLE, FREETEXT, and FREETEXTTABLE.

To learn how to use these functions, you need to go back to the résumé example you looked at earlier in this chapter. Suppose you wanted to find out how many résumés were in your table that had the word “SQL” somewhere.

CONTAINS()

If you didn't have full-text search working, you would write a query like this:

select count(*) 
from resume
where res_text like '%SQL%'

If your table has many résumés in it, this is going to take a while to complete because the entire table (including all text pages) will be scanned. To perform the same search with Search Server, use CONTAINS():

 select count(*) from ...