The thesaurus files that are released with SQL Server 2008 all contain the following XML code:

<XML ID="Microsoft Search Thesaurus">

<!--  Commented out

    <thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
        <expansion>
            <sub>Internet Explorer</sub>
            <sub>IE</sub>
            <sub>IE5</sub>
        </expansion>
        <replacement>
            <pat>NT5</pat>
            <pat>W2K</pat>
            <sub>Windows 2000</sub>
        </replacement>
        <expansion>
            <sub>run</sub>
            <sub>jog</sub>
        </expansion>
    </thesaurus>
-->
</XML>

[Top]

The default location of the thesaurus files is:

SQL_Server_install_path\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\FTDATA\

This default location contains the following files:

• Language-specific thesaurus files
  During setup, empty thesaurus files are installed in the above location. A separate file is provided for each supported language. A system administrator can customize these files.
  The default file names of the thesaurus files use following format:
  ‘ts’ + <three-letter language-abbreviation> + '.xml'
  The name of the thesaurus file for a given language is specified in the registry in the following value HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance-name>\MSSearch\<language-abbrev>.
  
• The global thesaurus file
  An empty global thesaurus file, tsGlobal.xml.
  

You can change the location and names of a thesaurus file by changing its registry key. For each language, the location of the thesaurus file is specified in the following value in the registry:

HKLM/SOFTWARE/Microsoft/Microsoft SQL Server/<instance name>/MSSearch/Language/<language-abbreviation>/TsaurusFile

The global thesaurus file corresponds to the Neutral language with LCID 0. This value can be changed by administrators only.

[Top]

A thesaurus query uses both a language-specific thesaurus and the global thesaurus. First, the query looks up the language-specific file and loads it for processing (unless it is already loaded). The query is expanded to include the language-specific synonyms specified by the expansion set and replacement set rules in the thesaurus file. These steps are then repeated for the global thesaurus. However, if a term is already part of a match in the language specific thesaurus file, the term is ineligible for matching in the global thesaurus.

[Top]

Each thesaurus file defines an XML container whose ID is Microsoft Search Thesaurus, and a comment, <!-- … -->, that contains a sample thesaurus. The thesaurus is defined in a <thesaurus> element that contains samples of the child elements that define the diacritics setting, expansion sets, and replacement sets, as follows:

• XML Structure of the Diacritical Setting
  The diacritics setting of a thesaurus is specified in a single <diacritics_sensitive> element. This element contains an integer value that controls accent sensitivity, as follows:
  

  Diacritics Setting	Value	XML
  Accent insensitive	0	<diacritics_sensitive>0</diacritics_sensitive>
  Accent sensitive	1	<diacritics_sensitive>1</diacritics_sensitive>

  Note:
  This setting can only be applied one time in the file, and it applies to all search patterns in the file. This setting cannot be specified for individual patterns.

• XML Structure of an Expansion Set
  Each expansion set is enclosed within an <expansion> element. Within this element, you specify one or more substitutions in a <sub> element. In the expansion set, you can specify a group of substitutions that are synonyms of each other.
  For example, you can edit the expansion section to treat the substitutions "writer", "author", and "journalist" as synonyms. full-text search queries that contain matches in one substitution are expanded to include all other substitutions specified in the expansion set. Therefore, in the preceding example, when you issue a FORMS OF THESAURUS or a FREETEXT query for the word "author", full-text search also returns search results containing the words "writer" and "journalist".
  This is what the expansion set section would look like for the above example:
  
   <expansion>
           <sub>writer</sub>
           <sub>author</sub>
           <sub>journalist</sub>
   </expansion>

• XML Structure of a Replacement Set
  Each replacement set is enclosed within a <replacement> element. Within this element you can specify one or more patterns in a <pat> element and zero or more substitutions in <sub> elements, one per synonym. You can specify a pattern to be replaced by a substitution set. Patterns and substitutions can contain a word, or a sequence of words. If there is no substitution specified for a pattern, it has the effect of removing the pattern from the user query.
  For example, suppose you want queries for "W2K", the pattern, to be replaced by "Windows 2000" or "XP", the substitutions. If you run a full-text query for "W2K", full-text search only returns search results containing "Windows 2000" or "XP". It does not return results containing "W2K". This is because the pattern "W2K" has been "replaced" by the patterns "Windows 2000" and "XP".
  This is what the replacement set section would look like for the above example:
  
   <replacement>
           <pat>W2K</pat>
           <sub>Windows 2000</sub>
           <sub>XP</sub>
   </replacement>

  If you have two replacement sets with similar patterns being matched, the longer of the two takes precedence. For example, if you run a FORMS OF THESAURUS query for "Internet Explorer online community" and you have the following replacement sets, the "Internet Explorer" replacement set takes precedence over the "Internet" replacement set. The query will therefore be processed as "IE online community" or "IE 5 online community".
  
  <replacement>
           <pat>Internet</pat>
           <sub>intranet</sub>
  </replacement>

  and
  
  <replacement>
           <pat>Internet Explorer</pat>
           <sub>IE</sub>
           <sub>IE 5</sub>
  </replacement>

[Top]

To edit a thesaurus file

• How to: Edit a Thesaurus File (Full-Text Search)
  

To load an updated thesaurus file

• sp_fulltext_load_thesaurus_file (Transact-SQL)
  

To view the tokenization result of a word breaker, thesaurus, and stoplist combination

• sys.dm_fts_parser (Transact-SQL)
  

Tasks

Concepts

Other Resources

Help and Information