Can i say that one column is more important then another in a full-text search?

Question

I use Full-text indexing in SQL Server 2008.

I can't seem to find an answer to this question. Say that i have a full-text index on a table with the columns "Name" and "Description". I want to make the "Name" column much more important then the "Description" column.

So if you search for "Internet" the result with the name "Internet" will always come on top no matter how many occurences there is for internet in the description. It must be possible right?

Answer 1

You could add a computed column to your select list using Case where you have assign a value to that column based on the occurence of the search term in the columns of interest and then order by that column. So for example something like:

SELECT (CASE WHEN Name LIKE "%searchTerm%" THEN 10
            WHEN Description LIKE "%searchTerm%" THEN 5
            ELSE 0 END CASE) AS computedValue
FROM myTable
ORDER BY computedValue DESC

Answer 2

As I know there is no T-SQL syntax to specify rate of column. But you can get this with trick of select. (assuming using FREETEXTTABLE, but you can rewrite with other FTS constructions)

SELECT CASE 
            WHEN ISNULL(hi_prior.RANK) low_prior.KEY
            ELSE hi_prior.RANK END --hi prior is selected whenever is possible
FROM
  FREETEXTTABLE (Table1, (HiPriorColumn), @query) hi_prior
FULL OUTER JOIN
  FREETEXTTABLE (Table1, (LowPriorColumn), @query) low_prior
ON (hi_prior.KEY = low_prior_KEY)

In case you need both result - use UNION, but multiply lowest on some rate: low_prior.RANK * 0.7

Answer 3

I found this article just now.

http://www.goodercode.com/wp/?p=10

In my code it became like this. Works exactly as i want to :) Thanks for you help!

SELECT dbo.Item.Name, dbo.Item.[Description],NAME_SRCH.RANK AS rank1, DESC_SRCH.RANK AS rank2
FROM dbo.Item LEFT OUTER JOIN
FREETEXTTABLE(dbo.Item, name, 'Firefox') NAME_SRCH ON
dbo.Item.ItemId = NAME_SRCH.[KEY] LEFT OUTER JOIN
FREETEXTTABLE(dbo.Item, *, 'Firefox') DESC_SRCH ON
dbo.Item.ItemId = DESC_SRCH.[KEY]
ORDER BY rank1 DESC, rank2 DESC