How to compare performance of two versions of a function? I just saw this function definition:

create function dbo.f (@a int,  @b int)  
returns integer
as  
begin 
return  case when 
        not exists (Select * from t1 where t1.col1 = @a)
        AND @b &gt; 0
        then 1 else 0 end
end
GO


Seeing a not exists I thought attention full table scan and tried to improve it

create function dbo.f (@a int,  @b int)  
returns integer
as  
begin 
return  case when 
        exists (Select * from t1 where t1.col1 = @a)
        OR @b &gt; 0
        then 0 else 1 end
end

GO


My feeling is, that this transformation could have been done by an optimizer. It seems to be straight forward, but how can I be sure if he does?

Comment on Igor's answer:
(comparison fixed thanks to Matts comment)

This inspires me to the following:

create function dbo.f (@a int,  @b int)  
returns integer
as  
begin 
 IF @b &lt;= 0
   RETURN 0

 IF exists (Select * from t1 where t1.col1 = @a)
   RETURN 0

  RETURN 1
end
GO
One thing you could do to compare these two versions is to rename one of them to f_old and execute them both inside the same batch, while including the actual execution plan and IO statistics.  From there, it is a simple matter of comparing the plans and IO stats of each.

One thing you need to be wary of is that in your first statement you had IF @b > 0.  In your second statement you have IF @b &lt; 0.  What happens if @b = 0?  You have not covered that possibility.

Finally, I imagine you will find that your thoughts regarding NOT EXISTS vs. EXISTS is correct.  EXISTS will process until the condition is met.  NOT EXISTS requires a table scan (if not indexed) and an index scan (if indexed).  On a small table, you will not see a huge performance gain, but it is always best to design your logic around EXISTS, as opposed to NOT EXISTS.

Matt