CODEWITHSUNDEEP
sql-serveroptimization
David Ferraro
David Ferraro

Reputation: 1

SQL Server Optimization: Function in SELECT calculated before or after WHERE Clause?

I was just wondering if in SQL Server if in a statement like this:

SELECT A.Field1, dbo.someFunction(A.IdentifierID) As Field 
FROM Table A WHERE A.IdentifierID = 1000

Will it call someFunction for all the rows in the table, or will it call it once?

Thanks!

Upvotes: 0

Views: 279

Answers (5)

Maghraby
Maghraby

Reputation: 11

the count of calls depends on the resulted rows, i.e, if the output of the query 100 row the it will be called 100 times, not for all rows in the table

but if it were in the where clause, the it will evaluated for each row

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95612

Compliant SQL servers are supposed to appear as if they apply the WHERE clause before they apply the SELECT clause. So it should appear to evaluate the function at most once for each row that satisfies WHERE A.IdentifierID = 1000.

But database engines are free to optimize however they like, as long as they give you the same results the standards require. So in your case, since you're selecting a single ID number, it might only have to evaluate the function once.

Upvotes: 0

n8wrl
n8wrl

Reputation: 19765

As vlad commented you should try it and see. There are of course two options:

  1. It could call it just once knowing IdentifierID will always be 1000.

but that might not be the right thing...

  1. It could call it for each row - maybe the function has side-effects?

I strongly suspect #2.

Upvotes: 0

rabudde
rabudde

Reputation: 7722

It will be called for every row of result

Upvotes: 3

Icarus
Icarus

Reputation: 63966

Will call it for every row, but since you are selecting a specific one (or so it seems), it will be called once on your example.

Upvotes: 0

Related Questions