Matt Marston (2/14/2008)
However, I do stand corrected, that in practice, it appears that SQL Server (at least when I test it on SQL Server 2005) also does further optimizatition depending on which clause the function is used in. In the WHERE clause (as shown in my previous example) it only depends on condition 1 (do the parameter values change?) and not on condition 2 (is it deterministic?). But the following example shows that elsewhere (at least in the SELECT clause) these 2 conditions must hold true. I tested on SQL Server 2005 and verified using SQL Profiler watching the SP:StmtStarting event.
Except that rule doesn't work in this case:
select getdate() from sys.all_columns
We all know that getdate() is non-deterministic. But still - you will find that you get a repeating SINGLE VALUE down the line (run that with distinct if you want to see what I mean. Perhaps the rule needs to be restricted to user-defined only.
----------------------------------------------------------------------------------
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?