You are a database developer on an instance of SQL Server 2008. Your HR database contains a Course table that contains descriptions for training courses provided by your company.
The HR database has been enabled for full-text searches. You have created a full-text index on the Description column in the Course table.
You execute the following Transact-SQL:
SELECT Description
FROM HR.dbo.Course
WHERE CONTAINS (Description, ‘FORMSOF (INFLECTIONAL, budget)’);
What will be returned?
A. only the Description values for rows in the Course table that contain a form of the word budget
B. the Description values for rows in the Course table that contain a form of the word budget along with a relevance ranking
C. only the Description values for rows in the Course table that contain a word with a meaning similar to the word budget
D. only the Description values for rows in the Course table that contain the word budget
Correct Answer: A
Explanation/Reference:
The given statement will return only the Description values for rows in the Course table that contain a form of the word budget. The CONTAINS predicate can be used in a WHERE clause 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. In this scenario, you used WHERE CONTAINS (Description, ‘FORMSOF (INFLECTIONAL, budget)’). This would return all Description values that contained a form of the word budget.
For example, SQL Server would return rows with a Description column containing the words budget, budgeted, budgeting, budgets, or budget’s.
The given statement will not return only the Description values for rows in the Course table that contain the word budget. The CONTAINS predicate can be used to perform such a search. The following statement would accomplish this:
SELECT Description
FROM HR.dbo.Course
WHERE CONTAINS (Description, ‘budget’);
You can use the CONTAINS predicate to find words with similar meanings. To accomplish this, you must first configure the appropriate thesaurus file to include the desired synonyms. Then, you could use the following statement:
SELECT Description
FROM HR.dbo.Course
WHERE CONTAINS (Description, ‘FORMSOF (THESAURUS, budget)’);
You can also use the FREETEXT predicate to search for matches with similar meaning rather than the exact wording. With the FREETEXT predicate, strings are first separated into words, word-stemming is performed to determine other word forms, and then the result is passed through the thesaurus to identify words with similar meanings. The primary difference between using the thesaurus with the CONTAINS predicate and using the FREETEXT predicate is that CONTAINS only uses the thesaurus and does not perform word stemming. The FREETEXT predicate accepts two parameters. The first parameter specifies one or more full-text enabled columns to search. An asterisk ( *) in the first parameter indicates that all fulltext enabled columns should be searched. The second parameter is a character string that can contain words or phrases. If the search should be performed on phrases, the search string should be enclosed in double quotation marks instead of single quotation marks. The following statement would search the Description column of the Course table for descriptions associated with budgeting tasks:
SELECT Description
FROM HR.dbo.Course
WHERE FREETEXT (Description, "budgeting tasks");
The given statement will not return the Description values for rows in the Course table that contain a form of the word budget along with a relevance ranking because the CONTAINS predicate does not return a relevance ranking. You can use the CONTAINSTABLE function in the FROM clause of a SELECT statement to perform a similar search and return a relevance ranking. The CONTAINSTABLE function returns a table containing rows matching the specified selection criteria. Each row of the table contains a Key column and a Rank column. The Key column contains the fulltext key, and the Rank column contains a relevance ranking.
