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. 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 let's see what steps we need to follow in order to accomplish and execute Full Text search queries.

Implementation

The very first thing we need to do before at least 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 we will discuss FTS Index 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 Sensitive. 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:

CREATE FULLTEXT CATALOG FTS_DEMO
WITH ACCENT_SENSITIVITY = OFF

Now, we have catalog ready for the Full Text Search. Now it’s time to add index to the tables required. First, let's discuss what Indexes are and how they behave. These are allowed on a table, i.e., one index per table and at most 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. Let's 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 in order 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:

CREATE FULLTEXT INDEX ON dbo.[Customer]
(CustName, CustEmail)
KEY INDEX PK_Customer_Index
ON FTS_DEMO
WITH STOPLIST = SYSTEM

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:

sp_help [TABLENAME]

This will give you the name as PK_***** something like this. This is very much required as we 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:

SELECT * FROM dbo.[Customer_BMW]
WHERE CONTAINS(Cust_Email,'gmail')

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:

SELECT * FROM dbo.[Customer_BMW]
WHERE CONTAINS(Cust_Email,'gmail OR yahoo')

The result goes as below:

SELECT * FROM dbo.[Customer_BMW]
WHERE CONTAINS(Cust_Email,'gmail AND suraj')

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 queries if you have 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.