Full Text Search in SQL

Introduction

Executing complex queries against character-based data on the sql tables can be accomplished using Full Text Queries across SQL as well as Azure SQL databases. There are many a times this type of requirement arises and we search for options for the implementation. One such scenario, I would like to share one such requirement. Suppose, we want users to search records from a table with column FirstName. Now, if users would like to search multiple entries to the search criteria, then how would the query go for search? Interestingly, FTS will do that for us..
Now lets see what steps we need to follow inorder to accomplish and execute Full Text search queries.

Implementation

The very first thing we need to do before atleast writing the query, is to create Catalogs. Now What is a catalog, this catalog will be a warehouse which will contain all the Indexes (FTS indexes). Select the database to which you would like to add the FTS catalog and move to the storage, expand it and you will find:- Full Text Catalogs and Full Text Stoplist
Now the new thing Full Text Stoplist is an interesting concept.

The stoplist is actually a list of words which restricts the SQL to allow them in the FTS indexes, now FTS Index we will discuss next. Now how the stoplist works is, the words mentioned in the stoplist are avoided and not added into the indexes from search criteria’s text.

Thus, the below images show how to add a catalog:-

The next step after selecting the New Full-Text Catalog, we get a dialog box, which asks for FTS catalog name. With Accent sensitivity, by default Sensitve. More about accent sensitivity here

Now, one thing to note here is as far as I implemented, FTS user interface as shown above is not permissible. So to add catalog, we need to query the script. The query goes as below:

Now we have catalog ready for the Full Text Search. Now it’s time to add index to the tables required. First, lets discuss what Indexes are and how they behave. These are allowed on a table , i.e. one index per table and atmost 1024 columns are supported per table. On the basis on these indexes the FTS can be applied and queried using the columns in the FTS index. Lets see how to add an FTS Index.

Using the User Interface:-

 

Just as we see the interface says directly the options to define an index on the table Customer. The following images will follow the steps through.

 

This creates a unique index on the Customer table, thus PK is prefixed. This states that we cannot create another index on the table.

The above dialog states that we need to check inorder to select the columns which we wish to allow into the FTS index. Here I select both the columns. Then,

 

This dialog states that the changes to the table columns are tracked automatically and the indexes are populated automatically.

 

The above dialog asks to select the catalog into which the indexes for the table are going to be added. Here we select the catalog we created and then click next.

                                                                                                         

 

Then click Next and Finish. Thus FTS index is created for the table Customer .

Now to create the Index using query script, we need to just write and run one query as below:

Thus this would create the Index for us and do the same if we follow the above mentioned steps through images. Here one important thing to note is the KEY INDEX should be the primary unique key for that table created. To get the name you can type and execute the following:

This will give you the name as PK_***** something like this. This is very much required aswe may panic on getting an error saying

A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key.

Now, we are all set to carry on with our query and expect the desired results. The query goes like:

This is how the syntax goes as above which is simple, this uses the keyword provided by FTS i.e. CONTAINS as it takes one keyword to find a match from the records.
Another is the FREETEXT . What this does is it separates the strings into separate words and then based on it makes a search using the meaning of the words.Using the CONTAINS we can use multiple string entries using OR or AND like below:

The result goes as below:

The result goes as below:

Conclusion

Thus, this is how simple and easy Full Text Search implementation is.This will be very handy if such requirement comes up. I hope this helps the readers. Please post the queries if any.
References
Of course the references are the integral part to be shared.
My guru and the master Sql-Blog Authority
MSDN
Happy Reading and Learning. 🙂