Compare SQL Server and Oracle LOB Data Types

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

SQL Server		Oracle
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(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.
VARBINARY(MAX)	Stores the binary objects in the database. Up to 2Gb in size.	BLOB	Stores unstructured binary data in the database, up to 8 terabytes in size.
VARBINARY(MAX) FILESTREAM	Stores the large objects outside of the database, in the NTFS file system. Size limited only by the volume size of the file system.	BFILE	Stores unstructured binary data, up to 8 terabytes, in operating-system files outside the database

In SQL Server VARBINARY(MAX), VARCHAR(MAX) and NVARCHAR(MAX) data types store the data in the record, unless the data is over 8Kb. When it exceeds 8Kb its behaviour changes and it stores a LOB locator in the record and holds the data in a LOB elsewhere. This locator value and the location is all handled behind the scenes and is invisible to the user.
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.

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