Comparison of data types in SQL server2000 and oracle9i

Comparison of data types in SQL server and oracle

Oracle and SQL server data types do not match exactly. Moreover there are some data types which are defined in oracle but do not exist in Sql Server and comparison of them have been listed below.

Microsoft SQL Server	Description	Oracle	Description
INTEGER	Stores integer values. Four-byte integer, 31 bits, and a sign. May be abbreviated as “INT”	NUMBER(10)	Stores numeric data.It is possible to place a table constraint on columns of this type (as an option) to force values between -2^31 and2^31. Or, place appropriate constraints such as: STATE_NO between 1 and 50
SMALLINT	Two-byte integer, 15 bits, and a sign.	NUMBER(6)	It is possible to place a table constraint on columns of this type (optionally) to force values between -2^15 and 2^15. Or, place appropriate constraints such as: STATE_NO between 1 and 50
TINYINT	One byte integer, 8 bits and no sign. Holds whole numbers between 0 and 255.	NUMBER(3)	You may add a check constraint of (x between 0 and 255) where x is column name.
REAL	Floating point number. Storage is four bytes and has a binary precision of 24 bits, a 7-digit precision.	FLOAT	Very similar to NUMBER it stores zero, positive, and negative floating-point numbers.
FLOAT	A floating point number. This column has 15-digit precision.	FLOAT	The Oracle NUMBER data type is used to store both fixed and floating-point numbers in a format compatible with decimal arithmetic.
BIT	A Boolean 0 or 1 stored as one bit of a byte. Up to 8-bit columns from a table may be stored in a single byte, even if not contiguous. Bit columns cannot be NULL and cannot have indexes on them.	NUMBER(1)	In Oracle, a bit is stored in a number (1) (or char). In Oracle, it is possible to store bits in a char or varchar field (packed) and supply PL/SQL functions to set / unset / retrieve / query on them.
CHAR(n)	Fixed-length string of exactly n 8-bit characters, blank padded. Synonym for CHARACTER.	CHAR(n)	A CHAR data type with a range of 2001 to 4000 is invalid. SQL Developer automatically converts a CHAR datatype with this range to VARCHAR2.
VARCHAR(n)	Varying-length character string. 0 < n < 256 for Microsoft SQL Server. 0 < n < 8000 for Microsoft SQL Server 7.0.	VARCHAR2(n)
TEXT	Character string of 8-bit bytes allocated in increments of 2k pages. “TEXT” is stored as a linked-list of 2024-byte pages, blank padded. TEXT columns can hold up to (231-1) characters.	CLOB	The CLOB field can hold up to 4GB.
IMAGE	Binary string of 8-bit bytes. Holds up to (231-1) bytes of binary data.	BLOB	The BLOB field can hold up to 4GB.
BINARY(n)	Fixed length binary string of exactly n 8-bit bytes. 0 < n < 256 for Microsoft SQL Server. 0 < n < 8000 for Microsoft SQL Server 7.0.	RAW(n)/BLOB
VARBINARY(n)	Varying length binary string of up to n 8-bit bytes. 0 < n < 256 for Microsoft SQL Server. 0 < n < 8000 for Microsoft SQL Server 7.0.	RAW(n)/BLOB
DATETIME	Date and time are stored as two 4-byte integers. Permitted values are legal dates between 1st January, 1753 AD and 31st December, 9999 AD.	DATE	The precision of DATE in Oracle and DATETIME in Microsoft SQL Server is different. The DATETIME data type has higher precision than the DATE data type. The Oracle TIMESTAMP data type can also be used. It has a precision of 1/10000000th of a second.
SMALL-DATETIME	Date and time stored as two 2-byte integers. Date ranges from 1/1/1900 to 6/6/2079. Time is the count of the number of minutes since midnight.	DATE	With optional check constraint to validate the smaller range.
MONEY	A monetary value represented as an integer portion and a decimal fraction, and stored as two 4-byte integer. When inputting Data of this type it should be preceded by a dollar sign ($). In the absence of the “$” sign, Microsoft SQL Server creates the value as a float. Storage size is 8 bytes.	NUMBER(19,4)	Oracle is more general and works in international environments where the use of the “$” sign cannot be assumed.
NCHAR(n)	Fixed-length character data type which uses the UNICODE UCS-2 character set. n must be a value in the range 1 to 4000. SQL Server storage size is two times n. Thus, a SQL Server column NCHAR(1000) will appear in the Source Model as NCHAR(2000).	CHAR(n*2)	Stores fixed length character data.
NVARCHAR(n)	variable-length character data type which uses the UNICODE UCS-2 character set. n must be a value in the range 1 to 4000. SQL Server storage size is two times n. Thus, a SQL Server column NVARCHAR (1000) will appear in the Source Model as NVARCHAR (2000).	VARCHAR(n*2)	Stores variable –length character data.
SMALLMONEY	Same as MONEY except monetary data values from -214,748.3648 to +214,748.3647, with accuracy to one ten-thousandth of a monetary unit. Storage size is 4 bytes.	NUMBER(10,4)	Since the range is -214,748.3648 to 214,748.364, NUMBER(10,4) suffices for this field.
TIMESTAMP	TIMESTAMP is defined as VARBINARY (8) with NULL allowed. Every time a row containing a TIMESTAMP column is updated or inserted, the TIMESTAMP column is automatically increment by the system. A TIMESTAMP column may not be updated by users.	NUMBER	You must place triggers on columns of this type to maintain them. In Oracle you can have multiple triggers of the same type without having to integrate them all into one big trigger.
SYSNAME	VARCHAR(30) in Microsoft SQL Server. NVARCHAR(128) in Microsoft SQL Server 7.0.	VARCHAR2(30) and VARCHAR2(128) respectively.