title: Index Seek vs. Clustered Index Scan - Why is the scan chosen?
body: <p>The following query uses an index seek on an index on the LastModifiedTime column.</p>
  
  <pre><code>SELECT 
        CONVERT(varchar, a.ReadTime, 101) as ReadDate,
        a.SubID,
        a.PlantID,
        a.Unit as UnitID,
        a.SubAssembly
  FROM dbo.Accepts a WITH (NOLOCK)
  WHERE  a.LastModifiedTime BETWEEN '3/3/2010' And '3/4/2010'
  AND a.SubAssembly = '400'
  </code></pre>
  
  <p>The query below, which is almost identical to the above query, uses a clustered index scan, instead of the index on LastModifiedTime. Can anyone tell me why? And, more importantly, what I can do to get SQL Server to use the index on the LastModifiedTime column, <strong>without</strong> using an index hint.</p>
  
  <pre><code>Declare @LastModifiedTimeEnd dateTime
  Declare @LastModifiedTimeStart dateTime
  
      SELECT 
            CONVERT(varchar, a.ReadTime, 101) as ReadDate,
            a.SubID,
            a.PlantID,
            a.Unit as UnitID,
            a.SubAssembly
      FROM dbo.Accepts a WITH (NOLOCK)
      WHERE  a.LastModifiedTime BETWEEN @LastModifiedTimeStart And @LastModifiedTimeEnd
      AND a.SubAssembly = '400'
  </code></pre>