sql-serverfull-text-searchfreetexttable

Full text searching scores across multiple columns


I am using full text searching on a SQL Server database to return results from multiple tables. The simplest situation would be searching a persons name fields and a description field. The code I use to do this looks like:

select t.ProjectID as ProjectID, sum(t.rnk) as weightRank
from
(
 select KEY_TBL.RANK * 1.0 as rnk, FT_TBL.ProjectID as ProjectID
 FROM Projects as FT_TBL 
 INNER JOIN FREETEXTTABLE(Projects, Description,  @SearchText) AS KEY_TBL 
   ON FT_TBL.ProjectID=KEY_TBL.[KEY]

     union all

 select KEY_TBL.RANK * 50 as rnk, FT_TBL.ProjectID as ProjectID
 FROM Projects as FT_TBL 
 ... <-- complex unimportant join
     INNER JOIN People as p on pp.PersonID = p.PersonID
 INNER JOIN FREETEXTTABLE(People, (FirstName, LastName), @SearchText) AS KEY_TBL 
    ON p.PersonID=KEY_TBL.[KEY]
 )
group by ProjectID

As is (hopefully) clear above, I am trying to weight heavily on matches of a person's name over matches in a project description field. If I do a search for something like 'john' all projects with a person named john on it will be heavily weighted (as expected). The issue I am having is on searches where someone provides a full name like 'john smith'. In this case the match is much less strong on name as (I presume) only half the search terms are matching in each of the firstname / lastname columns. In many cases this means someone with an exact match of the name entered will not necessarily be returned near the top of the search results.

I have been able to correct this by searching each of the firstname / lastname fields separately and adding their scores together so my new query looks like:

select t.ProjectID as ProjectID, sum(t.rnk) as weightRank
from
(
 select KEY_TBL.RANK * 1.0 as rnk, FT_TBL.ProjectID as ProjectID
 FROM Projects as FT_TBL 
 INNER JOIN FREETEXTTABLE(Projects, Description,  @SearchText) AS KEY_TBL 
   ON FT_TBL.ProjectID=KEY_TBL.[KEY]

     union all

 select KEY_TBL.RANK * 50 as rnk, FT_TBL.ProjectID as ProjectID
 FROM Projects as FT_TBL 
 ... <-- complex unimportant join
     INNER JOIN People as p on pp.PersonID = p.PersonID
 INNER JOIN FREETEXTTABLE(People, (FirstName), @SearchText) AS KEY_TBL 
    ON p.PersonID=KEY_TBL.[KEY]

     union all

 select KEY_TBL.RANK * 50 as rnk, FT_TBL.ProjectID as ProjectID
 FROM Projects as FT_TBL 
 ... <-- complex unimportant join
     INNER JOIN People as p on pp.PersonID = p.PersonID
 INNER JOIN FREETEXTTABLE(People, (LastName), @SearchText) AS KEY_TBL 
    ON p.PersonID=KEY_TBL.[KEY]
 )
group by ProjectID

My question:

Is this the approach I should be taking, or is there some way to have the full text searching operate on a list of columns as though it were a blob of text: i.e. treat the firstname and lastname columns as a single name column, resulting in a higher scoring match for strings including both the persons first and last name?


Solution

  • I have recently run into this and have used a computed column to concatenate the required columns together into one string and then have the full text index on that column.

    I have achieved the weighting by duplicating the weighted fields in the computed column.

    i.e. last name appears 3 times and first name once.

    ALTER TABLE dbo.person ADD
    PrimarySearchColumn AS 
    COALESCE(NULLIF(forename,'') + ' ' + forename + ' ', '') +
    COALESCE(NULLIF(surname,'')  + ' ' + surname  + ' ' + surname  + ' ', '') PERSISTED
    

    You must make sure you use the persisted keyword so that the column isnt computed on each read.