where 子句中的函数调用 [英] Function call in where clause

问题描述

我有一个查询如下:

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?

第一个查询与

SELECT * FROM Members (NOLOCK) 
 WHERE dbo.FormatPhone(Phone) = @Phone

推荐答案

与 SQL 一样，在不知道实际使用的架构的情况下，查询在很大程度上是无关紧要的.

As usual with SQL, the query is largely irelevant without knowing the actual schema is used against.

你有关于 Members.Phone 的索引吗?如果没有，那么您编写查询的方式没有区别，它们都会扫描整个表并执行相同的操作(即执行不佳).如果您确实有索引，那么您编写查询的方式就会大不相同:

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 index then the way you write the query makes all the difference:

SELECT * FROM Members WHERE Phone= @Phone;
SELECT * FROM Members WHERE Phone= dbo.FormatPhone(@Phone);
SELECT * FROM Members WHERE  dbo.FormatPhone(Phone)=@Phone;

第一次查询保证最优，会在索引上寻找电话.
第二个查询取决于 dbo.FormatPhone 的特性.它可能会也可能不会使用最佳搜索.
最后一个查询肯定是错误的.将扫描表.

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.

此外，我删除了 NOLOCK 提示，这似乎是当天的主题...参见 sql 中 nolock 的语法.NOLOCK总是错误的答案.使用快照隔离.

Also, I removed the NOLOCK hint, it seem the theme of the day... See syntax for nolock in sql. NOLOCK is always the wrong answer. Use snapshot isolation.

这篇关于where 子句中的函数调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！