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]
ORDER BY rank DESC;

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 thoughts

  1. Ben,
    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?
    RF

  2. 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
    ALTER FULLTEXT CATALOG catalog_name
    REBUILD

  3. hello.
    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….
    thanks!

  4. Hello
    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:
    SELECT
    cArticleTitle, cShortDescription
    FROM
    Article_ArticleContent
    WHERE CONTAINS
    (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.
    Ankit

Leave a Reply