Mike C (2/18/2008)
What if your UDF is a scalar detereministic UDF with a constant value passed in? How many times is this UDF executed:
CREATE FUNCTION dbo.AbsoluteValue (@i INT)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
RETURN ABS(@i);
END;
GO
SELECT dbo.AbsoluteValue(-10)
FROM sys.columns;
GO
If SQL Server is executing a deterministic UDF with a constant parameter hundreds or thousands of times, it is seriously wasting resources.
From what it looks like (based on the logical reads you see in Profiler), if you go to the trouble of marking it as deterministic (by using the WITH SCHEMABINDING), it does seem to treat it like a constant (meaning - evaluate once and pollinate once per row). But now you get to enter the world of confusing behavior: if you DON'T mark it using WITH SCHEMABINDING, the execution optimizer seems to not know that it is deterministic, and will re-evaluate it once per row. However - it DOES know that it's deterministic if you decide to call that from within another function (in 2000, since that was where it cared about deterministic components in functions).
----------------------------------------------------------------------------------
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?