title: SQL Server: how to optimize "like" queries?
body: <p>I have a query that searches for clients using "like" with wildcard. For example:</p>
  
  <pre><code>SELECT TOP (10) 
         [t0].[CLIENTNUMBER], 
         [t0].[FIRSTNAME], 
         [t0].[LASTNAME], 
         [t0].[MI], 
         [t0].[MDOCNUMBER]
    FROM [dbo].[CLIENT] AS [t0]
   WHERE (LTRIM(RTRIM([t0].[DOCREVNO])) = '0') 
     AND ([t0].[FIRSTNAME] LIKE '%John%') 
     AND ([t0].[LASTNAME] LIKE '%Smith%') 
     AND ([t0].[SSN] LIKE '%123%') 
     AND ([t0].[CLIENTNUMBER] LIKE '%123%') 
     AND ([t0].[MDOCNUMBER] LIKE '%123%') 
     AND ([t0].[CLIENTINDICATOR] = 'ON')
  </code></pre>
  
  <p>It can also use less parameters in "where" clause, for example:</p>
  
  <pre><code>SELECT TOP (10) 
         [t0].[CLIENTNUMBER], 
         [t0].[FIRSTNAME], 
         [t0].[LASTNAME], 
         [t0].[MI], 
         [t0].[MDOCNUMBER]
    FROM [dbo].[CLIENT] AS [t0]
   WHERE (LTRIM(RTRIM([t0].[DOCREVNO])) = '0') 
     AND ([t0].[FIRSTNAME] LIKE '%John%') 
     AND ([t0].[CLIENTINDICATOR] = 'ON')
  </code></pre>
  
  <p>Can anybody tell what is the best way to optimize performance of such query? Maybe I need to create an index? This table can have up to 1000K records in production.</p>