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

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.SubAssembly
    FROM dbo.Accepts a WITH (NOLOCK)
    WHERE  a.LastModifiedTime BETWEEN @LastModifiedTimeStart And @LastModifiedTimeEnd
    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.

SELECT 
      CONVERT(varchar,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),它将在每次运行查询时创建不同的执行计划,将参数值带入帐户(参数嗅探).

但是,这并不能保证将使用索引.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!