CODEWITHSUNDEEP
sql-servert-sqlfull-text-search
user1023321
user1023321

Reputation:

The FREETEXTTABLE on MS SQL 2012 returns strange ranks

I try to find several words in one table but in different fields. Why the records with one corresponing word have the rank higher than the records with two ones? The example:

Record 1

Title: Eddie Murphy

Description: An American stand-up comedian, actor, writer, singer, director, and musician.

Record 2

Title: Tom Cruise

Description: An American film actor and producer. He has won three Golden Globe Awards.

SELECT * FROM FREETEXTTABLE(SubjectContent, (Title, Description), 'tom actor')

returns Recrod 1 with rank 61 and Record 2 with rank 47 despite the record 2 contains both words ('tom' and 'actor') and record 1 contains only one word ('actor'). So the user receives the huge amount of unproper records before the proper one.

Though if I set the search parameter 'tom cruise actor' the request returns the high rank.

My fulltext index:

CREATE FULLTEXT INDEX ON SubjectContent(Title, [Description]) 
KEY INDEX PK_SubjectContent
ON FullTextSearch;

I unsuccessfully tried to change the property 'accent sensitive' and other properties of Full Text Catalog. Thanks for any help.

Upvotes: 1

Views: 758

Answers (1)

venkatp
venkatp

Reputation: 21

Looking at the 2 strings, I see that the second one is a larger document from fulltext point of view. this is because of the sentence separator you have in there. So if you pass these strings to the dm_fts_parser, you will see that the max occurrence of first string is 11 and second one is 21. Fulltext normalizes this document length in buckets of 16, 32, 128, 256 .. etc. so your first document falls in first bucket and the second in second bucket. hence first one has higher rank (inversely proportional to the length of the document). reference of all this is here http://msdn.microsoft.com/en-us/library/cc879245.aspx Thanks Venkat

Upvotes: 2

Related Questions