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?

Petry

possible duplicate of SQL Server Weighted Full Text Search – Toenail

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

Shechem

thx Jono! I don't even have SQL Server installed anymore so I can't test. I kind of can't believe this is still useful after all this time, but hey, let me know if it fails and I'll dust off my desktop and try to fix it. – Shechem

Answer 2

Listing 3-25. Sample Column Rank-Multiplier Search of Pro Full-Text Search in SQL Server 2008

SELECT *
FROM (
    SELECT Commentary_ID
        ,SUM([Rank]) AS Rank
    FROM (
        SELECT bc.Commentary_ID
            ,c.[RANK] * 10 AS [Rank]
        FROM FREETEXTTABLE(dbo.Contributor_Birth_Place, *, N'England') c
        INNER JOIN dbo.Contributor_Book cb ON c.[KEY] = cb.Contributor_ID
        INNER JOIN dbo.Book_Commentary bc ON cb.Book_ID = bc.Book_ID

        UNION ALL

        SELECT c.[KEY]
            ,c.[RANK] * 5
        FROM FREETEXTTABLE(dbo.Commentary, Commentary, N'England') c

        UNION ALL

        SELECT ac.[KEY]
            ,ac.[RANK]
        FROM FREETEXTTABLE(dbo.Commentary, Article_Content, N'England') ac
        ) s
    GROUP BY Commentary_ID
    ) s1
INNER JOIN dbo.Commentary c1 ON c1.Commentary_ID = s1.Commentary_ID
ORDER BY [Rank] DESC;

Am

Answer 3

Similar to Henry's solution but simplified, tested and using the details the question provided.

NB: I ran performance tests on both the union and left join styles and found the below to require far less logical reads on the union style below with my datasets YMMV.

 declare @searchTerm varchar(50) = 'model';
 declare @nameWeight int = 100;
 declare @descriptionWeight int = 10;
 declare @productTypeWeight int = 1;
 
 SELECT ranksGroupedByProductID.*, outerProduct.*
FROM   (SELECT [key],
               Sum([rank]) AS WeightedRank 
        FROM   (
                -- Each column that needs to be weighted separately 
                -- should be added here and unioned with the other queries
                SELECT [key],
                       [rank] * @nameWeight as [rank]
                FROM   Containstable(dbo.Product, [Name], @searchTerm) 
                       
                UNION ALL
                
                SELECT [key],
                       [rank] * @descriptionWeight as [rank]
                FROM   Containstable(dbo.Product, [Description], @searchTerm) 
                       
                UNION ALL

                SELECT [key],
                       [rank] * @productTypeWeight as [rank]
                FROM   Containstable(dbo.Product, [ProductType], @searchTerm) 
                       
                ) innerSearch

       -- Grouping by key allows us to sum each ProductID's ranks for all the columns
        GROUP  BY [key]) ranksGroupedByProductID

        -- This join is just to get the full Product table columns 
        -- and is optional if you only need the ordered ProductIDs
       INNER JOIN dbo.Product outerProduct
               ON outerProduct.ProductID = ranksGroupedByProductID.[key]

ORDER  BY WeightedRank DESC;  

Mugwump