sql server full text search: Containstable weighting question -- sql server full text search

Containstable weighting question

geek-y-guy
9/4/2006 10:26:56 AM

sql server full text search:

I have an FT Index on product names, and I'm trying to devalue common words
that would appear in many names.

I'm using a query like:

select p.product_name, k.rank from products p inner join

containstable(products,product_name,'isabout ("Bob&Ray" weight(.9),"video"
weight
(.1))') as k
on p.product_id = k.[key]
order by k.rank desc

When I run that query, the first result is

Jam Video with a rank of 952

followed by

Bob & Ray 2-volume video box set (256)
Bob & Ray first season (252)
Bob & Ray second season (252)
Crimestoppers Season 1 video (11)

etc.

Obviously I would like "Bob & Ray" to have a higher ranking that "Jam Video"
in the ranking. Is the ranking based on the number of words in the field, as
well as the matches against those words?

Is there any way to structure the query to devalue the word "video" so that
"Bob & Ray" would have the highest relevance?

Re: Containstable weighting question

Hilary Cotter
9/5/2006 12:00:00 AM

Ranking is based on relative rarity of a word. So if a word occurs rarely in
your documents it has higher resolving power and will be ranked higher than
words which occur relatively rarely. Likewise a word which occurs more
densely in a document will be weighted lower than a word which occurs less
often. So its like a bell curve with greatest contribution to overall weight
being around 1000 words IIRC.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

[quoted text, click to view]