Matt Miller (2/18/2008)
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).
That's exactly my point - the question is too simplistic and not specific enough. It's asking for a simple answer to a question that's far more complex than the question would have us believe. Type of UDF and determinism factor into the question significantly. While it's probably not all that common to invoke a UDF using the Function(Constant) form, it's not uncommon to see a UDF being invoked using the Function(@Variable) form. The question seems to imply that Function(Column_Name) is the only form available and that the UDF in question is nondeterministic, which are both bad assumptions IMHO.
BTW, my friend that I blogged about (who's currently interviewing for jobs) was asked this very question a couple of days ago. I can only assume the interviewer got it from here 🙁