Matt Miller (2/14/2008)
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.
Yes, the "rule" is regarding user-defined functions. And it is really more of a generalization than a rule. It is basically trying to predict optimizations that SQL Server will do. GetDate() is a built-in system function that behaves differently than user defined functions. The topic of converstion here is UDFs. Try that with an equivalent UDF.
CREATE FUNCTION dbo.fnGetDate()
RETURNS datetime
AS
BEGIN
RETURN GetDate();
END
GO
select dbo.fnGetDate() as dte
into #temp
from sys.all_columns
-- notice distinct values, demonstrating that the function is called multiple times
select distinct dte from #temp
Regarding my previous example, did you run profiler with the SP:StmtStarting event selected? That is the only event necessary to see the behavior. When I run the select statement with the "non-deterministic" version (I put that in quotes because it really is determinstic but the lack of SCHEMABINDING leads SQL Server to identify it as non-deterministic) I see an SP:StmtStarting event for every row with TextData "RETURN @intA". When using the determinstic version (WITH SCHEMABINDING) the SP:StmtStarting with TextData "RETURN @intA" is only fired once (it helps to clear the profiler output in between).