Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

User Defined Function Execution Expand / Collapse
Author
Message
Posted Wednesday, February 13, 2008 12:53 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
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...

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
Post #455320
Posted Wednesday, February 13, 2008 1:05 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 6:48 PM
Points: 1,136, Visits: 699
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...

:)
Post #455329
Posted Wednesday, February 13, 2008 1:09 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
Like this?
CREATE FUNCTION dbo.fnDateAdd(@intA int)
RETURNS int
AS
BEGIN
RETURN @intA
END
GO

Still no luck...


Best Regards,
Chris Büttner
Post #455330
Posted Wednesday, February 13, 2008 1:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:38 PM
Points: 7,161, Visits: 15,648
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...

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 set:).

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....:)


----------------------------------------------------------------------------------
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?
Post #455334
Posted Wednesday, February 13, 2008 1:21 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 27, 2014 12:38 PM
Points: 1,922, Visits: 400
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])

Post #455339
Posted Wednesday, February 13, 2008 1:49 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
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
Post #455375
Posted Wednesday, February 13, 2008 1:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:38 PM
Points: 7,161, Visits: 15,648
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?
Post #455382
Posted Wednesday, February 13, 2008 2:21 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
Ah, good to know! Thanks for the heads up!

Best Regards,
Chris Büttner
Post #455407
Posted Thursday, February 14, 2008 5:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:12 PM
Points: 5,615, Visits: 6,413
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #455633
Posted Thursday, February 14, 2008 8:38 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 27, 2014 6:49 AM
Points: 1,585, Visits: 1,859
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.
Post #455748
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse