FTS-Containstable-Ranks !

Question

FTS-Containstable-Ranks !

black.clouds.999

Mr or Mrs. 500

Points: 501
More actions
May 3, 2010 at 4:16 am

#220399

Can someone please help me understand how sql server itself decides the rank value when returning the rows from a containstable in FTS.
Example :- table :- "test" --> 2 columns id and sentence.unique index and fulltext index defined on sentence coln.
id Sentence
-------------------------------------
0I can climb mountain everest
1I ran to the store
2this is a foolish idea
3Mountaining is a good job
11Can you climb mountain everest
30Mounting is a job
33mountaining
Now see the 2 different queries with varied ranks :
Query1
select t.sentence,c.* from test t
join
containstable(dbo.test,sentence,'"mountain"') as c
on t.id=c.
sentence KEY RANK
---------------------------------------------------
I can climb mountain everest 048
Can you climb mountain everest1148
Query2
select t.sentence,c.* from test t
join
containstable(dbo.test,sentence,'"mounting"') as c
on t.id=c.
Result
Sentence KEY RANK
-----------------------------------
Mounting is a job3064
Now I dont understand Why is there a difference of ranks in both of the resultsets and what does that indicate ?
Guys out there ,plz help me understand ! Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

You actually search for an exact match of either the word "mountain" or "mounting". Therefore, you'll get two different result sets. You might want to search for "mount*".

For details, please see BOL, section "CONTAINS predicate".

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

black.clouds.999 Mr or Mrs. 500 Points: 501 More actions · Answer 2

Thanks Imu !

Here is what I got from BOL :

CONTAINSTABLE ranking uses the following algorithm:

StatisticalWeight = Log2( ( 2 + IndexedRowCount ) / KeyRowCount )

Rank = min( MaxQueryRank, HitCount * 16 * StatisticalWeight / MaxOccurrence )

Further details :

http://msdn.microsoft.com/en-us/library/ms142524(v=SQL.100).aspx

LutzM SSC Guru Points: 107049 More actions · Answer 3

black.clouds.999 (5/3/2010)
Thanks Imu !
Here is what I got from BOL :
CONTAINSTABLE ranking uses the following algorithm:
StatisticalWeight = Log2( ( 2 + IndexedRowCount ) / KeyRowCount )
Rank = min( MaxQueryRank, HitCount * 16 * StatisticalWeight / MaxOccurrence )
Further details :
http://msdn.microsoft.com/en-us/library/ms142524(v=SQL.100).aspx

But you're searching for two different terms...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]