Why or How does FREETEXTTABLE give a rank value higher then others.

Question

There is a store procedure that uses FREETEXTTABLE twice on two tables and then merges the results and returns the top 50.

The problem is if I do a search on "Women of Brewster", the results returns "Confession of an ex doofus motha" with a rank of 143 from table A and second "Women of Brewster Place" with a rank of 102 from table B.

Is this because of the count? (Table A return results total is 2399. Table B return results total is 3445.)

Answer 1

The short answer:

Freetext ranking is based on the OKAPI BM25 ranking formula. Each term in the query is ranked, and the values are summed. Freetext queries will add words to the query via inflectional generation (stemmed forms of the original query terms); these words are treated as separate terms with no special weighting or relationship with the words from which they were generated. Synonyms generated from the Thesaurus feature are treated as separate, equally weighted terms.

The much longer, and far more complicated answer can be found on Microsoft's site, of course. For advanced mathematics, click here.

Answer 2

1) The noise file was limited to a few characters, meaning that the word "of" is now consider important.

2) The two tables results (count) do matter, since the smaller table will most likely be given a better weight value. This will skew the rank to be higher in a smaller table.

Josef's link to MSDN was great at figuring out how it computes the rank value.