views:

245

answers:

7

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.

+2  A: 

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.

Daniel A. White
Some -- probably most -- database engines will use an index if the beginning of the field is fixed. Like if you search on "Abc%", it can use the index to find the first "Abc" and then search sequentially from there. But when the string begins with "%" ... see all the other answers.
Jay
+5  A: 

%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.

Anton Gogolev
+13  A: 

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.

chaos
The key word is "sargable"
Joel Coehoorn
+2  A: 

If you have % at the beginning of your clause, the query engine cannot generate a query plan that uses indexes but has to make table scans.

Maximilian Mayerl
+5  A: 

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)
KM
A: 

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.

devio
+1  A: 

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

IronGoofy
forget about using a trigger, use a computed column is much more efficient. You only need to REVERSE() the original value, you can search for it using the sample code in my answer.
KM
Computed Columns seem to be specific to SQLServer, and it's certainly a nice and elegant solution. As noted, I'm more of an Oracle person.
IronGoofy