IT Job search

Optimize query for LIKE

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. An interesting approach someone mentioned 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.

Digg This : Click to email a colleague Author details ------------------------------------------------------------------------ Jack Vamvas is a SQL Server Consultant. To contact him about this article , or any issues related to SQL Server register on www.ciquery.com and post a question. For SQL Server Consultancy of any SQL Server task - check SQL Server Specialists Make SQL Server go faster - click here