SQL Server Full-Text search remove noise words

SQL Server full-text search filters out certain noise words by default.  If you want to include some of these noise words in your full text search, you will need to do the following.

  1. Locate the noise file for your language.  For English, this would be noiseENU.txt and is located in the SQL Server install directory.  For example C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData (Your location may vary).  You can also use the following query to help determine the location:

    select * from sys.fulltext_catalogs

  2. Edit this file.  You can add or remove noise words/numbers, etc. from this file.

  3. The last thing you must do is rebuild the catalog (*Note, if you have a large catalog, do this off-hours as it will impact performance):

    ALTER FULLTEXT CATALOG YourCatalogNameHere
    REBUILD WITH ACCENT_SENSITIVITY = OFF
Now, the next time you run a full-text search, your changes will be factored in.

Add comment