CAST as numeric rounding issue

Question

CAST as numeric rounding issue

Jon.Morisi

SSChampion

Points: 12846
More actions
July 5, 2016 at 4:15 pm

#400358

Hi,
I have two servers, one running Microsoft SQL Server 2014 (SP1-CU5) and another running SQL Server 2008 R2 Service Pack 3 (SP3).
I have a linked server setup to Oracle 11g. Both servers are running the OraOLEDB.Oracle Provider with the same options and driver version (11.02.00.01).
I setup a test database in ORACLE:
CREATE TABLE [MySchema].NUMBER_TEST
(
UNDEFINED NUMBER
)
...
I threw some test values in there:
select * from [MySchema].NUMBER_TEST
order by 1
-98.786
-98.785
-98.784
98.784
98.785
98.786
Here's where things get weird on me:
SSMS 2014:
Select UNDEFINED, CAST(UNDEFINED AS numeric(20,2))
from openquery([MyLinkedServer],
'select * from [MySchema].NUMBER_TEST') Numbers
UNDEFINED(No column name)
-98.786-98.79
[highlight="#ffff11"]-98.785-98.78[/highlight] <-- Rounded Up
-98.784-98.78
98.78498.78
[highlight="#ffff11"]98.78598.78[/highlight] <-- Rounded Down
98.78698.79
SSMS 2008 R2:
(same query)
UNDEFINED(No column name)
-98.786-98.79
[highlight="#ffff11"]-98.785-98.79[/highlight] <-- Rounded Down
-98.784-98.78
98.78498.78
[highlight="#ffff11"]98.78598.79[/highlight] <-- Rounded Up
98.78698.79
Does anyone have any idea why the rounding is occurring differently?

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

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

Despite what your arrows say, 2014 rounded .785 down and 2008 rounded up consistently.

I'm not familiar with the Oracle NUMBER datatype, but the documentation I glanced at suggests that in the absence of scale and precision specifications, your data is being stored as floating, which is an approximation. Evidently 2008 and 2014 are interpreting the stored values differently in making rounding decisions. Hopefully someone else can give you a more precise explanation.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 2

For these reasons, I don't use implicit rounding caused by datatype scale reduction. I always use the ROUND() function to do the rounding and then stuff it into the datatype I want for display purposes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jon.Morisi SSChampion Points: 12846 More actions · Answer 3

Thanks for the replies. I put together a blog post here:

CAST as numeric rounding issue (Oracle Number to SQL Server numeric)

One thing I've learned from all of this is that the mathematical definition rounding is ambiguous!

Sergiy SSC Guru Points: 110208 More actions · Answer 4

It may be not entirely SQL2014 fault

I've set up a quick test in 2014 instance:CREATE TABLE #NUMBER_TEST

(

UNDEFINED NUMERIC (20,4)

)

INSERT INTO #NUMBER_TEST

SELECT -98.786 UNION

SELECT -98.785 UNION

SELECT -98.784 UNION

SELECT 98.784 UNION

SELECT 98.785 UNION

SELECT 98.786

SELECT nt.UNDEFINED, CAST(UNDEFINED AS numeric(20,2)) FROM #NUMBER_TEST nt

and the result exactly the same as for SQL2008R2:

UNDEFINED(No column name)

-98.7860-98.79

-98.7850-98.79

-98.7840-98.78

98.7840 98.78

98.7850 98.79

98.7860 98.79

There must be something with OPENQUERY.

Look at the drivers used to connect to Oracle in both cases.

_____________
Code for TallyGenerator