SQL Server Full Text Search - Weighting Certain Columns Over Others

user_143919 0
Viewed 3487

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?

full-text-search sql-server-2008 sql-server
3 Answers

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;
user_3514260
Related