How to change "LIKE '1%'" so that it is efficient for integers?

Question

I want to have query like this :

SELECT TOP 60 lcode
FROM LessonChart
WHERE lcode LIKE '1%'

But SQL Server says that conversion of int to varchar is not good and keyword "LIKE" is not efficient...

What should I do instead of this code ?

Change the design such that operation is not required. Maybe it shouldn't be an INT column anyway? — user2864740, Sep 08 '14 at 18:46
That is because LIKE is used to evaluate character values, not integers. You would have to cast your lcode as a varchar for this to work. — Sean Lange, Sep 08 '14 at 18:46
@SeanLange SQL Server [will automatically perform a cast](http://sqlfiddle.com/#!3/631fe/1) here (not sure about other vendors), but a big problem is the query is no longer [Sargable](http://en.wikipedia.org/wiki/Sargable). The OP clearly says the problem "conversion of int to varchar is *not good*" and keyword "LIKE" is *not efficient*". — user2864740, Sep 08 '14 at 18:51
The reason it isn't sargable is because of the implicit conversion. This all screams of incorrect datatype. — Sean Lange, Sep 08 '14 at 18:56
What is it you are trying to get from the data? Everything that starts with 1? 1, 10, 100, 1234, etc — Steve, Sep 08 '14 at 18:59

score 3 · Accepted Answer · edited May 23 '17 at 12:12

The core issue - and the warning - is even though lcode LIKE '1%' works in SQL Server (it accepts the syntax and perform an implicit cast) is, in doing so, the query is no longer Sargable. This means that any indices on the INT column cannot be used in the LIKE operation because of the implicit conversion.

.. a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query. The term is derived from a contraction of Search ARGument ABLE.

This loss of being able to use an index isn't specific to this CAST-and-then-LIKE operation and it also affects conditions that use the column in other expressions, such as math. While warnings are nice, SQL Server is rather limited in which queries it will outright complain about - a slow query may just be a slow query.

If this loss-of-index usage isn't (and won't be) a problem because only a relatively few rows have the condition applied (eg. there is little data or the query has already reduced the candidates to a reasonable number), then don't worry about it - but do keep reading as a change might be warranted anyway. See Display and Save Execution Plans which, along with knowledge the actual database design/growth, can help inform this decision.

If not being able to use an index is a problem or will be a problem (because the predicate is applied over tens of thousands if not millions of rows), or if looking for possible ways to simply clean up the query, then:

Evalaute the design. Is it really an integer value (eg. amount of money) or is it an identifier that uses only digits (eg. phone-number or zip-code)?

If it is the latter considering changing the design to CHAR/VARCHAR and add an index. This will fix the sargability issue and make the column/type better-represent the information.
Evaluate the design again. Does this leading "1" indicate information separate from the rest of the identifier? If so, could/should it be normalized into a separate column?

If it makes sense to separate it, then create a separate indexable (bit?) column and move on.

Even if it should not be "separated" per say a persisted computed column can be used. (While the identifier/lcode would now contain redundant information it would not be queried against directly.)

A computed column is computed from an expression that can use other columns in the same table .. By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic ..
If the design is correct such that neither of the above apply and the query must be able to use indices for performance reasons, then write the query so that it can use indices. Here is a kludge that is able to use the indices (and note how much of a kludge it is, see above).
```
WHERE lcode = 1
   OR lcode BETWEEN 10 AND 19
   OR lcode BETWEEN 100 AND 199
  -- etc
```
For a small number of rows this won't matter, but as the number of rows involved in the predicate increases using (even an ugly) indexable query can avoid massive execution penalties associated with having to load/scan an excessive number of rows.

How to change "LIKE '1%'" so that it is efficient for integers?

1 Answers1