Comparing SQL Server and Oracle datatypes

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:

• SQL Server 2012 Datatypes
• Oracle 11g Release 2 Datatypes

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. Its 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:

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.