Sql-server – Why or How does FREETEXTTABLE give a rank value higher than others


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.)

Best Solution

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.

Related Question