Compare SQL Server and Oracle Character Data Types

Oracle and SQL Server data types do not always match exactly. This table compares SQL Server 2008 and Oracle 11g character data types.

SQL Server		Oracle
CHAR	Fixed length non-Unicode character data. Can be specified up to 8000 characters	CHAR	Fixed length non-Unicode character data. Can be specified up to 4000 characters
NCHAR	Fixed length Unicode character data. Can be specified up to 4000 characters	NCHAR	Fixed length Unicode character data. Maximum of 2000 bytes, the maximum character length is dependent on the number of bytes of the national character set.
VARCHAR	Variable length non-Unicode character data. Can be specified up to 8,000 characters.	VARCHAR2	Variable length non-Unicode character data. Can be specified up to 4,000 characters. The VARCHAR data type is synonymous with the VARCHAR2 data type.
VARCHAR(MAX)	Variable length non-Unicode character data, up to 2,147,483,645 characters.	CLOB	Variable length non-Unicode character data, that can hold be up 8 terabytes of character data.
NVARCHAR	Variable length Unicode character data. Can be specified up to 4,000 characters.	NVARCHAR2	Variable length Unicode character data. Maximum of 4000 bytes, the maximum character length is dependent on the number of bytes of the national character set.
NVARCHAR(MAX)	Variable length Unicode character data, up to 1,073,741,822 characters.	NCLOB	Variable length non-Unicode character data, that can hold be up 8 terabytes of character data. The maximum character length is dependent on the number of bytes of the national character set.

"N” Data Types

For both SQL Server and Oracle, each character data type has a corresponding “N” data type, which use multi-byte character sets, allowing a larger number of different characters. This is useful for non-alphabetic languages.
This means that the size in bytes is no longer equal to the length in characters, so the maximum length is often less.
In Oracle the database character set controls the character set of CHAR, VARCHAR2 and CLOB data types. The national character set controls the character set of NCHAR, NVARCHAR2 and NCLOB data types.

SQL Server TEXT and NTEXT Data Types

SQL Server includes TEXT and NTEXT data types which may be removed in a future version. Developers should use VARCHAR(MAX) or NVARCHAR(MAX) instead.
By default the TEXT and NTEXT data types store a LOB locator in the record, and hold the actual text value in a LOB elsewhere.

LOB Data Types

The SQL Server VARCHAR(MAX) and NVARCHAR(MAX) data types store the text value in the record, unless the text is over 8,000 bytes, as a normal VARCHAR would. When it exceeds 8000 bytes its behaviour changes and it stores a LOB locator in the record and holds the text in a LOB elsewhere, like the older TEXT data type.
The Oracle CLOB data type can be defined to be stored within the record or have a LOB locator, along with other storage characteristics, when the object is created.

LONG Data Type

Oracle includes a LONG data type, which has been included for backward compatibility since Oracle 8, when the CLOB data type was introduced. The CLOB data type should always be used instead of a LONG.

See also: Compare Lob Data Types, Compare Numeric Data Types