Full Text Search in SQL Server

As we all know, we create clustered and non clustered index on columns to make the search efficient because those indexes are organized in B-Tree structure.

We can create such indexes on most of the columns in tables or views except those which are configured for large object data types.

Although this limitation is not a problem in many cases, there will be times when you’ll want to query such column types. However, without indexes defined on the columns, the query engine must perform a full table scan to locate the necessary data. But there is a solution—full-text search.

In this article we will see how to enable the full text search on table with an example.

Step 1) To enable the full text search on table your database should be configured with full text search enable option, if DB is not enabled then you can do the same using below SQL statement.

exec sp_fulltext_database ‘enable’

Step 2) Create a table on which we want to test this feature.

Create Table Contact (Id Int Identity(1,1) Primary Key, Name varchar(100), ContactName Varchar(1000))

Step 3) Insert some data into the table.

Insert Into Contact (Name, ContactName) Values(‘Mr. XY’,’Mr. YZ Mr.ZA’)
Insert Into Contact (Name, ContactName) Values(‘Mr. AB’,’Mr. AB Mr.BC’)
Insert Into Contact (Name, ContactName) Values(‘Mr. BC’,’Mr. CD Mr.DE’)
Insert Into Contact (Name, ContactName) Values(‘Mr. CD’,’Mr.DE’)

Step 4) Create Catalog for Full Text Index.


Step 5) Create Index on column

CREATE FULLTEXT INDEX ON Contact (Name, ContactName) Key Index PK__Contact__3214EC07E922EB7B

Where PK__Contact__3214EC07E922EB7B is the clustered index on Contact table. You can check index name in your table using

sp_helpindex Contact

Step 6) Execute the query and check for the result

Select * From Contact C
Inner Join FreeTextTable (Contact, *, ‘CD’) FC
On C.Id = FC.[Key]

Please note, FreeTextTable function should be used along with the From clause.