Comparison of Department and Employees tables after importing - Oracle

The next set of pictures shows the tables in their own platforms so that you can make comparisons. It looks like SQL 2005 did a pretty good job with the copying of the columns. However, the primary key information is lost and so are the indexes, and more. A detailed discussion of the differences is outside the scope of this tutorial. The NUMBER data type in Oracle XE goes over directly to the Decimal data type in SQL 2005 Server; similar differences exist for varchar and varchar2 data types. To quote from their documentation:

NUMBER [(precision [, scale]]) Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.

The decimal data type can store a maximum of 38 digits, all of which can be to the right of the decimal point. The decimal data type stores an exact representation of the number; there is no approximation of the stored value. The two attributes that define decimal columns, variables, and parameters are: p Specifies the precision, or the number of digits the object can hold. s Specifies the scale or number of digits that can be placed to the right of the decimal point. p and s must observe the rule: 0 <= s <= p <= 38. The default maximum precision of numeric and decimal data types is 38. In Transact-SQL, numeric is functionally equivalent to the decimal data type.

 

To explain this by way of images, compare this one from Oracle XE:

with this one from SQL 2005 Server:

Likewise, compare this image from Oracle XE:

with this one from SQL 2005 Server:

Summary

SQL 2005 Server's Integrated Services does a pretty good job of data transfer as shown here. The comparative view of tables shows some of the differences. A similar comparison for other tables and objects should give you a good idea of how data is handled in the two database programs, an invaluable aid if you are interested in ETL. There are quite a number of differences between DTS and SSIS as you might have noted, assuming you experienced the earlier version. Oracle XE is also a cool package, free, and can be upgraded easily from this version. You may also notice that Oracle XE gives you a lot more information on a table than SQL 2005 does, all in one screen.  Thus begins the battle of the titans.