CODEWITHSUNDEEP
sqlsql-serversql-server-2005performance
noob.spt
noob.spt

Reputation: 981

Function call in where clause

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

Upvotes: 10

Views: 51641

Answers (4)

Radhi
Radhi

Reputation: 6339

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

in the query above, function dbo.FormatPhone will be executed for every row in Members table.

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.

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294237

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.

Upvotes: 10

Dan Sydner
Dan Sydner

Reputation: 448

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.

Upvotes: 5

boydc7
boydc7

Reputation: 4633

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.

Upvotes: 5

Related Questions