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?
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.