Why the UDF is not deterministic?

Question

Post reply

Why the UDF is not deterministic?

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

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

Mark Cowne One Orange Chip Points: 26986 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: 26986 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.