SQL Server: Table Instance

A Developer creates the following table: 

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));

DBA/Developer 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;
 
DBA/Developer wants to display the names of all events in the EventDetails table. Modify the thesaurus file to include all words with the same meaning as introductory. Reload the thesaurus file using the sys.sp_fulltext_load_thesaurus_file system stored procedure. Create a query that uses the CONTAINS predicate with the FORMS OF THESAURUS clause. 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_path\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\FTDATA\ folder. These default thesaurus files have XML elements commented out initially, but DBA/Developer can modify them as required. In this scenario, DBA/Developer 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, DBA/Developer should first modify the thesaurus file to include the desired synonyms for the word introductory. For example, DBA/Developer 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>_{InternetExplorer}_IE_IE5</expansion><replacement><pat>
NT5</pat><pat>W2K</pat>_Windows2000
</replacement><expansion>_introductory_beginning</expansion></ thesaurus></XML>

After modifying the thesaurus file, DBA/Developer should reload it using the sys.sp_fulltext_load_thesaurus_file system stored procedure for the changes to take effect.
For example, the following statement would reload the English thesaurus file:

EXEC sys.sp_fulltext_load_thesaurus_file 1033;

Finally, DBA/Developer 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)');

DBA/Developer 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. DBA/Developer should not rebuild the full-text index on the EventDetails table. In this scenario, DBA/Developer 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.DBA/Developer 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.DBA/Developer should not create a query that uses the CONTAINS TABLE 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 CONTAINS TABLE 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
CONTAINS TABLE (EventDetails, Description, 'ISABOUT (novice weight(.8), beginning weight(.4),introductory weight (.1))')
AS k
ON e.EventID = k.[KEY]
ORDER BY k.[RANK] DESC;