User Defined Function performance - how to quantify

[SBendBuckeye]

Hello all,

We don't run a lot of transactions through our system so I don't know how to quantify a recent lunch discussion among our developers. The discussion pertained to avoiding functions in Where clauses where they potentially have to be evaluated over and over.

For purposes of discussion, assume the following two situations, Case 1 where the function result is stored into a local variable and thus is run once and Case 2 where the function is actually included in the Where clause.

-- Case 1
declare @lGuid uniqueidentifier
set @lGuid = [dbo].[GetGuidFromFunction]()

-- Now use local variable value in Where clause
select * 
from ViewName
where ShapeTypeID = @lGuid

-- Case 2 - Use function call in Where clause
select * 
from ViewName
where ShapeTypeID = [dbo].[GetGuidFromFunction]()

Presumably, the cost differential between the two cases will be approximately linear based on the number of rows being processed. Can someone give me some kind of a ration between the two? Also, with compiler optimizations, inlining, etc, is this even a significant issue?

If it makes a difference, we are running Sql Server 2005. Thanks in advance for any ideas and/or suggestions!

Have a great day!

j2associatesNO_SPAM@gmail.com