SQL Server Full Text Search Noise Words and Thesaurus Configurations

By: Jeremy Kadlec   |   Read Comments   |   Related Tips: More > Full Text Search

Problem
I have heard that Full Text Search uses a noise words to eliminate meaningless words in searches.  I have also heard that a thesaurus is used, but I am not exactly sure how.  Can you provide some details related to how both of these technologies are used in Full Text Search?  I am interested in more of a background on these specific Full Text Search technologies as well as where the files are located and how I can update them.

Solution
Although the noise words and thesaurus have many similarities in terms of the implementation with Full Text Search, let's break apart each technology in order to explain them with more clarity.  Keep in mind that the information in this tip relates to SQL Server 2005, although SQL Server 2000 and 7.0 have some of the same properties.  Stay tuned for information on Full Text Search in SQL Server 2008 because some of the rules have changed.

Noise Words

• Purpose: The noise words file has been established by Microsoft on a per language basis to determine the words which do not add value to the search.  For example, in the English language (United  States) noise words would include: because, been, before, being, between, both, but, etc.  In the Noiseenu.txt file (English US) over 100 noise words are setup by default and this file is used by default when querying with full text search.
• Language Support: 20 different languages are supported from Chinese (Noisechs.txt) to Turkish (Noisetrk.txt).
• Windows Directory: $SQL_Server_Install_Path\Microsoft SQL Server\MSSQL.1\MSSQL\FTDATA\
• Modifications: New noise words can be added to the file.  As we have had to make changes, we add them after the last entry which is 'your' by default.
• Additional Information
  • If you make a change to a noise word file, it is necessary to repopulate all of the full text catalogs before any of the new noise words will be used.

Thesaurus

• Purpose: Ability to define synonyms and use the synonyms in full text searches.  Specifically with the FORMSOF THESAURUS parameter with the CONTAINS or CONTAINSTABLE commands the thesaurus is used to determine synonyms of the search terms.  The same is true with the FREETEXT and FREETEXTTABLE commands.  With these commands the thesaurus is used to identify expressions or replacements for the searched terms.
• Language Support: The thesaurus files are in XML format with a global file (tsGLOBAL.xml) and then 18 language specific files.  By default all of the files have sample XML that is commented out.  So by default no synonyms are setup when a full text search is issued.
• Windows Directory: $SQL_Server_Install_Path\Microsoft SQL Server\MSSQL.1\MSSQL\FTDATA\
• Modifications: After reviewing the XML format, two types of thesaurus entries exist..  First are the expansion set and second are the replacement set.  The expansion set which can be considered synonyms or substitute terms.  The replacement set indicates that one string is replaced with another.
• Additional Information:
  • All thesaurus files should be saved as Unicode.
  • The Full Text Search Engine Windows Service must be restarted to begin to use the new thesaurus files.

Next Steps

• As you progress with your Full Text Search implementation, be sure to validate the Noise Words and Thesaurus are properly tuned to your searches.  Keep in mind they are used by default, but the Thesaurus has no data by default.
• Since Full Text Search supports numerous languages, be sure to modify the appropriate file based on your language.
• Pay special attention to changing either the noise words or the thesaurus.  Depending on the change, downtime could be needed, so just keep that in mind.
• Check out these additional resources from Microsoft:
  • Thesaurus
  • Configuring Thesaurus Files
  • How to: Edit a Thesaurus File
  • Noise Words
• Check out these Full Text Search tips:
  • Execute Complex Search Queries with Ranked Results in SQL Server
  • Full Text Search Querying Alternatives in SQL Server
  • Making the case for SQL Server Full Text Search
• Check out the Full Text Search category

Last Update: 5/5/2008

About the author

Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009.

View all my tips

We Recommend

• More Database Developer Tips...

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis! Get your SQL Server database under version control now! Find out why... What grade do you think your SQL Servers get? Find out with Edgewood's Health Check consulting services. Spring Clean Your Data - Clean your global contact data with Melissa Data tools for SSIS. Download a free trial! Optimizing SQL Server performance can be a daunting task. Or is it?