If you use a CONTAINS query in the FROM clause, then an additional score column is available for use in the SELECT list, ORDER BY clause, or other parts of the query. By default, the score column has the correlation name, contains. You can optionally specify a different correlation name following the closing bracket of the CONTAINS query. A higher score corresponds to a better match. Therefore, it is often useful to order by descending score to get the best matches first.

Example 1: Searching one column

For example, create a text index on the Products.Description column as follows:

CREATE TEXT INDEX scoringExample 
   ON Products ( Description );

Query the text index as follows. Note that the results of the CONTAINS clause can optionally be followed by a correlation name used to reference the score columns from elsewhere in the SELECT statement.

SELECT Products.Description, ct.* 
    FROM Products CONTAINS ( Products.Description, 'cap OR cotton' ) ct 
    ORDER BY ct.score DESC;

Note that Cotton Cap has a high score in the results because it contains both search terms.

Example 2: Searching multiple columns

When you perform a full text search across multiple columns, the result includes a score for the row. For example, create a text index on Products.Description and Products.Name columns as follows:

CREATE TEXT INDEX scoringExampleMult 
   ON Products ( Description, Name );

Query the Description and Name columns for the terms cap or visor.

SELECT Products.Description, Products.Name, ct.score 
    FROM Products CONTAINS ( Products.Description, 
                             Products.Name, 
                             'cap OR visor' ) ct 
    ORDER BY ct.score DESC;

The scores for a multi-column search are calculated as if the column values were concatenated together and indexed as a single value. Note, however, that phrases and NEAR operators never match across column boundaries, and that a search term that appears in more than one column increases the score more than it would in a single concatenated value.