Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


User Defined Function Execution


User Defined Function Execution

Author
Message
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 Visits: 3889
Hm, I am having a blackout:
Why is this function not deterministic?!?

CREATE FUNCTION dbo.fnDateAdd()
RETURNS int
AS
BEGIN
RETURN 0
END
GO



Do I need to have any special session settings enabled?
SELECT objectpropertyex(object_id('dbo.fnDateAdd'), N'IsDeterministic')
returns 0.

I am stuck... Sad

Btw: I prefer "wrong" questions with discussions that start your brain on a topic over questions where I just get my points.

Best Regards,

Chris Büttner
Bob Griffin
Bob Griffin
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1167 Visits: 702
I think you have to have input parameters. If the function is deterministic it will return a consistent result given the same input parameter(s).......give that a whirl...

Smile
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 Visits: 3889
Like this?
CREATE FUNCTION dbo.fnDateAdd(@intA int)
RETURNS int
AS
BEGIN
RETURN @intA
END
GO


Still no luck... Sad

Best Regards,

Chris Büttner
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7641 Visits: 18066
Christian Buettner (2/13/2008)
Hm, I am having a blackout:
Why is this function not deterministic?!?

CREATE FUNCTION dbo.fnDateAdd()
RETURNS int
AS
BEGIN
RETURN 0
END
GO



Do I need to have any special session settings enabled?
SELECT objectpropertyex(object_id('dbo.fnDateAdd'), N'IsDeterministic')
returns 0.

I am stuck... Sad

Btw: I prefer "wrong" questions with discussions that start your brain on a topic over questions where I just get my points.


It IS deterministic, and SQL Server knows it - but somehow that setting never gets setSmile.

Prove it to yourself.


create function dbo.fndateadd2()
returns int
as begin
return dbo.fndateadd()
end
go


It won't let you use anything non-deterministic when creating a function, ergo QED.

Long way to get to "that ObjectProperty be broke" part....Smile

----------------------------------------------------------------------------------
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?
Matt Marston
Matt Marston
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1963 Visits: 412
Chris, it appears that SQL Server requires the WITH SCHEMABINDING function option in order for a function to be identified as deterministic.

Here is a completely meaningless sample to show how SQL server handles deterministic functions.


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 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])


Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 Visits: 3889
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 lostSmile

Best Regards,

Chris Büttner
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7641 Visits: 18066
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?
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 Visits: 3889
Ah, good to know! Thanks for the heads up!

Best Regards,

Chris Büttner
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7767 Visits: 8731
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. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Carla Wilson-484785
Carla Wilson-484785
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1715 Visits: 1949
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search