SQL Server Full-Text Search with rank values

Once I wrote a post titled Enabling Fulltext search in Azure SQL Database discussing Full-Text search in Azure. While using it with one of my databases, wanted to show the result of the search ordered by how well they match to the search criteria. In order to sort the result as I need, the best is, get a rank generated for each row and use it for ordering the result. I had used Freetext function for getting the result but if I realized that this cannot be achieved using the Freetext function.

The CONTAINSTABLE and FREETEXTTABLE functions return a column named Rank for showing the rank related to the record based on matching. This can be used get the result sorted based on it, showing most relevant records at the top. Remember, the higher value of the Rank generated indicates the best matching.

Here is a code for showing the way of getting the rank;

view plainprint?

1. -- Creating a table  
2. CREATE TABLE dbo.EmployeeDetails  
3. (  
4.  EmployeeDetailsId int identity(1,1) not null  
5.  , constraint pk_EmployeeDetails primary key (EmployeeDetailsId)  
6.  , WorkingExperience nvarchar(4000) not null  
7.  , ProjectsWorked nvarchar(4000) not null  
8.  , Resume nvarchar(max)   
9. )  
10. GO  
11.   
12. CREATE FULLTEXT CATALOG EmployeeCatelog;  
13. GO  
14.   
15. CREATE FULLTEXT INDEX ON dbo.EmployeeDetails   
16.  (WorkingExperience, ProjectsWorked, Resume) KEY INDEX pk_EmployeeDetails  
17.  ON EmployeeCatelog;  
18.  -- By default CHANGE_TRACKING = AUTO  
19.   
20.   
21. -- Once enabled, search can be performed;  
22. SELECT *  
23. FROM dbo.EmployeeDetails  
24. WHERE freetext ((WorkingExperience, ProjectsWorked, Resume), 'SQL');  
25.   
26. SELECT *  
27. FROM dbo.EmployeeDetails  
28. WHERE freetext ((Resume), 'SQL');  
29.   
30. -- Get the rank and sort the result using it  
31. SELECT t.Rank, e.*  
32. FROM dbo.EmployeeDetails e  
33.  INNER JOIN CONTAINSTABLE (dbo.EmployeeDetails, (WorkingExperience, ProjectsWorked, Resume), 'SQL') AS t  
34.   ON e.EmployeeDetailsId = t.[Key]  
35. ORDER BY t.Rank DESC  

-- Creating a table
CREATE TABLE dbo.EmployeeDetails
(
 EmployeeDetailsId int identity(1,1) not null
 , constraint pk_EmployeeDetails primary key (EmployeeDetailsId)
 , WorkingExperience nvarchar(4000) not null
 , ProjectsWorked nvarchar(4000) not null
 , Resume nvarchar(max) 
)
GO

CREATE FULLTEXT CATALOG EmployeeCatelog;
GO

CREATE FULLTEXT INDEX ON dbo.EmployeeDetails 
 (WorkingExperience, ProjectsWorked, Resume) KEY INDEX pk_EmployeeDetails
 ON EmployeeCatelog;
 -- By default CHANGE_TRACKING = AUTO


-- Once enabled, search can be performed;
SELECT *
FROM dbo.EmployeeDetails
WHERE freetext ((WorkingExperience, ProjectsWorked, Resume), 'SQL');

SELECT *
FROM dbo.EmployeeDetails
WHERE freetext ((Resume), 'SQL');

-- Get the rank and sort the result using it
SELECT t.Rank, e.*
FROM dbo.EmployeeDetails e
 INNER JOIN CONTAINSTABLE (dbo.EmployeeDetails, (WorkingExperience, ProjectsWorked, Resume), 'SQL') AS t
  ON e.EmployeeDetailsId = t.[Key]
ORDER BY t.Rank DESC

Read more at: https://msdn.microsoft.com/en-us/library/cc879245.aspx