Full Text Search in SQL

Introduction

Executing complex queries against character-based data on SQL tables can be accomplished using Full Text Queries across SQL as well as Azure SQL databases. There are many times this type of requirement exists and we search for options for the implementation. I would like to share one such requirement. Assume, 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 the search? Interestingly, Full Text Search will do that for us.

Now let's see what steps we need to follow to do it 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 for 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 that restricts the SQL to allow them in the FTS indexes, now FTS Index we will explain next. Now how the stoplist works is, the words specified in the stoplist are avoided and not added into the indexes from the search criteria's text.

Thus, the following images show how to add a catalog:



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



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

CREATE FULLTEXT CATALOG FTS_DEMO
WITH ACCENT_SENSITIVITY = OFF

Now we have the catalog ready for the Full Text Search. Now it's time to add an index to the tables required. First, let's explain what the indexes are and how they behave. These are allowed on a table, in other words one index per table and at most 1024 columns are supported per table. On the basis of 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 the PK is prefixed. This states that we cannot create another index on the table.



The preceding dialog states that we need to check to select the columns that 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 preceding dialog asks to select the catalog into which the indexes for the table will be added. Here we select the catalog we created and then click Next.





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

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

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 preceding steps using 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 a requirement since we may panic on getting an error saying:

A full-text search key must be a unique, non-nullable, single-column index that 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 as in the following:

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

This is how the syntax goes as above that is simple. This uses the keyword provided by FTS, in other words CONTAINS since 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 that it makes a search using the meaning of the words. Using the CONTAINS we can use multiple string entries using OR or AND as in the following:

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

The result goes as in the following:

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

The result goes as in the following:



Conclusion

Thus, this is how simple and easy a Full Text Search implementation is. This will be very handy if such a requirement develops. 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.