% in the beginning of like clause

Question

I heard that its not advised to use % in the beginning of LIKE clause in SQL Server due to performance reasons.Why is this is so?

Some more details on this will help me in understanding the impact of this issue.

Answer 1

A % on the beginning of a LIKE clause means that indexes are completely useless. If there is static text to anchor the pattern to in front of the %, there's at least potential utility to be obtained from indexes.

Answer 2

having it anywhere would add a performance drain because there's no index on the content of the text field.

With it at the start, it has to do a search at worst case to the end of the text field.

Answer 3

why is LIKE '%...' not good? you can't use any index and must scan the entire table.

here is a good example:

go to the phone book and find me all names that match '%ch'. That will take quite a while since you are not able to use the clustered index, and must scan the entire book!

Given the data 'abcdefg'

WHERE Column1 LIKE '%cde%'  --can't use an index

WHERE Column1 LIKE 'abc%' --can use and index

WHERE Column1 Like '%defg' --can't use an index, but see note below

Note: If you have important queries that require '%defg', you could use a persistent computed column where you REVERSE() the column and then index it. Your can then query on:

WHERE Column1Reverse Like REVERSE('defg')+'%' --can use the persistent computed column's index

to add a persisted computed column (that reverses the string) and index on it, use this code:

ALTER TABLE YourTable ADD ReversedYourString  AS REVERSE(YourString) PERSISTED 

CREATE NONCLUSTERED INDEX IX_YourTable_ReversedYourString 
ON YourTable (ReversedYourString) 

Answer 4

%foo basically says "All strings ending with 'foo'". In order to filter out those, SQL server has to scan all table (in worst-case scenario) and check each and every string. This is why it's so expensive.

Answer 5

Full Table Scan

what DBAs fear most ;)

Since the search cannot be sped up by an index, the server has to loop through each record in the table (= table scan) and check whether the record matches the LIKE expression.

This may not be a problem for small tables, but certainly is for larger tables with lots of rows, since the records all have to be fetched from disk.

This is opposed to index scan, where the search criteria allow the server to use an index to restrict search to a (ideally) small set of records.

Answer 6

A lot of people have explained why col1 like '%...' is bad.

Here's a potential workaround if you run into this situation a lot:

• create another column, say col2
• write a trigger at insert/update of col1 that fills col2 with col1 "backwards"
• create an index on col2
• if your application has to search for col1 like '%...', search for col2 like '...%' instead (or even substr(col1, etc.) = '...' [pardon my oracle dialect]

We were using it to search for the last digits of a VIN (Vehicle Identification Number) or a SocialSecurity number and it worked great! The performance improvement were really great