SQL where 子句中的函数调用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1724325/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Function call in where clause
提问by noob.spt
I have a query as below:
我有一个查询如下：
SELECT * FROM Members (NOLOCK)
WHERE Phone= dbo.FormatPhone(@Phone)
Now here I understand that formatting has to be applied on the variable on column. But should I apply it on variable to assign to some other local variable then use it (as below).
现在我明白必须对列上的变量应用格式。但是我应该将它应用于变量以分配给其他一些局部变量然后使用它（如下所示）。
Set @SomeVar = dbo.FormatPhone(@Phone)
SELECT *
FROM Members (NOLOCK) WHERE Phone= @SomeVar
Which way is better or both are good?
哪种方式更好或两者都好？
EDIT: And how is first query different from
编辑：第一个查询与
SELECT * FROM Members (NOLOCK)
WHERE dbo.FormatPhone(Phone) = @Phone
回答by Remus Rusanu
As usual with SQL, the query is largely irelevant without knowing the actual schema is used against.
与 SQL 一样，在不知道实际使用的模式的情况下，查询在很大程度上是无关紧要的。
Do you have an index on Members.Phone? If no, then it makes no difference how you write the query, they all gonna scan the whole table and performe the same (ie. perform badly). If you do have an indexthen the way you write the query makes all the difference:
你有关于 Member.Phone 的索引吗？如果没有，那么您编写查询的方式没有区别，它们都会扫描整个表并执行相同的操作（即执行不佳）。如果您确实有索引，那么您编写查询的方式就会大不相同：
SELECT * FROM Members WHERE Phone= @Phone;
SELECT * FROM Members WHERE Phone= dbo.FormatPhone(@Phone);
SELECT * FROM Members WHERE dbo.FormatPhone(Phone)=@Phone;
First query is guaranteed optimal, will seek the phone on the index.
Second query depends on the characteristics of the dbo.FormatPhone. It may or may not use an optimal seek.
Last query is guaranteed to be bad. Will scan the table.
第一次查询保证最优，会在索引上查找电话。
第二个查询取决于 dbo.FormatPhone 的特性。它可能会或可能不会使用最佳搜索。
最后一个查询肯定是错误的。将扫描表。
Also, I removed the NOLOCK hint, it seem the theme of the day... See syntax for nolock in sql. NOLOCK is alwaysthe wrong answer. Use snapshot isolation.
另外，我删除了 NOLOCK 提示，这似乎是当天的主题...请参阅sql 中 nolock 的语法。NOLOCK总是错误的答案。使用快照隔离。
回答by boydc7
You'll almost certainly get better predictability if you assign to a variable first, lots of dependency in the optimizer around determinism vs. non-determinism.
如果您首先分配给变量，那么您几乎肯定会获得更好的可预测性，优化器中围绕确定性与非确定性的大量依赖性。
回答by Dan Sydner
The second is definitely preferred. The first one will evaluate the function for each row in the table, whilst the other one will do the calculation only once.
第二个绝对是首选。第一个将评估表中每一行的函数，而另一个将只计算一次。
回答by Radhi
SELECT * FROM Members (NOLOCK)
WHERE Phone= dbo.FormatPhone(@Phone)
in the query above, function dbo.FormatPhonewill be executed for every row in Memberstable.
在上面的查询中，function dbo.FormatPhone将对Members表中的每一行执行。
when second query
第二次查询时
Set @SomeVar = dbo.FormatPhone(@Phone)
SELECT *
FROM Members (NOLOCK) WHERE Phone= @SomeVar
it'll execute the function only once. So I think second option will be faster in case you have large data in member table.
它只会执行一次该函数。所以我认为如果成员表中有大量数据，第二个选项会更快。
