• Adam Thurgar

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.


6 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t