Full text search exact match check SQL Server

Question

I have a Full-text search on view, the view returns all approved businesses & meta data about said business.

I implemented a stored procedure to utilize the full-text search with the following code:

CREATE PROCEDURE [dbo].[Search]
@SearchTerm varchar(8000),
@CurrentPage int = 1, 
@PageSize int = 100

AS
BEGIN
    DECLARE @NearPredicate varchar(8000), 
            @AndPredicate varchar(8000), 
            @TotalRecords int

SELECT 
    @NearPredicate = COALESCE(@NearPredicate + ' NEAR ', '') + Data
FROM Split(@SearchTerm, ' ') 
    LEFT JOIN sys.fulltext_system_stopwords ON Data = stopword
WHERE stopword IS NULL

SET @AndPredicate = REPLACE(@NearPredicate, 'NEAR', 'AND')
SET @NearPredicate = '(' + @NearPredicate + ')'

SET @TotalRecords  = (
    SELECT 
        COUNT(*) 
    FROM 
        vwApprovedBusiness 
    WHERE FREETEXT(*, @AndPredicate ) 
    )

SELECT *,
    ct.Rank,
    @TotalRecords AS TotalRecords
FROM 
    vwApprovedBusiness a
        INNER JOIN FREETEXTTABLE (vwApprovedBusiness, *, @NearPredicate ) AS ct ON a.MyBusinessID = ct.[KEY]
ORDER BY 
    ct.RANK DESC
OFFSET (@CurrentPage - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS only

END

If I search for a Business Name e.g. One Guy Transport which is an approved business, it only shows up in my search results around 6th or 7th and not the first result as expected. Any assistance with this would be greatly appreciated!

Answer 1

I suggest to convert Full-Text Search calls from FREETEXT / FREETEXTTABLE to CONTAINS / CONTAINSTABLE because I think FREETEXT is not appropriate when you search for say business name which is the exact phrase. As per MSDN docs Query with Full-Text Search:

CONTAINS and FREETEXT are useful for different kind of matches, as follows:

• Use CONTAINS (or CONTAINSTABLE) for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches.

• Use FREETEXT (or FREETEXTTABLE) for matching the meaning, but not the exact wording, of specified words, phrases or sentences (the freetext string). Matches are generated if any term or form of any term is found in the full-text index of a specified column.

So when searching for Business Name you definitely want to have precise matching therefore CONTAINS must be used.

If CONTAINS/CONTAINSTABLE produces little results when searching inside business meta data you can split the search type in UI and use CONTAINS when search is by name and FREETEXT when search is by business description/meta data. I personally think CONTAINS will be ok in both cases.

Another note, in your query you have a separate FTS call to get the @TotalRecords count but you can embed that right into main search call:

SELECT *,
    ct.Rank,
    -- @TotalRecords AS TotalRecords
    COUNT(*) over () as TotalRecords
FROM 
    vwApprovedBusiness a
        INNER JOIN FREETEXTTABLE (vwApprovedBusiness, *, @NearPredicate ) AS ct ON a.MyBusinessID = ct.[KEY]

HTH