Comparing SQL Server and Oracle datatypes


By:   |   Updated: 2013-04-25   |   Comments (7)   |   Related: More > Data Types

Problem

When I first started with SQL Server, as initially I worked exclusively with Oracle databases, I found myself constantly thinking in terms of Oracle datatypes and having to look up what the equivalent was in SQL Server. Now that I am primarily a SQL Server DBA I find that the opposite is true. This tip will compare the different datatypes offered by both database systems so those of us that work with both technologies or are planning a migration from one technology to the other have a guide that they can use as a quick reference.

Solution

For this comparison we are going to focus on SQL Server 2012 and Oracle 11g Release 2. Full descriptions of all the datatypes for each technology can be found using the following links:

For this comparison we will break the datatypes down into the following categories (as found in the SQL Server link above):

  • Exact numeric
  • Approximate numeric
  • Date and Time
  • character strings (unicode and non-unicode)
  • Binary strings
  • Other data types

Exact numeric data types in SQL Server and Oracle

When it comes to exact numeric the most often used datatype would probably be the integer datatype. With SQL Server we have the following options based on the size required. TINYINT, SMALLINT, INT and BIGINT. The Oracle equivalent of these datatypes would be the NUMBER datatype specifying a precision based on the size required and a scale of 0. Specifying precision and scale should ring a bell for SQL Server DBAs when it comes to the decimal and numeric datatypes we use in SQL Server. DECIMAL[(p[,s])] and NUMERIC[(p[,s])] in SQL Server can be represented by NUMBER(p,s) in Oracle. The only difference to note with the two technologies is that in SQL Server the scale must be between 0 and the precision, i.e.. 0 <= s <= p, however in Oracle this scale value can range from -87 to 127. For example if you had a NUMBER defined as NUMBER(5,-1) then this value would always be rounded to the nearest ten.

SQL Server also has an additional datatype that deals specifically with monetary or currency values. The SMALLMONEY and MONEY datatypes store these values and are accurate to a ten-thousandth of the value they represent. Although Oracle does not have any specific monetary datatype we could represent these datatypes using the NUMBER datatype. SMALLMONEY could map to NUMBER(10,4) and MONEY could be represented by NUMBER(19,4).

Oracle also has a additional datatype FLOAT(p) which within Oracle is a subtype of the NUMBER datatype with precision p and is internally represented by the NUMBER datatype. With this datatype you are specifying the number of binary digits the variable can handle, between 1 and 126

Approximate numeric data types in SQL Server and Oracle

Within SQL Server we have 2 options for storing floating-point numbers, FLOAT and REAL. As we all know, REAL is simply a synonym for FLOAT(24) and uses 4 bytes of storage and any value greater than 24 for FLOAT requires 8 bytes of storage. These datatypes can be mapped easily to the ORACLE BINARY_FLOAT and BINARY_DOUBLE datatypes where the former requires 4 bytes of storage and the later requires 8 bytes.

Date and Time data types in SQL Server and Oracle

Breaking down the different date datatypes within SQL Server let's start with the simplest, SMALLDATETIME. It's Oracle equivalent would be the DATE datatype. Both of these datatypes do not store fractional seconds and the important difference to note is that the Oracle DATE datatype does support a much larger range of date values. The next level of accuracy when it comes to any date datatype would be one that stores fractional seconds. Within SQL Server these are represented by the DATETIME and DATETIME2 (fs) datatypes which can store up to 3 and 7 digits in the fractional seconds component respectively. The Oracle equivalent of this datatype is the TIMESTAMP (fs) datatype which has the ability to store up to 9 digits in the fractional seconds portion. The last category of date datatype are those that provide support for time zones. With SQL Server we can use the DATETIMEOFFSET datatype which is essentially the DATETIME2 datatype with and extra component to store the time zone offset. In Oracle there are two options that would be equivalent to it's SQL Server counterpart. First is the TIMESTAMP (fs) WITH TIME ZONE datatype which the same as the SQL Server datatype in that it simply has an extra component to store the time zone portion. Oracle also has the TIMESTAMP (fs) WITH LOCAL TIME ZONE datatype which stores the data normalized to the database time zone and retrieves it based on the session time zone.

There are also a couple datatypes available in each technology that don't really have equivalents in the other. SQL Server's DATE and TIME (fs) datatypes which store each component individually do not really have a direct equivalent in Oracle. Similarly Oracle's INTERVAL YEAR (y) TO MONTH or INTERVAL DAY (d) TO SECOND (fs) which store periods of time do not have a similar counterpart in SQL Server.

Character strings (unicode and non-unicode) data types in SQL Server and Oracle

With respect to character strings both Oracle and SQL Server support unicode character strings. In both cases the datatype is simply prefixed with and "N" to indicate that it can handle unicode characters. For the rest of this section we will simply focus on the non-unicode datatypes as any of the information described below applies the unicode equivalent. In SQL Server we handle strings using CHAR and VARCHAR for fixed and variable length character string respectively. Oracle uses CHAR and CLOB datatype which can handle up to (4GB-1)*db_block_size of data.

There is one other datatype I should mention for both technologies that will be deprecated in a future release, but are still supported for backward compatibility. SQL Server has a TEXT datatype and the Oracle equivalent to this is the LONG datatype. Both can store variable length character strings of up to 2GB.

Binary strings data types in SQL Server and Oracle

For storing binary data within SQL Server we now have 2 main options to choose from. If we need to store fixed length binary data we can use the BINARY(n) datatype which can hold between 1 and 8000 bytes of data. The Oracle equivalent of this would be the RAW(n) datatype. The only difference between the two is that the Oracle RAW datatype has a maximum size of 2000 bytes. The other option we have is the VARBINARY(n) datatype in SQL Server. This datatypes stores variable length binary data between 1 and 8000 bytes. If we need to store more than 8000 bytes of binary data in SQL Server we can also use this datatype and specify MAX as the size. Doing this allows us to store up to 2GB of data. The Oracle equivalent of this is the LONG RAW datatype which can also store up to 2GB of variable length binary data. SQL Server also has an IMAGE datatype, which stores up to 2GB of variable length binary data, but this datatype will be removed from a future release so VARCHAR(MAX) should be used in it's place. Oracle also has the ability to store data larger than 2GB in one column by using the BLOB (binary large object) datatype. It can store binary data up to ((4GB-1)*database_block_size).

Other data types data types in SQL Server and Oracle

Both SQL Server and Oracle also provide a datatype which can store XML data. SQL Server's is called XML and Oracle's is called XMLTYPE. Both platforms have built-in functions for accessing and parsing the column data. The only difference in the two is the amount of storage space. XML in SQL Server can handle up to 2GB of data while the XMLTYPE datatype in Oracle can handle up to 6GB.

There are a few datatypes that exist in SQL Server that do not have a direct equivalent counterparts in Oracle, but can be represented quite easily. These are the BIT, TIMESTAMP and UNIQUEIDENTIFIER datatypes. The BIT datatype is usually represented by NUMBER(1) in Oracle systems as it simplifies comparison operations, but you could also use CHAR(1). The TIMESTAMP datatype can be represented using the ORA_ROWSCN pseudo column in Oracle. Although not actually a physical column in the table it can be used in the same manner that TIMESTAMP columns are used in SQL Server to test whether or not data has been modified since it was retrieved. As for the UNIQUEIDENTIFIER datatype this can be represented in Oracle using the RAW(16) datatype. Similar to how SQL Server generates values for this column using the NEWID() function you can get the same functionality in Oracle from the SYS_GUID() function.

Finally there is one datatype in Oracle that has no equivalent SQL Server datatype. This is the BFILE datatype which simply contains a locator to a file that is stored outside the database.

Summary

The following summarizes the datatype mappings described above:

SQL Server Oracle
Exact Numerics

TINYINT

NUMBER(3)

SMALLINT

NUMBER(5)

INTEGER

NUMBER(10)

BIGINT

NUMBER(19)

DECIMAL(p,s)

NUMBER(p,s)

NUMERIC(p,s)

NUMBER(p,s)

SMALLMONEY

NUMBER(10,4)

MONEY

NUMBER(19,4)

Approximate Numerics

REAL

BINARY_FLOAT

FLOAT

BINARY_DOUBLE

Date Time

SMALLDATETIME

TIMESTAMP(3)

DATETIME

TIMESTAMP(3)

DATETIME2(fs)

TIMESTAMP(fs)

DATETIMEOFFSET(fs)

TIMESTAMP (fs) WITH TIME ZONE

DATETIMEOFFSET(fs)

TIMESTAMP (fs) WITH LOCAL TIME ZONE

Character strings

CHAR(x)

CHAR(x)

VARCHAR(x)

ARCHAR2(x)

VARCHAR(MAX)

CLOB

TEXT

LONG

Binary strings

BINARY(n)

RAW(n)

VARBINARY(n)

LONG RAW

VARBINARY(MAX)

LONG RAW or BLOB

IMAGE

LONG RAW

Binary strings

XML

XMLTYPE

BIT

NUMBER(1)

TIMESTAMP

ORA_ROWSCN pseudo column

UNIQUEIDENTIFIER

RAW(16)

N/A

BFILE

Next Steps
  • Read more information on any differences in internal storage characteristics of each datatype.
  • Check how formatting date strings differs between the two platforms.
  • Investigate the differences in converting between datatypes within each platform.


Last Updated: 2013-04-25


get scripts

next tip button



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, May 22, 2019 - 6:37:15 PM - Emil Back To Top

Thanks, this article helped me!


Friday, January 25, 2019 - 3:40:08 PM - Travis Back To Top

 Is it possible to use date and time styles (101, 102,  etc) in Oracle Sql Developer? Or are those style codes exclusive to Microsoft products?


Sunday, April 23, 2017 - 1:08:50 PM - Kelly Back To Top

Thank you! This chart is exactly what I needed!

 

That being said - I think there's a typo though - shouldn't varchar in sqllserver be the equalivant as varchar2 in oracle (as opposed to archar2 which is listed above currently). 


Thursday, May 26, 2016 - 6:50:39 AM - Zafer Balkan Back To Top

This has been the most important tutorial I've seen since I started with databases. Because I work in an environment who works both MsSql and Oracle simultaneously. But I believe this needs an update since it's been 3 years you both may need to add some more information and things had updates. Some links are broken in the article also. Also, after migration there occurs some needs for coding about the behaviors of databases. It happened to me on ASP.NET MVC 4.x and I believe most beginners confront these. Thank you for this great article.


Tuesday, October 13, 2015 - 4:29:14 PM - Sandeep Kataria Back To Top

awesome man...thanks for the table.


Thursday, February 05, 2015 - 12:15:01 PM - Scott Back To Top

Wouldn't you consider FILESTREAM to be roughly equivalent to BFILE?


Monday, April 14, 2014 - 2:24:07 PM - Richard Polunsky Back To Top

Thanks Ben.  A good start.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools