SQL Server Full Text Indexing



So this is more of a mental note to self, based on the summary of a lesson I just completed on SQL Server Full Text Indexing. In summary, to create Full Text Indexes, we need to keep the following in mind:

  • Before a full text index is created, you need to make a full text catalog that is mapped to a filegroup first.
  • Full text indexes can be created on columns with CHAR, VARCHAR, XML and VARBINARY.
  • When using  a full text index with a VARBINARY(MAX) column, you need to specify the column for the COLUMN TYPE parameter so that the Full Text Index Engine knows which filter to load for parsing the data. (for example a filter for Word Documents, Excel, HTML etc…)
  • The “LANGUAGE” setting specifies which “Word Breaker” and “Stemmer” SQL Server loads to tokenise and build inflectional forms for the index.
  • Word breakers can be used for different languages that have a close relationship with fairly acceptable results in most cases, but stemmers are language specific and can only be used on the exact language data you are dealing with.
  • CHANGE_TRACKING controls whether SQL Server tracks changes to underlying data columns – Automatically , manually or is off.



This is my interpretation of the summary provided at the end of lesson 1 in Chapter 5 of the Microsoft MCTS Exam 70-432 authored by Mike Hotek.


Full Text Predicates that can be used:

  • CONTAINS
  • CONTAINSTABLE
  • FREETEXT
  • FREETEXTTABLE


A Thesaurus file can be used to configure synonyms for search arguments. A stop list contains a list of words that you want to exclude from your full text index or search arguments.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.