为什么在MS Sql server的Where子句中使用CASE语句

Doing some performance tuning on a 3rd party vendor system in MS SQL Server 2012.

在MS SQL Server 2012中的第三方供应商系统上做一些性能调优。

They do a lot of where clauses like this:

他们在很多地方都有这样的条款:

WHERE 
(
   CASE
       WHEN  @searchID = '' THEN 1
       WHEN ((C.SAPCustomerNumber = @searchID ) OR (C.SAPCustomerNumber = @SapID)) THEN 1
    ELSE 0
    END = 1
                )

Other than confusing the query plan, what advantage if any would there be to Case 1 or 0 in the where clause?

除了混淆查询计划之外，如果在where子句中有1或0的情况，还有什么优势呢?

Thanks

谢谢

1 个解决方案

#1

The most common, by far, cause for this sort of code to appear is someone who is new to SQL, CASE expressions or both. They somehow become fixated on CASE and decide that it should be used for all conditional evaluations.

到目前为止，出现这种代码的最常见原因是SQL、CASE表达式或两者都不熟悉的人。他们以某种方式专注于案例，并决定它应该用于所有的条件评估。

This is usually a mistake and can be replaced with simpler boolean logical, as @Bogdan suggested in the comments:

这通常是一个错误，可以用更简单的布尔逻辑替换，正如@Bogdan在评论中所建议的:

((C.SAPCustomerNumber = @searchID ) OR (C.SAPCustomerNumber = @SapID) OR (@searchID = '' ))

The second reason this can be done is if someone is attempting to enforce the order of evaluation of predicates¹. CASE is documented to evaluate its WHEN conditions in order (provided that they are scalar expressions, not aggregates). I'd seriously not recommend that anyone actually writes code like this though - it's easily mistaken for the first form instead. And even if a particular evaluation order is best today, with today's data, who's to say whether it will still be correct tomorrow, or in a month or years time?

可以这样做的第二个原因是如果有人试图强制执行谓词1的求值顺序。CASE被文档化以按顺序评估它的条件(前提是它们是标量表达式，而不是聚合)。但我不建议任何人真正编写这样的代码——它很容易被误认为是第一种形式。即使一个特定的评估顺序是最好的今天，用今天的数据，谁说明天还是正确的，还是一个月或几年?

¹SQL does not guarantee any evaluation order for WHERE clause predicates in general, nor any form of short-circuiting evaluation. The optimizer is generally free to re-order predicates - both within the WHERE clause and in JOIN/ON clauses - to try to achieve the overall result as cheaply as possible. You shouldn't, generally, try to prevent it from doing so.

1SQL不保证一般WHERE子句谓词的任何评估顺序，也不保证任何形式的短路评估。优化器通常可以在WHERE子句和JOIN/ON子句中*地重新排序谓词，以尽可能便宜地实现总体结果。一般来说，你不应该试图阻止它。

If it's not picking the most efficient plan, this is far better fixed by updating/creating indexes and statistics or actually forcing a specific plan, rather than using CASE.

如果它没有选择最有效的计划，那么通过更新/创建索引和统计数据，或者实际上强制一个特定的计划，而不是用例，这就更好了。

#1