Query Match Score

You can sort query results using the score that indicates the closeness of a match.

When you include a CONTAINS clause in the FROM clause of a query, each match has a score associated with it. The score indicates how close the match is, and you can use score information to sort the data. Two main criteria determine score:

The number of times a term appears in the indexed row. The more times a term appears in an indexed row, the higher its score.
The number of times a term appears in the TEXT index. The more times a term appears in a TEXT index, the lower its score.

Depending on the type of full text search, other criteria affect scoring. For example, in proximity searches, the proximity of search terms impacts scoring. By default, the result set of a CONTAINS clause has the correlation name contains that has a single column in it called score. You can refer to "contains".score in the SELECT list, ORDER BY clause, or other parts of the query. However, because contains is a SQL reserved word, you must remember to put it in double quotes. Alternatively, you can specify another correlation name, for example, CONTAINS ( expression ) AS ct. The examples for full text search refer to the score column as ct.score.

This statement searches MarketingInformation.Description for terms starting with ‘stretch” or terms starting with “comfort”:

SELECT ID, ct.score, Description
FROM MarketingInformation
CONTAINS ( MarketingInformation.Description,
           'stretch* | comfort*' )
AS ct ORDER BY ct.score DESC;