I received a question from a client yesterday : "We've implemented a query with LIKE in the WHERE clause. But if I want to search for a suffix (LIKE '%suffix') an Index Scan is used and takes very long. Is there any way that SQL Server does an Index Seek in case of LIKE '%suffix'? How can I optimize? "

By it's nature a - '%suffix' will scan rather than seek. But , something like 'a%suffix' will seek. Note the 'a' before the '%'. The optimizer can make a guess on the basis of 'a'. If the '%' is in front , the optimizer cannot predict the outcome. As an example, inspect this index tree:

+----AAA----+
| |
+-ABB-+ +-CCC-+
| | | |
ADD AEE CFF CGG

An index is a binary tree. The query engine determines which branch of the tree to take by deciding if the value it's looking for is greater than or less than the value it's currently sitting on:

Seeking a value 'ABB': Two jumps gets us to the desired value, because we can navigate the tree.

Seeking a value of '%D', we start with the first node, 'AAA'. Which way do we branch to continue our search? We don't know, therefore we have to look at EVERY node to find those that end in 'G', therefore an index (or table) scan.

Another approach is to store another column with the REVERSE() of the existing column, then querying with the REVERSE() of the search string, 'xiffus%'. This could also be achieved with an indexed view, saving the space in the base table. This would require some manipulation of the query string.

Republished from http://www.sqlserver-dba.com.