Why the UDF is not deterministic?

Question

Post reply

Why the UDF is not deterministic?

Atif

Hall of Fame

Points: 3578
More actions
February 17, 2009 at 5:12 am

#134821

I have a UDF craeted through following code
USE [AdventureWorks]
GO
CREATE FUNCTION
[dbo].[Calculation] ( @p1 INT, @p2 TINYINT, @p3 TINYINT)
RETURNS INT
AS
BEGIN
DECLARE @ReturnVal INT
SET @ReturnVal = @p1 + @p1*@p2/100 - @p1*@p3/100
RETURN @Returnval
END
GO
I expect it to be deterministic, but testing the property through following code, it returns zero. It indicates that it is non deterministic.
SELECT OBJECTPROPERTY(OBJECT_ID('[dbo].[Calculation]'),
'IsDeterministic') IsUDFDeterministic
Please point out the reason for non determinism.
Regards
DBDigger Microsoft Data Platform Consultancy.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

The function needs to be schemabound

CREATE FUNCTION

[dbo].[Calculation] ( @p1 INT, @p2 TINYINT, @p3 TINYINT)

RETURNS INT

WITH SCHEMABINDING

AS

BEGIN

DECLARE @ReturnVal INT

SET @ReturnVal = @p1 + @p1*@p2/100 - @p1*@p3/100

RETURN @Returnval

END

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Atif Hall of Fame Points: 3578 More actions · Answer 2

Thanks for solution.

Please specify some reference for details of this behavior.

Regards

DBDigger Microsoft Data Platform Consultancy.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

Schemabinding guarantees that the dependancies of the function (e.g. column types) cannot be changed without first altering the function. Check BOL for more information.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

charles.flock-906611 SSC Enthusiast Points: 151 More actions · Answer 4

You should be careful with this function, as it will produce zero in situations where you otherwise would expect a non-zero result. Since none of your calculations are parameterized, you will default to the SQL Server rules regarding precision. For example, if your values for @p1, @p2, adn @p3 are 1,1,and 199, the result will be zero. There are many other conditions under which this will be true.