Full Text Search

A rarely used part of SQL Server is its' full text search capabilities. Honestly, it is quite a basic implementation of FTS but it can do the job if you can live with its limitations.

If you need a full blown full text search engine look at Solr or Elasticsearch - these are the leading search engines IMO. I have implemented Solr in a production environment and it was awesome.

But I have also used SQL Servers FTS to fix a performance issue.

In the case of the performance issue, the application was building queries with clauses like:

(T_5001.C_CODE LIKE '1655001%')) OR (T_5001.C_DESCRIPTION LIKE '%1655001%')) OR (T_5001_31_T_5026.C_SUPPLIERPRODUCTCODE LIKE '1655001%'))

A lot of these columns were defined as text type column (nvarchar) datatype.

Why they were using this datatype instead of an int, I don't know. Just another reminder about how important it is to select the correct datatype.

The implementation of a FTS on these columns required code changes to use CONTAINS and FREETEXT clauses instead of being able to use a LIKE, but the developers just created a new condition class (whatever that means).

This lead to a performance improvement in the application by using SQL Servers FTS.