title: How to make LIKE '%Search% faster in SQL Server
body: <p>I have a below query which is taking up a lot of time:-</p>
  
  <pre><code> SELECT 
        p.CrmId,
        park.Name
   FROM
       Property p
   INNER JOIN
       Som som ON som.CrmId = p.SystemOfMeasurementId 
   LEFT JOIN
       Park park ON park.CrmId = p.ParkId
   WHERE 
       ((p.City like '%' + @search + '%' ) 
        OR (p.Address1 like '%' + @search + '%') 
        OR (p.Address2 like '%' + @search + '%' )
        OR (p.State like '%' + @search + '%' ) 
        OR (park.Name like '%' + @search + '%' ) 
        OR (p.ZipCode like '%' + @search + '%')
       )
       AND (@usOnly = 0 or (p.CrmCountryId = @USA_COUNTRY_ID))
  </code></pre>
  
  <p>I have read that indexes don't work well when we use <code>LIKE '%Search%</code>.</p>
  
  <p>Any ideas on how to make this faster?</p>