Matt Marston (2/13/2008)
-- This will only call the function once
SELECT * FROM sysobjects WHERE [id] <= dbo.fnDateAdd(object_id('dbo.fnDateAdd'))
-- This will call the function for every row in sysobjects.
SELECT * FROM sysobjects WHERE [id] <= dbo.fnDateAdd([id])
True - but that's because one of them is taking in a scalar value as input (as in -
object_id('dbo.fnDateAdd') is going to return the same value no matter how many times you run it), and one of them is taking in a parameter that changes for each row. It really has nothing to do with the determinism of fnDateAdd.
In general - if the optimizer detects that the inputs to a scalar function are static, then the function is evaluated once, and used as a scalar value. Look at rand() or getdate() when applied to a set. The only thing I can think of that doesn't follow that pattern is NEWID() (and I suppose its companion NEWSEQUENTIALID()).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?