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.