create table pat_test( i int, j varchar( 20 ))
go
create clustered index idx_pt on pat_test (i)
go
create index idx_j on pat_test(j)
go
declare @i int, @j varchar ( 20 ), @x int

select @x =  0 

while @x <  10000 
begin 
    select @i = @x
    select @x = @x+ 10 
    while @i < @x
    begin
        select @i = @i+ 1 
        select @j = 'aa' + convert (varchar( 10 ), @i)
        insert into pat_test values (@i, @j)
    end
    
    select @x = @x+ 10 
    while @i <@x
    begin
        select @i = @i+ 1 
        select @j = 'bb' + convert (varchar( 10 ), @i)
            insert into pat_test values (@i, @j)
    end
    
    select @x = @x+ 10 
    while @i <@x
    begin
        select @i = @i+ 1 
        select @j = 'cc' + convert (varchar( 10 ), @i)
            insert into pat_test values (@i, @j)
    end
end

-- test 1
set showplan on
go
select * from pat_test
where i between  120  and  180 
and ( j like 'aa%' or j like 'bb%')

set showplan on
go
select * from pat_test
where  i between  120  and  180 
and substring(j, 1 , 2 ) in ('aa', 'bb')

set showplan on
go
select * from pat_test
where  i between  120  and  180 
and (substring(j, 1 , 2 ) = 'aa' or  substring(j, 1 , 2 ) = 'bb')

-- test 2
set statistics io, time on
go
select * from pat_test
where i between  120  and  180 
and ( j like 'aa%' or j like 'bb%')

set statistics io, time on
go
select * from pat_test
where  i between  120  and  180 
and substring(j, 1 , 2 ) in ('aa', 'bb')

set statistics io, time on
go
select * from pat_test
where  i between  120  and  180 
and (substring(j, 1 , 2 ) = 'aa' or  substring(j, 1 , 2 ) = 'bb')
-- test 3
 insert into pat_test 
 select count(*)+ 1 , 'cc' from pat_test
 
  insert into pat_test 
 select count(*)+ 1 , 'zz' from pat_test
 
 set showplan on
 go
  select * from pat_test
where  i >  10000 
and (substring(j, 1 , 2 ) = 'cc' or  substring(j, 1 , 2 ) = 'zz')