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. |
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.
Use the function GETDATE() in place of the Oracle SYSDATE to get the current system date and time.
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.
| Data Types | IDENTITY (Property) |
| timestamp |