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.
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.
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.
%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.
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.
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.
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)
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.
A lot of people have explained why col1 like '%...' is bad.
Here's a potential workaround if you run into this situation a lot:
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