Full Text Search : Basics and Configuration

This is the first blog in a series of posts which I am planning to write  on full text search .In this post I will give a brief introduction on full text search and explain how to configure it in SQL Server 2012.I will be using SQL commands rather than the graphical interface provided by SQL Server Management studio for configuration.

What is Full Text Search

As per MSDN:

Full-text queries perform linguistic searches against text data in full-text indexes by operating on words and phrases based on rules of a particular language such as English or Japanese. Full-text queries can include simple words and phrases or multiple forms of a word or phrase.

Basically there are lot of things which you can do using Full Text Search which are either not possible using normal keyword search or are tedious.Below are couple of scenarios.

1. Better performance due to index support
2. Ordering or Ranking of results based on the match relevance
3. Search different forms of a word based on the language e.g. Searching for Process will search for Process,Processing,Processed etc.
4. Search for word and all its synonyms

There are many other things which are possible using advanced full text queries.

Configuring Full Text Search

The information mentioned below is primarily applicable to SQL Server 2012 but should work with minor or no changes for SQL Server 2008 as well.

• Pre-requisites

To use full text search the feature should be installed while installing SQL Server 2012.If you have an existing installation which does not have this feature installed you can run the setup again and choose the option when setup asks you to add features to existing installation.

If you are using SQL Server 2012 Express edition then you will need SQL Server 2012 Express edition with Advanced Services to have full text search related features.It can be downloaded from this link.

I am using a sample database (viz. EmployeeProfile) that I created and populated with some dummy values for the purpose of this post.Only requirement here is that the table on which you want to enable full text search should have a unique index defined on a non nullable column (or just define a primary key column).This is called Key Index.

• Create Full Text Catalog

First step is to create a full text catalog.Full text catalog is just a mechanism for organizing full text indexes.One full text catalog usually have many full text indexes but one full text index can only be part of one catalog.

Run below command in SQL Server management studio to create a full text catalog:

CREATE FULLTEXT CATALOG EmployeeProfileCatalog;

You can see this catalog created under storage node of your database.

• Create Full Text Index

Second step is the create a full text index on the columns which you want to enable for full text search.Only one full text index can be defined on  a Table.

CREATE FULLTEXT INDEX ON EmployeeProfile
 ( 
  EmployeeJobSector
     Language 1033,
  EmployeeResume
     Language 1033
 ) 
  KEY INDEX PK_EmployeeProfile 
      ON EmployeeProfileCatalog; 

Language code 1033 in the above command specifies English language.

• Start Full Population

Next step is to just populate your indexes by a process called Population.Basically you can run the above create index command in a way where it starts the population immediately after the index have been created.This although is discouraged in real life scenarios as population on big tables is a performance intensive task and is usually done during lean hours by DBAs.

   ALTER FULLTEXT INDEX ON EmployeeProfile
   START FULL POPULATION;

That’s it ! Now you are ready to run full text queries on your table. I will be writing a separate post on full text queries but just to give you a taste of these queries i will list down some examples of using “Contain” keyword for searching.

Simple search for two keywords in a Text column

select * from EmployeeProfile 
where Contains(EmployeeResume,'Extensive AND Experience')

Results: 73 Rows

Search for two keywords near to each other (within 2 character distance)

select * from [EmployeeProfile] 
where Contains(EmployeeResume,'NEAR((Extensive,Experience),2)')

Results: 42 Rows

Search for different forms of a same keyword  : Clothes

select * from [EmployeeProfile] where 
Contains(EmployeeJobSector, 'FORMSOF(INFLECTIONAL, Clothes)')

Results: Returns both Clothes as well as Clothing

For further reading on this and related topics you can refer this exhaustive course on pluralsight.