title: Query performance of searching pattern
body: <p>I have the following sample data for understanding the requirement.</p>
  
  <p>Table: </p>
  
  <pre><code>create table ft_test
  (
      col1 int identity(1,1),
      col2 varchar(max)
  );
  
  insert into ft_test values('John A Henry');
  insert into ft_test values('Dsouza mak Dee');
  insert into ft_test values('Smith Ashla Don');
  </code></pre>
  
  <p><strong>Edit</strong>:</p>
  
  <pre><code>CREATE UNIQUE INDEX UIX_test_Col1 ON ft_test(col1);
  
  CREATE FULLTEXT CATALOG ftcat
  WITH ACCENT_SENSITIVITY = OFF
  
  CREATE FULLTEXT INDEX ON ft_test  
  (col2 LANGUAGE 1033)  
  KEY INDEX UIX_test_Col1  
  ON ftcat  
  WITH STOPLIST = SYSTEM
  </code></pre>
  
  <p>Notes: </p>
  
  <blockquote>
    <ol>
    <li>Search for the given pattern in the given column of the table.</li>
    <li>I have more than 200 millions of records in the table. </li>
    <li>Created FULLTEXT index on required column.</li>
    </ol>
  </blockquote>
  
  <p>Looking for best performing search query.</p>
  
  <p>Givens:</p>
  
  <blockquote>
    <ol>
    <li>Pattern: <code>oh</code> </li>
    <li>Column to  Search: <code>col2</code></li>
    </ol>
  </blockquote>
  
  <p>Expected Output:</p>
  
  <pre><code>col1    col2
  ----------------------
  1       John A Henry
  </code></pre>
  
  <p>Try:</p>
  
  <ol>
  <li><p>Using FULLTEXT Index: <code>CONTAINS</code></p>
  
  <pre><code>SELECT col1, col2
  FROM ft_test1
  WHERE CONTAINS(col2, '"*oh*"')  
  </code></pre></li>
  </ol>
  
  <p>No output.  </p>
  
  <ol start="2">
  <li><p>Using FULLTEXT Index: <code>FREETEXT</code> </p>
  
  <pre><code>SELECT col1, col2
  FROM ft_test1
  WHERE FREETEXT(col2, '"*oh*"')  
  </code></pre></li>
  </ol>
  
  <p>No output.</p>
  
  <ol start="3">
  <li><p>Using Pattern Index: <code>PATINDEX</code></p>
  
  <pre><code>SELECT col1, col2
  FROM ft_test1
  WHERE PATINDEX('%oh%',col2)&gt;0
  </code></pre></li>
  </ol>
  
  <p>Got an output.      </p>
  
  <p>Execution Plan: <code>Table Scan</code> </p>
  
  <ol start="4">
  <li><p>Using character expression: <code>CHARINDEX</code></p>
  
  <pre><code>SELECT col1, col2
  FROM ft_test1
  WHERE CHARINDEX('oh',col2)&gt;0
  </code></pre></li>
  </ol>
  
  <p>Got an output.  </p>
  
  <p>Execution Plan: <code>Table Scan</code>    </p>
  
  <ol start="5">
  <li><p>Using <code>LIKE</code>.</p>
  
  <pre><code>SELECT col1, col2
  FROM ft_test1
  WHERE col2 LIKE '%oh%'   
  </code></pre></li>
  </ol>
  
  <p>Got an output.  </p>
  
  <p>Execution Plan: <code>Table Scan</code>    </p>