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;

-- 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

Enabling Fulltext search in Azure SQL Database

One of the greatest searching facility provides by database management system is full-text search. This was available even with Microsoft SQL Server 2005, and throughout all versions. Now it is available with Azure SQL Database V12 and it can be simply enabled and used with TSQL used for working with on-premise databases.

Full-Text Search offers fast and rich text-based searches. It requires a catalog to be maintained and can be implemented with character-based columns in tables.

Read more on Full-Text indexing: https://msdn.microsoft.com/en-us/library/ms142583.aspx

Full-Text search or Azure search

Azure provides Azure Search for performing searches on various sources and application. It can be used instead of Full-Text Search however if the requirement is only for searching against character-data stored in the SQL Server and need to join search result with other tables, then Full-Text Search is recommended.

Here is a simple example of enabling Full-Text in Azure SQL Database.

-- 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 Server');

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