Matt Miller (2/14/2008)
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.
If something is going to return the same value no matter how many times you run it, then it is, by definition deterministic. So it does depend on the determinism of the function. As I stated in my original post, the number of times a function gets evaluated depends on 1) whether it gets called with the same values or with changing values and 2) whether it deterministic.
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.
CREATE FUNCTION dbo.fnDateAdd(@intA int)
RETURNS int
--WITH SCHEMABINDING
AS
BEGIN
RETURN @intA
END
GO
-- This should return 0
SELECT objectpropertyex(object_id('dbo.fnDateAdd'), N'IsDeterministic')
-- This will call the function multiple even though the parameter
-- values do not change since the function is not deterministic
SELECT *, dbo.fnDateAdd(object_id('dbo.fnDateAdd')) FROM sysobjects
GO
ALTER FUNCTION dbo.fnDateAdd(@intA int)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
RETURN @intA
END
GO
-- This should return 1
SELECT objectpropertyex(object_id('dbo.fnDateAdd'), N'IsDeterministic')
-- This will call the function once since the parameter
-- values do not change and the function is deterministic
SELECT *, dbo.fnDateAdd(object_id('dbo.fnDateAdd')) FROM sysobjects