SQL Server、索引和参数的特殊情况
我有一个表，我们将其称为History。主键（又名聚集索引）称为HIST_ID。该表在开发数据库中约有 2300 行。现在考虑以下两个查询：
查询 1：
declare @x int
set @x = 14289
select * from History where hist_id=@x
查询 2：
declare @x int
set @x = 14289
select * from History where hist_id=@x or @x is null
唯一的区别是末尾的 或 @x is null。然而，第一个查询执行索引查找，第二个查询执行索引扫描。什么给？
先发制人的回复 - 不，选项（重新编译）没有帮助。
补充：我想要一些有说服力的事实，而不是猜测。我自己可以猜测出十几个可能的原因。但这里真正的问题是什么？
I have a table, let's call it History. The primary key (aka Clustered Index) is called HIST_ID. The table has some 2300 rows in the development DB. Now consider the following two queries:
Query 1:
declare @x int
set @x = 14289
select * from History where hist_id=@x
Query 2:
declare @x int
set @x = 14289
select * from History where hist_id=@x or @x is null
The only difference is the or @x is null at the end. However the first query does an index seek, the second - index scan. What gives?
Pre-emptive reply - no, option(recompile) doesn't help.
Added: I'd like some solid argumented facts, not guesses. I can guess a dozen possible reasons for this myself. But what is the real problem here?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我建议该计划与传入/使用的参数分开生成，因此本质上需要返回每一行（取决于 @x 的值）。因此，查询计划正在处理它可以接收的参数的最坏情况。
例如，如果@x 的输入为空，则查询将被迫返回每一行，因为每一行都满足始终返回true 的文字方程/谓词。为了使查询计划覆盖 @x 的每个值，它必须生成一个执行扫描的计划。
I would suggest that the plan is being produced separate to the parameter being passed in / used, so in essence there is a requirement (depending on the value of @x) to return every row. As such the query plan is dealing with it's worst case scenario of the parameters it can receive.
e.g. If the input for @x was null, then the query would be forced to return every row since every row would satisfy a literal equation / predicate that always returned true. For the query plan to cover every value of @x, it must generate a plan that performs a scan.
当然这是索引扫描。
聚集索引扫描 = 表扫描，因为您没有“@x IS NULL”的合理谓词。
参数化的缓存计划是通用的，适用于 @x = NULL 或 @x = value。
如果您没有定义@x，您应该得到相同的计划。
如果您编码“12345 IS NULL”，则会检测到并忽略它。
我找不到关于如何在查询计划中处理常量的博客文章。要点是它们是通用的，并且不会发生短路而允许计划重用。
Of course it's an index scan.
A clustered index scan = table scan because you have no sensible predicate for "@x IS NULL".
The parameterised, cached plan does is general and will work for @x = NULL or @x = value.
If you don't define @x, you should get the same plan.
If you coded "12345 IS NULL" then this is detected and ignored.
I can't find an blog article on how constants are treated in query plans. The gist is that they are generalised and short circuits don't happen to allow plan reuse.
我猜优化器认为它是有益的。另一种方法是使用与您编写的计划相同的计划
You can rewrite the query in this way，但我很确定优化器能够自行完成此操作。您有多少个空值？
编辑：原来我误读了这个问题，并认为你想要在哪里（@x = hist_id OR hist_id 为空）。事实上，您需要一个动态标准。请查看这篇文章。请注意，如果您指定了WITH(RECOMPILE)，这种查询应该可以在 SQL2k8 中工作，但是由于一个严重的错误，这种支持被删除了。
I guess the optimizer determines it is beneficial. The alternative would be to use the same plan as if you had written
You can rewrite the query in this way, but I'm pretty sure the optimizer is capable of doing this by itself. How many null values do you have?
Edit: Turns out I misread the question and thought you wanted WHERE (@x = hist_id OR hist_id is null). In fact you want a dynamic criterion. Check out this article. Note that this your kind of query was supposed to work in SQL2k8 if you specify WITH(RECOMPILE), but due to a nasty bug this support was removed.