Full Text Search in SQL

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:
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:
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:


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:
The result goes as in the following:


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.