Data Types in Oracle and SQL Server

Microsoft® SQL Server™ has a larger selection of data types than Oracle. There are many possible conversions between the Oracle and SQL Server data types.

Oracle SQL Server
CHAR char is recommended. char type columns are accessed faster than varchar columns because they use a fixed storage length.
VARCHAR2
and VARCHAR
varchar.
LONG varchar or text. (If the length of the data values in your Oracle column is 8,000 bytes or less, use varchar; otherwise, use text.)
CLOB text. Oracle CLOB columns can store up to 4GB of data. SQL Server text columns are limited to 2GB.
NCHAR nchar is recommended. SQL Server nchar columns contain values from the Unicode standard, not a particular national character set.
NVARCHAR2 nvarchar is recommended. SQL Server nvarchar columns contain values from the Unicode standard, not a particular national character set.
NCLOB ntext. Oracle NCLOB columns can store up to 4GB of data. SQL Server ntext columns are limited to 2GB.
RAW varbinary.
LONG RAW varbinary or image. (If the length of the data values in your Oracle column is 8,000 bytes or less, use varbinary; otherwise, use image.)
BLOB image. Oracle BLOB columns can store up to 4GB of data. SQL Server image columns are limited to 2GB.
BFILE SQL Server does not support the use of external data sources in the same way as an Oracle BFILE column. External data sources can be accessed by SQL Server but are not mapped to a column.
NUMBER If the integer is from 1 through 255, use tinyint.
If the integer is from -32768 through 32767, use smallint.
If the integer is from -2,147,483,648 through 2,147,483,647 use int.
If you require a number with decimal places, use decimal. Do not use float or real, because rounding may occur (Oracle NUMBER and SQL Server decimal do not round).
If you are not sure, use decimal; it most closely resembles Oracle NUMBER data type.
DATE datetime or timestamp.
ROWID Use the identity column type.
MLSLABEL MLSLABEL is used only for backward compatibility with earlier versions of Oracle using Trusted Oracle. MLSLABEL columns are not needed in SQL Server.

Timestamp Columns

A timestamp column enables BROWSE-mode updates and makes cursor update operations more efficient. A timestamp column is updated automatically every time a row is inserted or updated.

Datetime Columns

Use the function GETDATE() in place of the Oracle SYSDATE to get the current system date and time.

Sequences and the IDENTITY Property

If your Oracle application currently uses sequences to generate sequential numeric values, it can be altered to take advantage of the SQL Server IDENTITY property. The primary difference between SQL Server and Oracle is that the IDENTITY property is actually part of the column, while a sequence is independent of any tables or columns.

See Also
Data Types IDENTITY (Property)
timestamp  

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.