title: Query with LIKE, increasingly slow with a smaller resultset
body: <p>Say I have a Person table with 200000 records, there's a clustered index on it's GUID primary key. This GUID is generated using the NEWSEQUENTIALID() construct provided by SQL Server (2008 R2). Furthermore there is a regular index on the LastName (varchar(256)) column.</p>
  
  <p>For every record I've generated a unique name (Lastname_1 through Lastname_200000), now I'm playing around with some queries and have come to find that the more restrictive my criteria is, the slower SQL Server will return actual results. And this performance implication is quite severe.</p>
  
  <p>E.g.: </p>
  
  <pre><code>SELECT * FROM Person WHERE Lastname LIKE '%Lastname_123456%'
  </code></pre>
  
  <p>Is much slower than</p>
  
  <pre><code>SELECT * FROM Person WHERE Lastname LIKE '%Lastname_123%'
  </code></pre>
  
  <p>Responsetimes are measured by setting statistics on:</p>
  
  <pre><code>SET STATISTICS TIME ON
  </code></pre>
  
  <p>I can imagine this being caused </p>
  
  <p>1) Because of the LIKE clause itself, since it starts with % it isn't possible to use the inde on that particular column,</p>
  
  <p>2) SQL having to think more about my 'bigger question'.</p>
  
  <p>Is there any truth in this? Is there some way to avoid this?</p>
  
  <p>Edit:
  To add some context to this question, this is part of a use case for a 'free search'. I would very much like the system to be fast when a user enters a full lastname.</p>
  
  <p>How should I make these cases perform? Should I avoid the '%xxx%' construction and go for 'xxx%' like construction? Which does add alot of speed, but at the cost of some flexibility for the user...</p>