How to make a UDF precise - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to make a UDF precise

Author

Topic

skemp
Starting Member

4 Posts

Posted - 2011-02-14 : 11:31:53

Hi,

I am running into a bit of a brick wall with this one and have read as many BOL articles as I can and cannot find a suitable expalnation of what is causing the problem.

I am trying to implement an indexed view on many millions of periodic readings that applies Chauvenet's criterion so as to exclude any values that may be caused by measurement errors and such like. All is going well except that I need to use an error function, as defined here:
[url]http://weblogs.sqlteam.com/peterl/archive/2009/03/11/Excel-ERF-clone.aspx[/url]

CREATE FUNCTION dbo.fnErf
(
        @z FLOAT,
        @MaxIterations TINYINT = 10
)
RETURNS FLOAT
AS
BEGIN
        IF @z IS NULL
                RETURN      NULL

        DECLARE @n TINYINT,
                @s FLOAT,
                @p FLOAT,
                @a FLOAT

        SELECT  @n = 1,
                @p = 1,
                @a = @z,
                @MaxIterations = COALESCE(ABS(@MaxIterations), 10)

        WHILE @p <> 0.0E AND @n <= @MaxIterations
                SELECT  @s = @z /(2.0E * @n + 1.0E),
                        @p = - @p * @z * @z / @n,
                        @a = @a + @s * @p,
                        @n = @n + 1

        RETURN  @a * 2.0E / SQRT(PI())
END

In order to use this in my indexed view it has to be both determinate and precise (I have modded it to have schemabinding etc).

The problem i am having is with making it precise. It seems that some of the BOL articles imply that decimals are all precise, hence i have changed it to use decimals. However some of the functions seem to return floats (SQRT, EXP etc). Is it this that is causing the function to be imprecise?
if so how do i resolve it? i have tried wrapping the last line in a convert(Decimal(18,10)).... but this makes no difference. if i comment out all of the lines that use the built in functions then it is fine.

Is this possible to do? Can anyone point me at or offer a nice clear explanation of how to make a function precise?

Many thanks in advance. I hope that is enough information.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-14 : 15:32:50

I ran into a similar problem creating customized functions for some performance models at a bank..try specifying specific precision for each variable as decimal, and instead of returns float, have it return DECIMAL(38,19).

Poor planning on your part does not constitute an emergency on my part.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-02-14 : 16:46:34

If you want the function to return a numeric(18,10) use this as your return:

RETURN  convert(numeric(18,10),round(@a * 2.0E / SQRT(PI()),10))

CODO ERGO SUM

skemp
Starting Member

4 Posts

Posted - 2011-02-15 : 04:05:20

Hi,

Thank you both for your reply.

I should have been clearer in my initial post and have pasted my altered code (in which everything has been set to a decimal etc):

The following represents the code I had altered with the Decimal(38,19) definitions and the convert and round on the last line.

CREATE  FUNCTION [dbo].[fn_ERF](
				@z DECIMAL (38,19)
				) 

RETURNS DECIMAL (38,19)
WITH SCHEMABINDING

AS
BEGIN

DECLARE @MaxIterations TINYINT 
SET @MaxIterations = 10
        IF @z IS NULL
                RETURN      NULL

        DECLARE @n TINYINT,
                @s DECIMAL (38,19),
                @p DECIMAL (38,19) ,
                @a DECIMAL (38,19)

        SELECT  @n = 1,
                @p = 1,
                @a = @z,
                @MaxIterations = COALESCE(ABS(@MaxIterations), 10)

        WHILE @p <> 0 AND @n <= @MaxIterations
                SELECT  @s = @z /(2.0 * @n + 1.0),
                        @p = - @p * @z * @z / @n,
                        @a = @a + @s * @p,
                        @n = @n + 1

        RETURN CONVERT(DECIMAL(38,19),ROUND(@a * 2.0 / SQRT(PI()),19))
END

GO

I have also tried the following variant:

CREATE  FUNCTION [dbo].[fn_ERF](@Z as DECIMAL (18, 10)) 

RETURNS DECIMAL (38, 19)
WITH SCHEMABINDING

BEGIN

DECLARE @t DECIMAL (18, 10)
DECLARE @ans DECIMAL (38, 19)

SET @t = 1.0 / (1.0 + 0.5 * ABS(@Z))
--Use Horner's Method
SET @ans = ROUND(1 - @t * EXP(-@z * @z - 1.26551223 +
							@t * (1.00002368 +
							@t * (0.37409196 +
							@t * (0.09678418 +
							@t * (-0.18628806 +
							@t * (0.27886807 +
							@t * (-1.13520398 +
							@t * (1.48851587 +
							@t * (-0.82215223+
							@t * (0.17087277)))))))))),19)
if @Z < 0.0
	SET @ans = -@ans

RETURN @ans
END

GO

And get the same issue with the imprecise function.

I beleive that DECIMAL is the only suitable data type that is counted as precise (from the BOL pages), i have no idea what else to do. Any help would be very, very greatly appreciated!

kind regards
Stephen

skemp
Starting Member

4 Posts

Posted - 2011-02-15 : 06:10:37

As an update, I tried adding the second example above directly into the view (since it only accepts a column value as an argument), it was a mess but it 'worked'.

I know get a little bit more of a specific error report stating that the EXP function is not precise and hence cannot be used. Can i 'force' the function to be precise? Or is it impossible to perform any sort of complex maths on an indexed view?

Is there another way i should be looking at this?

I suspect i can work around it by modifying the underlying table to have an 'IsOutlier' parameter and then setting that to 1 in the case that the test fails. In this case I would need to run the 'update outliers' whenever I changed the data table (which is not freqently).

If anyone can offer anything else on this, then please let me know.

Regards
Stephen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-15 : 06:34:11

Otherwise, have a trigger to calculate the ERF value for each record and then you can create the view without any hassle.
This makes sense, because why calculate the ERF value more than once? Recalculate it only when needed.

N 56°04'39.26"
E 12°55'05.63"

skemp
Starting Member

4 Posts

Posted - 2011-02-15 : 06:48:40

Hi Peso,

You need to recalculate because the ERF is based on the Standard Deviation and Average of the dataset as a whole (i did not mention that these are the values that are passed to the function). If i add to the dataset then the possibility exists for the data to be marked in or out of being an outlier. I am working on the assumption that a view is not the best way to do this and hence adding a flagging column to the dataset.

Many thanks for your help, however.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources