You are a database developer on an instance of SQL Server 2008. You maintain a table, named EventDetails, containing information on scheduled events defined as follows:
CREATE TABLE EventDetails (
EventID int IDENTITY(1,1),
EventName varchar(50),
Description varchar(400),
FacilitatorID int,
EventDt datetime,
CONSTRAINT PK_EventID PRIMARY KEY CLUSTERED (EventID)
);
You execute the following Transact-SQL statement:
CREATE FULLTEXT CATALOG EventFTCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.EventDetails (Description) KEY INDEX PK_EventID ON EventFTCatalog WITH CHANGE_TRACKING AUTO;
You want to display the names of all events in the EventDetails table that have a description containing either the word introductory or a word with the same meaning as introductory.
Which three actions should you take? (Choose three. Each correct answer represents part of the solution.)
A. Create a query that uses the LIKE operator.
B. Modify the thesaurus file to include all words with the same meaning as introductory.
C. Rebuild the full-text index on the EventDetails table.
D. Enable full-text support for the database.
E. Create a query that uses the CONTAINS predicate with the FORMS OF THESAURUS clause.
F. Reload the thesaurus file using the sys.sp_fulltext_load_thesaurus_file system stored procedure.
G. Create a query that uses the CONTAINSTABLE function with the ISABOUT clause.
Correct Answer: BEF
Explanation/Reference:
1. Modify the thesaurus file to include all words with the same meaning as introductory.
SQL Server 2008 has default thesaurus files that can be used with full-text searches to search for synonyms. These thesaurus files are XML files that can be modified to contain the desired synonyms or pattern replacements. 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. A global thesaurus file, named tsglobal.xml, and a thesaurus file for each language are located in the SQL_Server_install_pathMicrosoft SQL Server MSSQL10_50.MSSQLSERVERMSSQLFTDATA folder. These default thesaurus files have XML elements commented out initially, but you can modify them as required.
In this scenario, you wanted to display the names of all events in the EventDetails table with a description containing the word introductory or a word with the same meaning as introductory. To accomplish this, you should first modify the thesaurus file to include the desired synonyms for the word introductory. For example, you would modify the thesaurus file as follows to identify the word beginning as a synonym for the word introductory:
<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>
<sub>introductory</sub>
<sub>beginning</sub>
</expansion>
</thesaurus>
</XML>
2. Reload the thesaurus file using the sys.sp_fulltext_load_thesaurus_file system stored procedure.
After modifying the thesaurus file, you should reload it using the sys.sp_fulltext_load_thesaurus_file system stored procedure for the changes to take effect. For example, the following example parses and loads the English thesaurus file:
EXEC sys.sp_fulltext_load_thesaurus_file 1033;
3. Create a query that uses the CONTAINS predicate with the FORMS OF THESAURUS clause.
Finally, you should create a query that uses the modified thesaurus file. The following query uses the CONTAINS predicate with the FORMS OF THESAURUS clause to return the desired results:
SELECT EventName FROM dbo.EventDetails WHERE CONTAINS (Description, ‘FORMSOF(THESAURUS, introductory)’);
You should not create a query that uses the LIKE operator. The LIKE operator is used to identify values that contain a specific character pattern, not synonyms.
You should not rebuild the full-text index on the EventDetails table. In this scenario, you specified the WITH CHANGE_TRACKING AUTO clause with the CREATE FULLTEXT INDEX statement. This configures automatic population of the full-text index. Each time the Description column of the EventDetails table is updated, the changes are also made to the full-text index. Therefore, there is no need to rebuild the full-text index.
You should not enable full-text support for the database. In previous versions of SQL Server, full-text support had to be enabled, but with SQL Server 2008, full-text search support is enabled for all user databases by default.
You should not create a query that uses the CONTAINSTABLE function with the ISABOUT clause. The ISABOUT clause is used to search for words with different weightings, not for word synonyms. For example, the following query uses the CONTAINSTABLE function in the FROM clause of a query to search the EventDetails table for the words novice, beginning, and introductory with a different weighting assigned to each word, and returns the rows with the highest rank first:
SELECT *
FROM EventDetails AS e INNER JOIN CONTAINSTABLE(EventDetails, Description, ‘ISABOUT (novice weight(.8), beginning weight
(.4),introductory weight (.1))’) AS k ON e.EventID = k.[KEY]
ORDER BY k.[RANK] DESC;
