Create SQL Server Full-Text Search

In order to start using SQL Server Full-Text Search (FTS) capabilities in our database, it’s only required to accomplish two steps:

1. Create a full-text catalog.
2. Create a full-text index with the columns we are going to enable FTS.

For our Post table, we want to allow FTS for the Title and Text columns. We need to assign a name to create the catalog.

USE BlogDb
GO
CREATE FULLTEXT CATALOG [PostFTSCatalog] WITH ACCENT_SENSITIVITY = ON
GO

Now we are ready to create the index. It is required that the table contains one unique index (normally we use the primary key). The table can have only one full-text index, to create the full-text index you specify the table, columns involved, the unique index on that table, and a name. This is the statement to create it:

CREATE UNIQUE INDEX UI_Post ON Post(Id);
GO

CREATE FULLTEXT INDEX ON Post
(Title, [Text])  
KEY INDEX UI_Post 
ON PostFTSCatalog
GO

That’s it, we have enabled SQL Server Full-Text Search in our table.