Creating Full Text Index in SQL Server

Introduction

In this article we will see how to create a full text search index for SQL Server database for effective search. In so many cases we need to provide the search facility in our application. To provide searching facility within our local database we can use the greater feature provided by Microsoft SQL Server i.e. full text index.

Background

When we want to use the full text index service we need to start the service first else it will raise the errors. So let's start with how to start the service and use the Full text index step by step.

Step 1

Open SQL Server and create a new database with the following table for eg. Tbl_Search but keep in mind that the full text index only works on primary key or unique key containing tables.

Create Table Tbl_Search
(
Id Int Primary Key Identity(1,1),
Title Varchar(500),
[Desc]Varchar(max)
)

Insert some rows in the created table.

Step 2

Now we will create full text index on our database and table for providing search in table. So create the Full Text Catalog on our database by using following query.

CREATE FULLTEXT CATALOG FTSearch

In the query above we have created FullText Catalog with the name FTSearch.

Step 3

Now we will create full text index on our table Tbl_Search but for that we require the unique key id or primary key id so find the id of unique or primary key by using following command.

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

This command will display the all constraint names on tables present in our database from the output. Copy the Tbl_Search constraint name for creating a full text index on TblSearch.

CREATE FULLTEXT INDEX ON Tbl_Search