Ranking SQL Server 2005 Full-Text Search Results

When performing full-text searches you usually want not just results, but a ranking indicating how close a match is to what you are looking for. In SQL Server 2005, ranks are accessed via ranking functions – FULLTEXT searches are ranked using function FULLTEXTTABLE() and CONTAINS searches are ranked using function CONTAINSTABLE(). Both of these functions are used the same way, and both accept search patterns, the same search patterns supported by the FULLTEXT and CONTAINS predicates themselves.
Here is an example:
SELECT f.rank, note_id, note_text
FROM productnotes,
FREETEXTTABLE(productnotes, note_text, 'rabbit food') f
WHERE productnotes.note_id=f.[key]

This example performs a FREETEXT type search. Instead of filtering using the WHERE clause, the FREETEXTTABLE() function is used and given a search pattern instructing the full-text engine to match any rows that contain words meaning rabbit and food. FREETEXTTABLE() returns a table which is given an alias of “f” (so as to be able to refer to it in column selection and the join), this table contains a column named “key” which will contain the primary key value of the table that was indexed (productnotes in this example), and “rank” which is the rank value assigned. And finally, results here are sorted by rank descending, as the higher the rank the greater the match.
It is also possible to assign weight values to search patterns and words. The rankings assigned in the example used here assumed that all words were equally important and relevant. If this is not the case, and some words are more important than others, then the ISABOUT() function can be used to assign relative weights, and the full-text search engine will then use these values when determining rankings.

5 responses to “Ranking SQL Server 2005 Full-Text Search Results”

  1. RichF Avatar

    Love the post; I have the full text search up and running. I used your SQL but the rank on each of the results = ‘0’.
    Any Ideas?

  2. Patrick Avatar

    working fine over here, maybe you have the wrong columns in your ft catalog

  3. Gayeshan Avatar

    You are getting rank 0 because it uses a previously built FT catalog. If you rebuild the catalog you should get values for the RANK.
    Either you can rebuild the FTC using the following query or right clicking the FTC from SQL Server Management Studio

  4. mike Avatar

    is there a rational explanation for which after some select statements, the rank returned by the full-text search engine is 0, knowing that just after the repopulation the rank is displayed correctly?
    in other words, time and usage messes up the ranking….

  5. Ankit Avatar

    I am doing FREETEXT search in my Database.
    I have indexed my table (Article_ArticleContent) and included some of the columns.
    However, executing the query do not returns any records:
    cArticleTitle, cShortDescription
    (cArticleTitle,’ "a*" ‘);
    Although executing the following query, it returns me the correct number of records:
    select cArticleTitle, cShortDescription
    from dbo.Article_ArticleContent
    where cArticleTitle LIKE ‘a%’
    Can you please let me know if I am missing something in Indexing or in the SELECT query.

Leave a Reply