Function call in where clause

sql sql-server sql-server-2005 performance

40,729

Solution 1

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

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;

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.

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.

Solution 2

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.

Solution 3

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.

40,729

Author by

noob.spt

Updated on July 09, 2022

Comments

noob.spt almost 2 years
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
```
Abdul Saqib over 14 years

In addition the first option most likely won't be able to use an index.
noob.spt over 14 years

The function evaluation for every row will be there if we apply function on table column or variable? Or both ways are same?
Adir D over 14 years

No, if you perform the calculation up front, you are essentially handing the query a constant.
noob.spt over 14 years

You mean these two queries are same? SELECT * FROM Members (NOLOCK) WHERE Phone= dbo.FormatPhone(@Phone) SELECT * FROM Members (NOLOCK) WHERE dbo.FormatPhone(Phone) = @Phone
Tom H over 14 years

No, those two queries are not the same. Doing "WHERE Phone = dbo.FormatPhone(@Phone)" and "WHERE Phone = @FormattedPhone" should be the same though.
Tom H over 14 years

Dan - the optimizer should not evaluate the function for each row in the first query - "WHERE Phone = dbo.FormatPhone(@Phone)"
noob.spt over 14 years

Thanks Tom. I agree with you, but not 100% sure.
noob.spt over 14 years

Tom could you post your comment as a solution. Thanks.
noob.spt over 14 years

I went through the post and it talks about not using NOLOCK with Update/Insert statements. I don't see why its a problem here?
noob.spt over 14 years

Do you mean use with(nolock) instead of NOLOCK?
Remus Rusanu over 14 years

Do you intent to read uncommited data? Or did you add NOLOCK as a workaround for concurency issues?
noob.spt over 14 years

Concurrency is not an issue in this case, as long as I can finish the job in a snap.
noob.spt over 14 years

Thanks vrunda. I am getting different responses and opinion here, though I don't believe that function will execute for every row in first query. DO you have any references which prove it?