• 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?