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

In working on a migration project, I discovered two different versions of SQL rounding and/or truncating implicit conversions from an Oracle NUMBER data type to a SQL Server numeric data type differently.

Here's my test case:

ENVIRONMENT:

Two servers, one running Microsoft SQL Server 2014 and another running SQL Server 2008 R2.

Setup an Oracle 11g Linked Server. Both servers are running the OraOLEDB.Oracle Provider with the same options and driver version (11.02.00.01).

Setup a test database in ORACLE:

CREATE TABLE [MySchema].number_test
  (
     UNDEFINED NUMBER
  ) 

Throw some test values in there:

INSERT INTO [MySchema].number_test VALUES      (-98.786)
INSERT INTO [MySchema].number_test VALUES      (-98.785)
INSERT INTO [MySchema].number_test VALUES      (-98.784)
INSERT INTO [MySchema].number_test VALUES      (98.784)
INSERT INTO [MySchema].number_test VALUES      (98.785)
INSERT INTO [MySchema].number_test VALUES      (98.786) 
COMMIT

THE TEST:

Run the following SQL from both SQL Server 2014 and SQL Server 2008 R2:

SELECT undefined,
       Cast(undefined AS NUMERIC(20, 2))  AS  IConvert
FROM   Openquery([mylinkedserver], 'select * from [MySchema].NUMBER_TEST')
       Numbers  

RESULTS:

SSMS 2014:

undefined	IConvert
-98.786	-98.79
-98.785	-98.78
-98.784	-98.78
98.784	98.78
98.785	98.78
98.786	98.79

SSMS 2008 R2:

undefined	IConvert
-98.786	-98.79
-98.785	-98.79
-98.784	-98.78
98.784	98.78
98.785	98.79
98.786	98.79

FINDINGS:

I'm not sure what's going on here with the implicit conversion from Oracle NUMBER to SQL Server numeric, particularly why the behavior is different between SQL Server 2014 and SQL Server 2008 R2.  Did Microsoft make a decision to change the rounding behavior?  I did test the generic ROUND() function which has consistent results in all 3 environments:

SELECT Round(-98.785, 2) 

SQL Servers:

-98.790

Oracle:

-98.79

Given multiple other issues in 2014 with Oracle NUMBER data types, there does seem to be reason to suspect that either there is a bug:

FIX: Slow performance when you query numeric data types from an Oracle database

FIX: The value of NUMBER type is truncated when you select data from an Oracle-linked server by using OLE DB provider

...Or that for whatever reason the implicit conversion in 2014 is rounding to the nearest even.

Perhaps the 2014 SQL Server is implicitly converting to float, using the nearest even prior to the explicit cast to Numeric.  However, how the scale (number of decimal digits that will be stored to the right of the decimal point) would be determined in such a scenario is a conundrum.   Either way, although the mapping is defined the same, the behavior demonstrated between the two versions of SQL Server is inconsistent.

Research into ANSI and IEEE both boil down to truncation and/or rounding is implementation defined.

I ultimately used the following workaround based on Oracle's rounding behavior as defined by its ROUND() Function:

SELECT * 
FROM   Openquery([mylinkedserver], 'select UNDEFINED, ROUND(UNDEFINED,2) AS 

IConvert from [MySchema].NUMBER_TEST order by 1') Numbers 

I then had results in 2014 that match what is returned from 2008 R2.

The lesson here is to make sure any conversions, or rounding, happen as far upstream as possible.

POST:

I was blogging as I went with this.  Here's one last FLOAT test I ran:

TRUNCATE TABLE [MySchema].number_test 
INSERT INTO [MySchema].number_test VALUES      (-54321.785) 
INSERT INTO [MySchema].number_test VALUES      (-98.785) 
INSERT INTO [MySchema].number_test VALUES      (98.785) 
INSERT INTO [MySchema].number_test VALUES      (54321.785) 
COMMIT 

SQL Server Queries:

SELECT undefined,
       Cast(undefined AS NUMERIC(20, 2)) AS IConvert
FROM   Openquery([mylinkedserver], 'select * from [MySchema].NUMBER_TEST') 

Numbers

SELECT undefined,
       Cast(undefined AS FLOAT(1)) AS IConvert
FROM   Openquery([mylinkedserver], 'select * from [MySchema].NUMBER_TEST') 

Numbers 

Results:

SQL Server 2014

undefined	IConvert
-54321.785	-54321.79
-98.785	-98.78
98.785	98.78
54321.785	54321.79

When the data has a 7 digit precision and the float definition limits the scale to 2 we see a different rounding behavior.

undefined	IConvert
-54321.785	-54321.79
-98.785	-98.785
98.785	98.785
54321.785	54321.79

SQL Server 2008 R2

undefined	IConvert
-54321.785	-54321.79
-98.785	-98.79
98.785	98.79
54321.785	54321.79

undefined	IConvert
-54321.785	-54321.79
-98.785	-98.785
98.785	98.785
54321.785	54321.79