FREETEXTTABLE Returning Incorrect Rank Result

Question

Hi there,

I'm trying to fix a bug with a site search function and have isolated it down to an issue with the FREETEXTTABLE function.

I have a the following query:

SELECT * FROM dbo.SiteContentForSearch INNER JOIN FREETEXTTABLE(SiteContentForSearch, sSearchText, 'NFC' ) AS SearchResultTable 


ON dbo.SiteContentForSearch.liSearchID = SearchResultTable.[Key]

This is returning a rank of more than 0 for many of the rows which have 'NFC' present in the sSearchText column. However there is (at least) one row which it is returning a rank of o. It has the following text which as you can see contains NFC present a couple of times. Any idea's?

Payments and Ticketing Contactless smart cardsContactless smart card technology is being employed successfully in many areas. For instance:Retailers are using contactless smart card payments to increase basket size and cut queuesTransport operators are using contactless smart cards (ITSO, Oyster etc) to streamline backend processes and improve the customer experienceEvent and venue operators are using RFID wristbands and bracelets for effective and secure ticketing and access controlNear field communication (NFC) and mobile paymentsWhen it comes to driving the market for mobile payments and ticketing, mobile phone technology near field communications (NFC) could be the answer. And as NFC does not need special point-of-sale contactless readers, NFC can be seamlessly integrated with the existing contactless infrastructure. But with mass market adoption a few years away, what should you do now?Working with contactless smart cards, NFC and mobile paymentsWe have been helping clients improve their customers' experience using RFID and mobile phone technologies for a long time. As a result, we can integrate all major payment schemes. We can help you:Understand your customers' user journey to get the core applications right such as payments and ticketingInnovate, for instance using RFID tags or NFC with digital signage to personalise real-time customer offersBuild and test NFC applications within our NFC framework

Cheers

Steve

Answer 1

I'm currently having some FreeTextTable issues myself and the marked answer to this question:

http://stackoverflow.com/questions/210094/why-or-how-does-freetexttable-give-a-rank-value-higher-then-others

which contains this link

http://msdn.microsoft.com/en-us/library/ms345119%28SQL.90%29.aspx

explains how the FreeTextTable does its thing in maths. Particularly with reference to the OKAPI BM25 ranking formula.

Underneath the maths part is a section on issues with the ranking formula. Obviously its been 16 months since you asked this question but maybe this answer will help someone else with a similar problem. If you step through the problems and meta-problems with implementing the alogrithm you'll probably see where it could possibly be falling over.

HTH (someone...)

EDIT: Also the answer to this question:

http://stackoverflow.com/questions/3097166/freetexttable-always-has-a-rank-of-0

includes this link

http://msdn.microsoft.com/en-us/library/ms142524.aspx

Which is a more approachable summary of the info about how the ranking formula works that is contained in the previous link.