Search code examples
sqlsql-serverfull-text-search

SQL Server Fulltext search with ranking on multiple fields

I'm trying to build a very simple search engine for a library.

I have a table [Tales] that contains the following fulltext indexed columns TITLE, AUTHOR and GENRE.

The user can enter a search phrase like this: "american war by david potter"

I split each word building the following query:

SELECT TOP (100) 
    [title],
    [author],
    [genre],
    [TaleID],
    k.[RANK]
FROM 
    [Books].[dbo].[Tales] AS t 
INNER JOIN 
    CONTAINSTABLE([Tales] , *, '"history" OR "america" OR "war" OR "by" OR "david" OR "potter"') AS k ON t.TaleID = k.[key]
ORDER BY 
    k.[RANK] DESC

and the result is the following:

enter image description here

The highlighted line, row No.28, should be the one with the higher ranking, as it contains all the words searched by the user. Instead it seems that the fulltext engine prefer the multiple occurrences of the same word "american".

What's wrong with my query?

EDIT: trying with FREETEXTTABLE, the results are the same. But adding more terms I can get the right result on top:

SELECT TOP (1000) 
    [title],
    [author],
    [genre],
    [publisher],
    [storyteller],
    [TaleID],
    k.[RANK]
FROM 
    [Books].[dbo].[Tales] AS t 
INNER JOIN 
    FREETEXTTABLE([Tales], ([title],[author],[genre]), 'history of america''s civil war by david potter') as k ON t.TaleID = k.[key]
ORDER BY
    k.[RANK] DESC;
Solution

  • Here is a link to the documentation of the ranking algorithms and Query with Full-Text Search

    From the documentation:

    CONTAINS/CONTAINSTABLE
    Match single words and phrases with precise or fuzzy (less precise) matching. You can also do the following things: Specify the proximity of words within a certain distance of one another. Return weighted matches. Combine search conditions with logical operators.

    FREETEXT/FREETEXTTABLE
    Match 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.