导致聚集索引扫描的日期参数

Question

我有以下查询

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 > @StartDate AND b.col2 < @EndDate

当我运行这个并检查实际的执行计划时，我可以看到最昂贵的操作是集群索引扫描(索引在a.pred上)

但是，如果我将查询更改为以下

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 > '2017-09-22' AND b.col2 < '2017-09-23'

索引扫描被消除，并且索引搜索被使用。

有人能解释一下为什么会这样吗？在我看来，这与变量中的值可以是任何值有关，因此SQL不知道如何计划执行。

有没有什么方法可以消除表扫描，但仍然可以使用变量？(PS，这将转换为以@StartDate和@EndDate为参数的存储过程)

编辑

col2是DATETIME，但是，如果我使我的变量DATETIME的问题仍然存在

Answer 1

SQL使计划可用于变量。

当您使用变量时，它在不知道您将传递的实际值的情况下编译查询。即使在这个sql batch中，值也是已知的但是不需要为另一组传递参数重新编译查询。

因此，如果您对值进行硬编码-DB编译它，它会选择针对这些特定值优化的计划(例如，它猜测通过日期检查的预期行数)。它至少不会比使用变量时更糟糕。但DB需要为另一组硬编码值重新编译它(因为查询的文本发生了更改)，这需要时间，并且占用了compiled query cache存储来取代其他有用的查询。

截止日期：

有什么方法可以消除表扫描，但仍然可以使用变量吗？(PS，这将转换为以@StartDate和@EndDate为参数的存储过程)

我认为b.col2上的非聚集索引可能是解决方案。该索引的密钥也可以包含b.pred作为代理密钥的一部分或包含(with include(pred))。

Answer 2

这个查询有变量，这个问题中关于sql server不知道变量的值，因此必须根据猜测的结果集大小制定计划的建议与你的问题有关。

当WHERE子句包含参数化值时，为什么SQL Server使用索引扫描而不是索引查找

但是，您提到要将此代码转换为存储过程。在将其转换为存储过程时，查询优化器应该能够探查变量的值，并从中制定和执行计划。请尝试将它转换为存储过程并执行它。在这种情况下，查询计划应该会有所改进。