title: CHARINDEX vs LIKE search gives very different performance, why?
body: <p>We use Entity Frameworks for DB access and when we "think" LIKE statement - it actually generates CHARINDEX stuff. So, here is 2 simple queries, after I simplified them to prove a point on our certain server:</p>
  
  <pre><code>-- Runs about 2 seconds
  SELECT * FROM LOCAddress WHERE Address1 LIKE '%1124%' 
  -- Runs about 16 seconds
  SELECT * FROM LOCAddress WHERE ( CAST(CHARINDEX(LOWER(N'1124'), LOWER([Address1])) AS int)) = 1
  </code></pre>
  
  <p>Table contains about 100k records right now. Address1 is VarChar(100) field, nothing special.</p>
  
  <p>Here is snip of 2 plans side by side. Doesn't make any sense, shows 50% and 50% but execution times like 1:8
  <a href="https://i.stack.imgur.com/oiBEL.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/oiBEL.jpg" alt="enter image description here"></a></p>
  
  <p>I searched online and general advice is to use CHARINDEX instead of LIKE. In our experience it's opposite. My question is what causing this and how we can fix it without code change?</p>