Question:
What is faster: two LIKE statements or one BETWEEN?

((MyData LIKE '02105_') OR ((MyData LIKE '02106_)MyData BETWEEN '021050' AND '021069'

Please advise.

Answer:
When trying to decide which is the faster SQL for a particular query, my philosophy is to test the two options rather than making an educated guess.

Using version 7, I created a table with 100,000 rows with data similar to your example. Then I turned on show stats i/o and ran the two versions you provided. There was absolutely no difference in performance. That makes sense because, in both cases, the optimizer took advantage of an index on that column.

However, you should realize that your queries are not exactly the same. The LIKE keyword will return a match on the value ‘02105’, while the query using the BETWEEN statement will skip that row.

Here is the code I used to test your queries:

use pubsgoset nocount ongoif exists(select * from sysobjects where type = 'U' and name = 'liketest')   drop table liketestgocreate table liketest(   i char(6) constraint pk_liketest        primary key (i))godeclare @i intselect @i = 0while @i < 100000begin     insert into liketest (i) values ( convert(char,(floor(rand(@i)* 1000000)),0))         select @i = @i + 1endselect * fromliketest wherei LIKE '12105_' OR i LIKE '12106_'select *from liketest wherei BETWEEN '121050' AND '121069'