You are a database developer. You plan to design a database solution by using SQL Server 2008.
You have a Web site supported by a database that has the full-text search component installed. You plan to create a table named Courses that will have the following structure.
Users of the Web site will search for courses based on the CourseTitle field.
You need to construct a full-text query that ensures the following compliances when a user launches the search for a course:
• Rows are returned when the exact search phrase is found.
• Rows are in order of how well they match with the search phrase.
What should you specify in the full-text query?
A. A FREETEXTTABLE function
B. A FREETEXT predicate
C. A CONTAINSTABLE function
D. A CONTAINS predicate
Correct Answer: C
Explanation/Reference:
Full-text queries use the full-text predicates (CONTAINS and FREETEXT) and functions (CONTAINSTABLE and FREETEXTTABLE).
CONTAINS and FREETEXT are specified in the WHERE or HAVING clause of a SELECT statement. They can be combined with any of the other Transact-SQL predicates, such as LIKE and BETWEEN.
The CONTAINS and FREETEXT predicates return a TRUE or FALSE value. They can be used only to specify selection criteria for determining whether a given row matches the full-text query. Matching rows are returned in the result set.
CONTAINS and FREETEXT are useful for different kind of matches, as follows:
• Use CONTAINS (or CONTAINSTABLE) for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches. When using CONTAINS, you must specify at least one search condition that specifies the text that you are searching for and the conditions that determine matches.
• Use FREETEXT (or FREETEXTTABLE) for matching the meaning, but not the exact wording, of specified words, phrases or sentences (the freetext string).
Matches are generated if any term or form of any term is found in the full-text index of a specified column.
The CONTAINSTABLE and FREETEXTTABLE functions are referenced like a regular table name in the FROM clause of a SELECT statement. They return a table of zero, one, or more rows that match the full-text query. The returned table contains only rows of the base table that match the selection criteria specified in the fulltext search condition of the function.
Queries using one of these functions return a relevance ranking value (RANK) and full-text key (KEY) for each row, as follows:
• The KEY column returns unique values of the returned rows. The KEY column can be used to specify selection criteria.
• The RANK column returns a rank value for each row that indicates how well the row matched the selection criteria. The higher the rank value of the text or document in a row, the more relevant the row is for the given full-text query.
CONTAINSTABLE is useful for the same kinds of matches as CONTAINS, and FREETEXTTABLE is useful for the same kinds of matches as FREETEXT. When running queries that use the CONTAINSTABLE and FREETEXTTABLE functions you must explicitly join rows that are returned with the rows in the SQL Server base table.
