Ben Forta

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.