title: Date Parameter causing Clustered Index Scan
body: <p>I have the following query</p>
  
  <pre><code>DECLARE @StartDate DATE = '2017-09-22'
  DECLARE @EndDate DATE = '2017-09-23'
  
  SELECT a.col1,
         a.col2,
         b.col1,
         b.col2,
         b.col3,
         a.col3
  FROM   TableA a
         JOIN TableB b
             ON b.pred = a.pred
  WHERE  b.col2 &gt; @StartDate AND b.col2 &lt; @EndDate
  </code></pre>
  
  <p>When I run this and inspect the actual execution plan, I can see that the most costly operator is a clustered index scan (The index is on a.pred)</p>
  
  <p>However, if I change the query as follows</p>
  
  <pre><code>SELECT a.col1,
         a.col2,
         b.col1,
         b.col2,
         b.col3,
         a.col3
  FROM   TableA a
         JOIN TableB b
             ON b.pred = a.pred
  WHERE  b.col2 &gt; '2017-09-22' AND b.col2 &lt; '2017-09-23'
  </code></pre>
  
  <p>The index scan is eliminated and an index seek is used.</p>
  
  <p>Can someone explain why this is? In my mind, this is something to do with the fact that the value in the variable could be anything so SQL doesn't know how plan the execution.</p>
  
  <p>Is there any way I can eliminate the table scan but still have use of the variable? (PS, this will be converted to a stored procedure which takes @StartDate and @EndDate as parameters)</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>col2 is DATETIME, however, if I make my variable DATETIME the problem still persists</p>