You are a database developer on an instance of SQL Server 2008. The Name and Description columns of the AssemblyProcess table are full-text enabled. You issue the following SELECT statement:
SELECT ID, Description FROM AssemblyProcess WHERE CONTAINS(*, ‘ "weld" AND "grind" ‘);
What is returned in the result?
A. the ID and Description values for all rows containing the words weld and grind near each other in the full-text enabled columns
B. the ID and Description values for all rows with a Name containing the word weld or the word grind
C. the ID and Description values for all rows containing the words weld and grind in the full-text enabled columns
D. the ID and Description values for all rows containing the phrase "weld and grind" in the full-text enabled columns
Correct Answer: C
Explanation/Reference:
The CONTAINS predicate can be used to search character-based columns for an inflectional form of a specific word, a specific word or phrase, a word or phrase that is near another word or phrase, or words or phrases with weighted values. You can combine several conditions using the AND, OR, and NOT keywords. The CONTAINS search condition is case insensitive. The syntax for the CONTAINS predicate is as follows:
CONTAINS ({column | *}, ‘<contains_search_condition>’)
The search condition is enclosed in single quotes, but individual words or phrases inside the search condition can be offset with double quotes. If the condition only includes single quotes, the argument is passed as a single search condition. A search condition of ‘ "weld" AND "grind" ‘returns all rows having both weld and grind in the column. In contrast, a search condition of ‘weld AND grind’ returns all rows having weld and grind separated by a stopword. Because stopwords, or noise words, are not recognized, the search condition of ‘weld OR grind’ would produce an identical result.
The given statement does not return the ID and Description values for all rows containing the words weld and grind near each other in the full-text enabled columns. To perform such a search, you would use the NEAR keyword in the CONTAINS predicate.
The given statement does not return the ID and Description values for all rows with a Name containing either the word weld or grind. Using an asterisk (*) in the first argument of the CONTAINS predicate specifies that all columns registered for full-text searching be used.
The given statement does not return the ID and Description values for all rows containing the phrase "weld and grind" in the full-text enabled columns.