Optionally provide private feedback to help us improve this article...

Thank you for your feedback!


ContainsTable v FreeTextTable

There are four principal T-SQL functions which allow one to interact with your Full-Text indices: CONTAINS, FREETEXT, CONTAINSTABLE and FREETEXTTABLE. When you enable full-text search within either InstantForum or InstantKB you can choose from two of these options either CONTAINSTABLE or FREETEXTTABLE.

The key difference between the ContainsTable & FreeTextTable search options is the use of inflection. The FreeTextTable option has inflection enabled by default whilst the ContainsTable option does not have inflection enabled.

Inflection allows SQL server to find all nouns & verbs of words you search for using an internal thesaurus. This thesaurus is determined by the language you select for your full-text indexes.

For example with FreeTextTable enabled if you search for "drove" results with the terms driving, driver, driven, drive etc will be returned. Whilst if you have ContainsTable enable only results with the original search term of "drove" would be returned.

Because of this inflection FreeTextTable is slightly more expensive than ContainsTable however FreeTextTable can return a broader set of search results for users to explore.

ContainsTable

Offers precise or fuzzy (less precise) matches to single words and / or phrases.

FreeTextTable

Matches the meaning, but not necessarily the exact wording of the search terms.

How Ranking Is Calculated

The ranking associated with search results is determined using a number of factors depending on the search type you prefer.

ContainsTable ranking uses the following algorithm:

StatisticalWeight = Log2( ( 2 + IndexedRowCount ) / KeyRowCount )
Rank = min( MaxQueryRank, HitCount * 16 * StatisticalWeight / MaxOccurrence )

Phrase matches are ranked just like individual keys except that KeyRowCount (the number of rows containing the phrase) is estimated and can be inaccurate and higher than the actual number.

FreeText ranking is based on the OKAPI BM25 ranking formula. FREETEXTTABLE queries will add words to the query via inflectional generation (inflected forms of the original query words); these words are treated as separate words with no special relationship to the words from which they were generated. Synonyms generated from the Thesaurus feature are treated as separate, equally weighted terms. Each word in the query contributes to the rank.