SQL Server Full Text Search - Weighting Certain Columns Over Others

Question

If I have the following full text search query:

SELECT *
FROM dbo.Product
   INNER JOIN CONTAINSTABLE(Product, (Name, Description, ProductType), 'model') ct
      ON ct.[Key] = Product.ProductID

Is it possible to weigh the columns that are being searched?

For example, I care more about the word model appearing in the Name column than I do the Description or ProductType columns.

Of course if the word is in all 3 columns then I would expect it to rank higher than if it was just in the name column. Is there any way to have a row rank higher if it just appears in Name vs just in Description/ProductType?

Answer 1

You can do something like the following query. Here, WeightedRank is computed by multiplying the rank of the individual matches. NOTE: unfortunately I don't have Northwind installed so I couldn't test this, so look at it more like pseudocode and let me know if it doesn't work.

declare @searchTerm varchar(50) = 'model';

 SELECT 100 * coalesce(ct1.RANK, 0) +
        10 * coalesce(ct2.RANK, 0) +
        1 * coalesce(ct3.RANK, 0) as WeightedRank,
        *
   FROM dbo.Product
            LEFT JOIN
        CONTAINSTABLE(Product, Name, @searchTerm) ct1 ON ct1.[Key] = Product.ProductID
            LEFT JOIN
        CONTAINSTABLE(Product, Description, @searchTerm) ct2 ON ct2.[Key] = Product.ProductID
            LEFT JOIN
        CONTAINSTABLE(Product, ProductType, @searchTerm) ct3 ON ct3.[Key] = Product.ProductID 
 order by WeightedRank desc