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