title: "Between" operator generating bad query plan when using parameters
body: <p>I have a simple dates table (Date, DateID) which contains a list of dates between 1-Jan-1900 and 31-Dec-2100. </p>
  
  <p>When selecting from the table using the <code>between</code> operator and hard-coded parameter values, I get a correct query plan with 3 estimated rows compared to 2 actual rows:</p>
  
  <pre><code>select v.Date from Dates v
  where v.Date between '20130128' and '20130129';
  </code></pre>
  
  <p>However when replacing the hard-coded values with parameters, the query plan changes to a very poor plan, with over 6000 estimated rows and only 2 actual rows:</p>
  
  <pre><code>select v.Date from Dates v
  where v.Date between @startdate and @enddate;
  </code></pre>
  
  <p>The query plans themselves are identical, it's just the difference in estimated rows that is causing the parameterised query to run about 4 times slower than the hard-coded query. Is there anything I'm missing as to why the parameterised version runs so much slower, and what indexes/hints can I give SQL Server to help it use the correct query plan?</p>
  
  <p>Some additional info: </p>
  
  <ul>
  <li>The problem does not arise when using simple equality <code>=</code> criteria, it seems specific to the <code>between</code> operator.</li>
  <li>If I add <code>option(recompile)</code> to the end of the parameterised query, I get a perfect query plan, identical to the hard-coded query.</li>
  <li>The dates table has only two columns, Date and DateID, with a clustered index on the primary key DateID column, and a unique non-clustered index on the Date column. All have updated statistics.</li>
  <li>The query plan performs automatic parameterisation for the hard-coded query, replacing the hard-coded values with @1 and @2, and displaying the query as uppercase. It doesn't appear to perform any transformation for the parameterised query. </li>
  <li>Using SQL Server 2008 R2.</li>
  </ul>
  
  <p>I know enough to <strike>realise</strike> <em>suspect</em> that this is some sort of parameter sniffing problem. Why not add <code>option(recompile)</code> to the query? This is being used as part of a much larger complex query and I understand good practice is to let SQL Server do its thing and re-use query plans from the cache where possible. </p>
  
  <p>Edit and update: thanks for the thoughtful responses so far. To refine the question further, the query plan uses a perfectly good index for both of the above queries, but why does it not recognise that the date range is only two days wide for the parameterised query, why instead does it think the range is 6000 rows wide? Especially when, looking at the query plan, SQL Server is performing automatic parameterisation for the hard-coded query anyway?? In the underlying query plan, both plans look identical in that they both are parameterised!</p>