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.