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.