Division precision - 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 2000 Forums

Transact-SQL (2000)

Division precision

Author

Topic

vooose
Starting Member

10 Posts

Posted - 2004-09-17 : 05:46:39

Hi. I fiddled alot with this one but couldnt get it to work so I thought a post was necessary.

This problem relates to division precision. Consider the simple code

DECLARE @tmp DECIMAL(38, 20),
@a DECIMAL(38, 20),
@b DECIMAL(38, 20)

SET @a = 25
SET @b = 33
SET @tmp = @a/@b
PRINT @tmp

25 divided by 33 in real life gives 0.757575 recurring...for some reason the print output is:

0.75757500000000000000

No matter what I do SQL is not honouring the extra precision and zeroing every extra decimal place after the 6th. Any insight?

Wal

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-17 : 06:20:21

This looks like a BUG actually.
Look up "scale of data type" in BOL:

When you divide: @a/@b the resulting scale is: max(6, s1 + p2 + 1) according to BOL.
But:
"When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated."

But in all my tests the resulting scale is always 6 !!!

Please someone:
counterexample needed !!!

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

vooose
Starting Member

10 Posts

Posted - 2004-09-19 : 19:59:35

Thanks for your reply...I have read the documentation and it simply doesnt work according to spec! Nothing much else I can say, this is either a bug or something we are both completly missing. Any ideas world ?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-20 : 04:19:53

Well, got it sort of working now,
It is interesting to note that in numeric division it might not be the "best" to apply the most precise variable definition.
If the resulting precision+scale is "too large" then truncation will occur.
In the example below setting @ and @b to lower precision+scale gave better results after division, compared to using a higher precision+scale.

Here is my test code:

USE tempdb
GO

DECLARE @tmp DECIMAL(38,20),
@a DECIMAL(15, 6),
@b DECIMAL(15, 6)

SET @a = 25
SET @b = 33
SET @tmp = @a/@b
SELECT @a AS a, @b As b, @a/@b AS aDIVb, @tmp AS tmp INTO #testtable


SELECT
	@a AS a,
	@b As b,
	@a/@b AS aDIVb,
	@tmp As tmp,
	--p1,
	--s1,
	--p2,
	--s2,
	--[max(6,s1+p2+1)],
	p_aDIVb,
	s_aDIVb,
	p_tmp,
	s_tmp,
	p1-s1+s2+[max(6,s1+p2+1)] AS p_aDIVb2,
	[max(6,s1+p2+1)] AS s_aDIVb2
FROM(
SELECT
	COLUMNPROPERTY(OBJECT_ID('tempdb..#testtable'),'a','PRECISION') AS p1,
	COLUMNPROPERTY(OBJECT_ID('tempdb..#testtable'),'a','SCALE') AS s1,
	COLUMNPROPERTY(OBJECT_ID('tempdb..#testtable'),'b','PRECISION') AS p2,
	COLUMNPROPERTY(OBJECT_ID('tempdb..#testtable'),'b','SCALE') AS s2,
	COLUMNPROPERTY(OBJECT_ID('tempdb..#testtable'),'aDIVb','PRECISION') AS p_aDIVb,
	COLUMNPROPERTY(OBJECT_ID('tempdb..#testtable'),'aDIVb','SCALE') AS s_aDIVb,
	COLUMNPROPERTY(OBJECT_ID('tempdb..#testtable'),'tmp','PRECISION') AS p_tmp,
	COLUMNPROPERTY(OBJECT_ID('tempdb..#testtable'),'tmp','SCALE') AS s_tmp,
	CASE 	WHEN 	COLUMNPROPERTY(OBJECT_ID('tempdb..#testtable'),'a','SCALE')+
			COLUMNPROPERTY(OBJECT_ID('tempdb..#testtable'),'b','PRECISION')+1 < 7 THEN 6
		WHEN 	COLUMNPROPERTY(OBJECT_ID('tempdb..#testtable'),'a','SCALE')+
			COLUMNPROPERTY(OBJECT_ID('tempdb..#testtable'),'b','PRECISION')+1 > 38 THEN 6
		ELSE 	COLUMNPROPERTY(OBJECT_ID('tempdb..#testtable'),'a','SCALE')+
			COLUMNPROPERTY(OBJECT_ID('tempdb..#testtable'),'b','PRECISION')+1 END AS [max(6,s1+p2+1)]
) t
	


DROP TABLE #testtable

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

vooose
Starting Member

10 Posts

Posted - 2004-09-20 : 21:57:46

Thats the most illogical thing I've seen so far. The more precise the get, the less precise you get. Thanks for your replies...I guess I will just stick with decimal(15, 6)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources