FREETEXTTABLE always has a rank of 0

Question

I'm using SQLServer 2008 and if I perform the following query:

SELECT 
  *
FROM
  FREETEXTTABLE(SomeTable, Name, 'a name that I know exists')

I get the rows back that I would expect, but the rank is always 0.

Searching for a solution to this problem, I found this question on the Microsoft ASP.NET forum, and sure enough if I add:

ALTER FULLTEXT CATALOG MyCatalog REBUILD

I start to get a rank - but only temporarily.

I don't want to have to rebuild my catalog every time I do a search especially when I have lots of data in my database and if I add it to my Sproc directly before the query, my query returns no results anyway, presumably because the catalog has finished being rebuilt. There seem to be other people having this and similar problems but I have been unable to find a solution. Any ideas?

Answer 1

The rank is relative to the other results returned in the query and is therefore only useful for sorting on relevance from the returned values. There is detailed information on the ranking method.

Answer 2

I am running in the same issue, and currently accepted answer is not a solution for me.

Yes the ranking is done as said by this answer, but it is no way a reason for having inconsistent results when it has been some times since the last catalog rebuild. Ranking should not dramatically change upon rebuild and even less some minutes after rebuild...

For me, there is a bug in freetexttable ranking. (Bug which does not affect containstable ranking: I have checked it myself with my own buggy catalog, and it is also written on this Microsoft forum post.)

From this other Microsoft forum post it seems this bug occurs only in catalog having only very few rows indexed. Adding data to the catalog causes the bug to disappear.

So here is my answer, taken from Pavel Valenta on yet another Microsoft forum post:

If your real catalog is not going to have more than a few hundreds rows indexed, add some dummy table to your catalog in order to have more rows indexed.

This will not pollute your results due to the way queries are build. Yes this seems quite a strange fix. But that is the only one that had solved the trouble for me.

One final note: I had this problem with sql 2005 sp4, not tested with 2008. (The question is for 2008.)