sql-server - 索引查找与聚集索引扫描 - 为什么选择扫描？

Question

以下查询对 LastModifiedTime 列的索引使用索引查找。

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'

下面的查询与上面的查询几乎相同，它使用聚集索引扫描，而不是 LastModifiedTime 上的索引。谁能告诉我为什么？而且，更重要的是，我可以做些什么来让 SQL Server 使用 LastModifiedTime 列上的索引， 没有 使用索引提示。

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'

Answer 1

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.

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'))

或者，您可以添加 OPTION (RECOMPILE) ，它会在每次运行查询时创建不同的执行计划，将参数值纳入账户(参数嗅探)。

但是，这并不能保证索引会被使用。