SQL Server 2005 and Oracle Sync Note number type conversions

In the previous article I've talked about using synonyms to connect to Oracle under SQL Server 2005, and we can use synonyms to connect Oracle under SQL Server 2005 to real-time access to Oracle databases. However, if the table data stream in Oracle affects the performance of the application system, it should be performed on a daily basis using the database job:

drop table abc--删除旧表
go
select * into abc
from aaa--aaa为同义词

From AAA--AAA is synonymous so that data from Oracle can be synchronized to a local SQL Server database. This solves performance problems with cross instance queries.

In this way for six months did not find any problems, but recently found a phenomenon, in Oracle has a table AAA, one of the Fields Bill number type (not specified precision and decimal data bits), for this type, SQL Server2005 synchronized Table ABC is defined as the type of nvarchar (384)!? is clearly a numeric type why does SQL Server convert it to a string type?

If it's just that the data type changes, there's nothing I can do, but it's even more strange that some of these data are found in Oracle as 12.34567, but in SQL Server 2005 it's A 12.345670543574563452346547546234234543656434., after the dozens of decimal, it's amazing! One row of data is 1 in Oracle, and in SQL Found in the server is 0.99999999999999999999999999999999 ... But this kind of data also rarely occurs, in tens of thousands of data can find 2-3 of these dozens of-bit decimal data.

It is this kind of data that makes it possible for applications to calculate results that are not matched by the results from Oracle's side of the system.

Tests indicate that SQL Server can automatically convert the number type to the numeric (15,0) type if the precision and scale of number types are specified in Oracle, such as numbers (15).

Because the number type can represent data between 1.0 * 10 (-130)--9.9...9 * 10 (125) {38 9 and 88 0}, the precision can be as low as 38 digits after the decimal point, because SQL Server does not have such a high precision data type, so in If no number precision and decimal places are specified, SQL Server converts it to a string type to meet the need for length and precision.

The workaround is to modify the nvarchar (384) type of the synchronized table in SQL Server to type decimal or numeric, not to delete the table when synchronizing, but to clear the contents of the table and then insert the data. Synchronous SQL is:

TRUNCATE TABLE abc--清除表abc内容
go
insert into abc--将同义词aaa中的数据插入abc表
select *
from aaa

The problem is solved, but what is the reason for the difference between the data on both sides of the query? I don't know that yet.