You are a database developer on an instance of SQL Server 2008. Your database contains a DisciplinaryAction table created with the following statement:
CREATE TABLE DisciplinaryAction (
ActionID int,
EmpID int,
ReportingBy int,
ActionDate datetime,
ActionDetails varchar(4000),
CONSTRAINT PK_ActionID PRIMARY KEY CLUSTERED (ActionID)
);
You have created a full-text catalog and full-text index to enable full-text searches on the ActionDetails column.
You want to identify all employees who have a disciplinary action relating to attendance issues. You also want the results to be ranked according to their relevance.
Which construct should you use?
A. the CONTAINS predicate including the NEAR clause
B. the CONTAINSTABLE function including the FORMS OF INFLECTIONAL clause
C. the FREETEXT predicate
D. the FREETEXTTABLE function
Correct Answer: D
Explanation/Reference:
The FREETEXTTABLE function can be used in the FROM clause of a query to search for word or phrase’s meaning instead of the exact word or phrase. The FREETEXTTABLE 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 full-text key, and the Rank column contains a relevance ranking. Thesaurus files are used with queries that use the FREETEXT predicate and the FREETEXTTABLE function, and with queries that use the CONTAINS predicate and the CONTAINSTABLE function with the FORMS OF THESAURUS clause. You can customize thesaurus files to include desired synonyms.
You should not use the CONTAINS predicate including a NEAR clause. 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. Using the NEAR clause searches for words or phrases that are near other words or phrases. In addition, the CONTAINS predicate does not return a relevance ranking as required in this scenario.
You should not use the CONTAINSTABLE function including the FORMS OF INFLECTIONAL clause. Using the FORMS OF INFLECTIONAL clause with the CONTAINS predicate or the CONTAINSTABLE function searches for a word and any other forms of that word, but does not search for meaning based on synonyms in the thesaurus.
You should not use the FREETEXT predicate. Although you can use the FREETEXT predicate to search for matches based on meaning rather than the exact words, the FREETEXT predicate does not return a relevance ranking as required in this scenario. The FREETEXT predicate is used in a WHERE clause to search full-text enabled character-based columns for values that match the meaning and not the exact wording. 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 full-text 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. 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.
