February 13, 2008 at 1:49 pm
Hm, I thought I had tried the SCHEMABINDING before already but that really works now.
I have also tried to use a non-deterministic function within a function - that actually is allowed:
DROP FUNCTION dbo.fnDateAdd
GO
CREATE FUNCTION dbo.fnDateAdd(@intA int)
RETURNS datetime
WITH SCHEMABINDING AS
BEGIN
RETURN GETDATE()
END
GO
DROP FUNCTION dbo.Test
GO
CREATE FUNCTION dbo.Test()
RETURNS datetime
AS
BEGIN
RETURN dbo.fnDateAdd(1)
END
GO
SELECT dbo.Test()
Since were not updating any data (no side-effects) this seems to be fine.
Thanks for your hints, I was really lost:)
Best Regards,
Chris Büttner
February 13, 2008 at 1:53 pm
Christian Buettner (2/13/2008)
Hm, I thought I had tried the SCHEMABINDING before already but that really works now.I have also tried to use a non-deterministic function within a function - that actually is allowed:
..In 2005 and later. Try that in 2000 and you won't get past the getdate(). 2005 mollified the "must be deterministic" rule.
sorry - should have specified that.
----------------------------------------------------------------------------------
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?
February 13, 2008 at 2:21 pm
Ah, good to know! Thanks for the heads up!
Best Regards,
Chris Büttner
February 14, 2008 at 5:43 am
Wow, this is quite a thread.
Check the references section on the question and you'll see where I got the idea for it. I suppose I should have checked up on my source, but the information in the training kit actually made sense to me, so I thought it'd make a neat Question of the Day.
I didn't take into account CROSS APPLY which I've never used.
My apologies for the poorly worded question, but I'm still glad I posted it. I'm learning all sorts of things from this discussion. The primary thing being even Microsoft Press authors don't apparently get this stuff right. @=)
February 14, 2008 at 8:38 am
Matt Marston,
Thanks for the excellent examples on how the usage of the function can make a big difference! Definitely something I will keep in mind when building a query.
February 14, 2008 at 8:54 am
I always reckon it's a good QotD if I learn something either from it or from the resulting discussion. In this case, I need to go off and look at functions defined WITH SCHEMABINDING.
Hence it was a good question to raise even if the answer was not so clear ;).
Thanks for putting it up.
Derek.
Derek
February 14, 2008 at 9:01 am
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.
In general - if the optimizer detects that the inputs to a scalar function are static, then the function is evaluated once, and used as a scalar value. Look at rand() or getdate() when applied to a set. The only thing I can think of that doesn't follow that pattern is NEWID() (and I suppose its companion NEWSEQUENTIALID()).
----------------------------------------------------------------------------------
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?
February 14, 2008 at 10:01 am
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
February 14, 2008 at 11:33 am
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?
February 14, 2008 at 11:44 am
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).
February 14, 2008 at 11:49 am
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).
I did. It was a good point. Of course - I needed to run it on something bigger to see it (the differences were so small I was chalking that up to sampling errors).
----------------------------------------------------------------------------------
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?
February 18, 2008 at 2:18 pm
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.
February 18, 2008 at 2:35 pm
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?
February 18, 2008 at 2:46 pm
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 🙁
February 18, 2008 at 7:28 pm
Mike C (2/18/2008)
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 🙁
Brandie (who put the question in to Steve) mentioned she got it out of one of the MS Press training books...God only know where they might have picked it up.:blink::ermm::sick:
----------------------------------------------------------------------------------
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?
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply