• Article

Limiting Ranked Result Sets (Full-Text Search)

The CONTAINSTABLE and FREETEXTTABLE functions return a column named RANK that contains ordinal values from 0 through 1000 (rank values). These values are used to rank the rows returned according to how well they match the selection criteria. The rank values indicate only a relative order of relevance of the rows in the result set, with a lower value indicating lower relevance. The actual values are unimportant and typically differ each time the query is run.

Note

The CONTAINS and FREETEXT predicates do not return any rank values.

The number of items matching a search condition is often very large. To prevent CONTAINSTABLE or FREETEXTTABLE queries from returning too many matches, use the optional top_n_by_rank parameter, which returns only a subset of rows. top_n_by_rank is an integer value, n, that specifies that only the n highest ranked matches are to be returned, in descending order. If top_n_by_rank is combined with other parameters, the query could return fewer rows than the number of rows that actually match all the predicates.

SQL Server orders the matches by rank and returns only up to the specified number of rows. This choice can result in a dramatic increase in performance. For example, a query that would normally return 100,000 rows from a table of one million rows are processed more quickly if only the top 100 rows are requested.

Note

For information about how full-text search generates the rank values, see How Search Query Results Are Ranked (Full-Text Search).

Example

Example A: Searching for only the top three matches

The following example uses CONTAINSTABLE to return only the top three matches.

USE Northwind;
GO
SELECT   K.RANK, CompanyName, ContactName, Address
FROM      Customers AS C
         INNER JOIN
         CONTAINSTABLE(Customers,Address, 'ISABOUT ("des*",
            Rue WEIGHT(0.5),
            Bouchers WEIGHT(0.9))', 3) AS K
         ON C.CustomerID = K.[KEY];
GO

Here is the result set.

RANK CompanyName          ContactName       address            
---- ------------         -----------       -------            
123  Bon app'             Laurence Lebihan  12, rue des Bouchers 
65   Du monde entier      Janine Labrune    67, rue des Cinquante Otages 
15   France restauration  Carine Schmitt    54, rue Royale

Example B: Searching for the top ten matches

The following example uses CONTAINSTABLE to return the description and category name of the top 10 food categories where the Description column contains the words "sweet and savory" near either the word "sauces" or the word "candies."

SELECT FT_TBL.Description, 
   FT_TBL.CategoryName, 
   KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
   CONTAINSTABLE (Categories, Description, 
      '("sweet and savory" NEAR sauces) OR
      ("sweet and savory" NEAR candies)'
      , 10
   ) AS KEY_TBL
   ON FT_TBL.CategoryID = KEY_TBL.[KEY];
GO

In This Section

  • How Search Query Results Are Ranked (Full-Text Search)
    Describes how full-text search generates the rank values returned by a query, including commonly used terms and statistical values that are important in calculating rank, rank computation issues, and the ranking of CONTAINSTABLE and FREETEXTTABLE functions results.