Geek Answers Handbook

% at the beginning of a similar article

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

More information on this will help me understand the impact of this problem.

+4
source share
7 answers

A % at the beginning of the LIKE means that the indices are completely useless. If there is static text to bind the template to % , there, at least the potential utility can be obtained from the indices.

+13
source

%foo basically says: “All rows end with“ foo. ”To filter them, the SQL server must scan the entire table (in the worst case) and check each row. That's why it is so expensive.

+5
source

Why is LIKE '% ...' not good? you cannot use any index and must scan the entire table.

here is a good example:

go into the phone book and find me names that match "% ch". Which will be long enough, since you are not able to use a 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 can use a constant computed column, where you have a REVERSE () column, and then index it. Then you can request:

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

to add a constant computed column (which changes the row) and an index on it, use this code:

 ALTER TABLE YourTable ADD ReversedYourString AS REVERSE(YourString) PERSISTED CREATE NONCLUSTERED INDEX IX_YourTable_ReversedYourString ON YourTable (ReversedYourString)
+5
source

with it, a performance stream will be added anywhere, because there is no index in the contents of the text field.

With it, he must perform the worst-case search until the end of the text field.

+2
source

If you have% at the beginning of your sentence, the query mechanism cannot create a query plan that uses indexes, but must perform a table scan.

+2
source

Many people have explained why col1 likes "% ...". Bad.

Here's a potential workaround if you come across this situation a lot:

  • create another column say col2
  • write trigger when inserting / updating col1 that populates col2 with col1 back
  • create index on col2
  • if your application should look for col1, for example, "% ...", look for col2 as "...%" instead of (or even substr (col1, etc.) = "..." [pardon my oracle talk]

We used it to find the last VIN (Vehicle Identification Number) or SocialSecurity number, and it works great! The performance improvement was really great.

+1
source

Full table scan

which DBAs are most afraid;)

Since the search cannot be accelerated by the index, the server must skip each record in the table (= table scan) and check if the record matches the LIKE expression.

This can be a problem for small tables, but, of course, for large tables with a large number of rows, since all records must be extracted from disk.

This is contrary to index scans, where search criteria allow the server to use the index to limit the search to a (ideally) small set of records.

0
source

More articles:


All Articles