Index Seek vs. Clustered Index Scan - Why is the scan chosen?

sql-server

Solution

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?

The query below does not know the values of the parameters when building the plan and assumes that in general, the clustered index scan is better.

And, more importantly, what I can do to get SQL Server to use the index on the `LastModifiedTime` column, without using an index hint.

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'
OPTION (OPTIMIZE FOR (@LastModifiedTimeStart = '3/3/2010', @LastModifiedTimeEnd = '3/4/2010'))

Alternatively, you can add `OPTION (RECOMPILE)`, which will create the different execution plan each time the query is run, taking the parameter values into the account (parameter sniffing).

This, however, does not guarantee that the index will be used.

Problem

The following query uses an index seek on an index on the LastModifiedTime column. ``` 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' ``` 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, without using an index hint. ``` 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' ```

Original source