SQL Server › Full Text Search

How do you rank and sort full text search results by their relevance to the search query?

Advanced

Answer

You use the CONTAINSTABLE or FREETEXTTABLE table valued functions instead of the simple CONTAINS or FREETEXT predicates, which return a table including a RANK column representing how relevant each matching row is, letting you join this back to your original table and sort the results by relevance, showing the most relevant matches first.

SELECT a.*, ft.RANK
FROM Articles a
INNER JOIN CONTAINSTABLE(Articles, Content, 'database performance') ft
  ON a.ArticleId = ft.[KEY]
ORDER BY ft.RANK DESC;

Real-world example A search results page shows the most relevant articles first by joining the articles table with CONTAINSTABLE's ranking information, giving users a genuinely useful, relevance sorted search experience.

Common follow-ups: What factors influence how the RANK value is calculated?;What is the difference between CONTAINSTABLE and FREETEXTTABLE?

Query Optimization & Plans;Indexes