SQL ContainsTable returns low Rank for exact match

Question

For a product named "Red bike with upgraded racing frame", a containstable search returns the following:

containstable(tbl, col, '"red bike"') Rank: 100
containstable(tbl, col, '"red bike with upgraded racing frame"') Rank: 255

My questions are:

• Why does the second search return 255 and not 1000 - should I be using something other than containstable?
• Is there a better way to achieve my end result?

The end result I'm looking for is a fast way to search product names. I opted to use containstable because my testing shows it's significantly faster than doing 'tbl.col.field = "" or tbl.col.field like '%%'', and also because I would like to be able to do full text searches in addition to partial and exact match searches.

The product name results are being aggregated with other results - for example, when a user runs a search, the product name and product description are both searched and the results are combined. For this reason, I would like exact product name matches to rank highest, but currently, what's happening is that sometimes description full text matches will outrank an exact product name match.

I am trying to avoid writing multiple queries for each search type and hard-coding rank values.

Answer 1

I don't believe there is anyway to specify a weighted value for a column with CONTAINSTABLE full-text search results like you're wanting.

You could implement your own custom 'ranking' system by using FREETEXTTABLE on the individual columns, then UNION the results together with a custom weigned value for the rank result giving 'Product Name' a much higher precidence as a work-around.