Differences between data types between SQL Server and Oracle

Published On: 2016-04-27By:

Recently there has been some talk online about how SQL Server processes data type conversions compared to how Oracle’s method for type conversion. I figured that I would clear up some of the confusion that’s out there.

When concatenating a number (like an INT) to a string (like a VARCHAR) you get an error message in SQL Server, but no error message in Oracle. The reason for this difference is due to the data type precedence differences in the two platforms. In SQL Server the database engine is trying to convert the VARCHAR value to an INT value (in this example) because INT is higher on the data type conversion order. Oracle on the other hand when concatenating a number (NUMERIC) to a string (like VARCHAR2) does not through an error message because it is converting the number to a string, then concatenates the string values.

If you create a table with a numeric datatype, and use a string value in the WHERE clause both platforms will throw an error because the data types don’t match.

I’ve also seen questions about why SQL Server has so many numeric data types when Oracle only has one. Having the different data types in SQL Server helps to optimize the storage of numbers, and to make it easier to predict how must storage your table will need (I’m assuming no compression in either case). With Oracle it requires one bit per value of 16 (remember data is stored in binary, not as actual numbers) (assuming that the value isn’t mostly zeros such as 100000 or 1000000000 where each of those values only requires two bytes of storage). In SQL Server, the space needed depends on the size of the data type with TINYINT taking 1 byte, SMALLINT taking 2 bytes, etc.

With data compression the storage changes for both platforms allowing numbers to be stored using less space. However, Data Compression in SQL Server requires Enterprise Edition and Advanced Compression in Oracle requires Enterprise Edition as well as the Advanced Compression feature to be purchased.

Denny

The post Differences between data types between SQL Server and Oracle appeared first on SQL Server with Mr. Denny.


Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via