Professional SQL Server™ 2005 Programming

21.3. Full-Text Query Syntax

Full-text search has its own brand of query syntax. It adds special commands to extend T-SQL and to clearly indicate that we want the full-text engine to support our query rather than the regular SQL Server engine.

Fortunately, the basics of full-text queries are just that — basic. There are only four base statements to work with the full-text engine. They actually fall into two overlapping categories of two statements each:

	Exact or Inflectional Term	Meaning
Conditional	CONTAINS	FREETEXT
Ranked Table	CONTAINSTABLE	FREETEXTTABLE

The conditional predicates both work an awful lot like an EXISTS operator. Essentially they, for each row, provide a simple yes or no as to whether the row qualifies against the search condition provided. You use both of these in the WHERE clause of your queries. On the other hand, the two ranked queries do not provide conditions at all — instead, they return a tabular result set (which you can join to) that includes the key value of all the rows that found matches (that's what you join to) as well as a ranking to indicate the strength of the match.

Let's look more closely at each of the four keywords.

21.3.1. CONTAINS

This term looks for a match based on a particular word or phrase. By default, it's looking for an exact match (that is swim must be swim — not swam), but it can also use modifiers to look for what are called inflectional matches — (words that have the same root — such as swim and swam). CONTAINS recognizes certain ...