Appendix C: Data Types

This appendix lists the available data types for each Relational Database Management System (RDBMS) and briefly describes the most commonly used types for each database platform.

RDBMS Data Types

Table C-1 summarizes the column data types available in the various RDBMSs used in this book.

Table C-1: Column Data Types
Category	Access	SQL Server	Oracle	MySQL	DB2
Binary Object	OLE Object, Memo	binary, varbinary, Image	bfile, BLOB, long raw	blob, text	blob
Bit	Yes/No (synonyms for tinyint(1))	bit	byte	bit, bool	N/A
Character	Text, Memo	char, nchar, varchar, nvarchar, text, ntext	char, nchar, long, varchar, nvarchar, CLOB	char, nchar, varchar, nvarchar, mediumtext, longtext	char, varchar, longvarchar, CLOB
Currency	Currency	money, smallmoney	N/A	N/A	N/A
Numeric	Number	bigint, decimal, float, int, numeric, real, smallint, tinyint	number (decimal, numeric, double precision, float, integer, smallint)	bigint, decimal, double, int, mediumint, numeric, real, smallint, tinyint	bigint, decimal/numeric, double/float, integer, smallint, real
Temporal	Date/Time	datetime, smalldatetime	date	datetime, time, timestamp, year	date, time, timestamp
Unique	AutoNumber	unique identifier	N/A	N/A	N/A

SQL Server Data Types

Table C-2 describes the SQL Server data types.

Table C-2: SQL Server Data Types
Category	Data Types	Description
Binary Object	binary, varbinary	The binary type stores fixed-length binary data with a maximum length of 8,000 bytes. varbinary is a variable-length equivalent.
Binary Object	Image	Variable-length binary data that can store up to 2^31 - 1 bytes.
Bit	bit	Integer data, storing either 0 or 1.
Character	char, varchar	char stores fixed-length non-Unicode data up to 8,000 characters. varchar is the variable-length equivalent.
	nchar, nvarchar	nchar is the Unicode equivalent of char, with a maximum length of 4,000 characters. nvarchar is the variable-length equivalent.
	text, ntext	Variable-length non-Unicode character data up to 2^31 - 1 characters. ntext stores Unicode character data up to 2^30 - 1 characters.
Currency	money, small money	Both store monetary values. Range of acceptable values are: money: -2^63 to 2^63 - 1 smallmoney: -214,748.3648 to 214,748.3647 Both are accurate to one ten-thousandth of a unit.
Numeric	bigint, int, smallint, tinyint	All used for storing integers of varying sizes: bigint: -2^63 to 2^63 - 1 int: -2^31 to 2^31 - 1 smallint: -2^15 to 2^15 - 1 tinyint: 0 to 255
	decimal, numeric	Fixed-precision and scale-numeric data in the range: -10^38 +1 to 10^38 -1 Both are functionally equivalent.
	float, real	Floating-point precision numbers with ranges: float: -1.79E + 308 to -2.23E - 308, 0 and 2.23E + 308 to 1.79E + 308. real: -3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38.
Temporal	datetime, smalldatetime	Both store date and time data. Values range from: datetime: 01/01/1752 to 12/31/9999 smalldatetime: 01/01/1900 to 06/06/2079 datetime is accurate to 3.33 milliseconds, and smalldatetime is accurate to one minute.
Unique	unique identifier	A Globally Unique Identifier (GUID).

Oracle Data Types

Table C-3 describes the Oracle data types.

Table C-3: Oracle Data Types
Category	Data Types	Description
Binary Object	bfile	Contains a locator to a large external binary file of up to 4 gigabytes (GB).
	BLOB	A binary large object of up to 4GB.
	long raw	Raw binary data of up to 2GB.
	raw	Raw binary data of up to 2,000 bytes.
Bit	byte	Stores one byte
Character	char	Stores fixed-length non-Unicode character data up to 2,000 bytes.
	nchar, nvarchar	nchar is the Unicode equivalent of char, with a maximum length of 2,000 characters. nvarchar is the variable-length equivalent, but with a maximum length of 4,000 bytes.
	long, CLOB, NCLOB	long stores variable-length character data up to 2GB. CLOB stores up to 4GB of single-byte characters. Supports fixed- and variable-width character sets. NCLOB is the Unicode equivalent.
	rowid	A hexadecimal string value that represents the address of a row in a table.
	urowid	A hexadecimal string value that represents the logical address of a row in a table ordered by index.
	varchar	Variable-length character data of up to 4,000 bytes.
Numeric	number	The number data type stores all kinds of numbers, both fixed and floating point, positive, negative, and zero. All numeric types are essentially numbers, but with varying scale and precision. When used directly, number can store values in the range of 10^-130 and 10^126 - 1.
	integer, smallint	Both are equivalent and store integer values in the range -10^38 to 10^38.
	decimal, numeric	Both are synonyms for number.
	float, real	Both are identical and store floating-point precision numbers with range of -10^38 to 10^38.
	double precision	Floating-point precision number with a binary precision of 126.
Temporal	date	Stores a date and time between 01/01/4712 BC to 12/31/9999 AD.
	timestamp	Used to represent the year, month, day, hour, minute, or second component of a date.
	interval year to month	Stores the period of time between two dates in years and months.
	interval day to second	Stores the period between two times in days, hours, minutes, and seconds.

DB2 Data Types

Table C-4 describes the DB2 data types.

Table C-4: DB2 Data Types
Category	Data Types	Description
Binary Object	BLOB	Contains a variable-length binary string of up to 2GB.
Binary Object	vargraphic	Variable-length graphic string of up to 16,336 characters.
Character	char	Fixed-length character data of up to 254 characters.
	clob	Variable-length character data of up to 2GB.
	dbclob	A variable-length character large object of up to 1,073,741,823 characters.
	varchar	Variable-length character data of up to 32,672 bytes.
External Data	datalink	Represents a link to an external data source.
Numeric	bigint	An 8-byte integer in the range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
	integer	A 4-byte integer in the range -2,147,483,648 to 2,147,483,647.
	smallint	A 2-byte integer in the range -32,768 to 32,767.
	decimal	An exact decimal value with fixed precision and scale in the range -10^31+1 to 10^31-1.
	real	A single-precision floating-point number in the range -3.402E+38 to -1.175E-37, zero, or 1.175E-37 to 3.402E+38.
	double	A double-precision floating-point number in the range -1.79769E+308 to -2.2250738585072014E-308, zero, or 2.225E-307 to 1.79769E+308.
Temporal	Date	Represents a date between 01/01/0001 and 12/31/9999.
	Time	Represents a time in hours, minutes, and seconds.
	Timestamp	Represents a date and time, including a fractional microsecond component.

MySQL Data Types

Table C-5 describes the MySQL data types.

Table C-5: MySQL Data Types
Category	Data Types	Description
Binary Object	longtext/longblob	A variable-length text or BLOB field with a maximum of 4,294,967,295 characters.
	mediumtext/mediumblob	A variable-length text or BLOB field with a maximum of 16,777,215 characters.
	text/blob	A variable-length text or BLOB field with a maximum of 65,535 characters.
	tinytext/tinyblob	A variable-length text or BLOB field with a maximum of 255 characters.
Bit	tinyint	An integer in the range -128 to 127 (signed) or 0 to 255 (unsigned).
Character	char	A fixed-length string in the national character set.
Character	varchar	A variable-length string in the national character set.
Composite Types	enum	A string object that can have one out of a number of possible values.
Composite Types	set	A string object that can have none, one, or more values out of a number of possible values.
Numeric	bigint	An integer in the range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (signed) or 0 to 18,446,744,073,709,551,615 (unsigned).
	decimal	An unpacked floating-point number (stored as a string).
	double	A double-precision floating-point number in the range -1.7976931348623157E+308 to -1.175494351E-38, zero, or 2.2250738585072014E-308 to 1.7976931348623157E+308.
	float	A floating-point value. This can represent either single- or double-precision numbers.
	int	An integer in the range -2,147,483,648 to 2,147,483,647 (signed) or 0 to 4,294,967,295 (unsigned).
	mediumint	An integer in the range -8,388,608 to 8,388,607 (signed) or 0 to 16,777,215 (unsigned).
	smallint	An integer in the range -32,768 to 32,767 (signed) or 0 to 65,535 (unsigned).
Temporal	date	A date in the range 01/01/1000 to 12/31/9999.
	datetime	A combined date and time value.
	time	A time in the range '-838:59:59' to '838:59:59'.
	timestamp	A timestamp value reflecting the number of seconds since midnight on January 1, 1970.
	year	A year in two- or four-digit format.

Access Data Types

Table C-6 shows the Access field types listed with JET SQL equivalents.

Table C-6: Access Field Types
Category	Data Types	Description
Binary Object	OLE Object (longbinary)	A variable-length text or BLOB field with a maximum of 4,294,967,295 characters. Longbinary is the Jet name for this data type.
Boolean	Yes/No (boolean)	Stores values of -1 for yes/true, 0 for no/false. Any non-zero value input into this field is stored as -1.
Character	Text (char, varchar, string)	Can store up to 255 characters. Jet equivalents of a Text field are alphanumeric, char, varchar, and string.
Character	Memo (longtext, longchar, memo, note)	Can store up to 64,000 characters of alphanumeric data.
Numeric	Number (byte, int / integer, single, long, double)	The number field can be defined as one of the Jet types of byte, int, integer, single, double, and long. Sizes are: byte: 1 byte int, integer: 2 bytes single, long: 4 bytes double: 8 bytes
Currency	Currency (currency, money)	Precision up to 4 decimal places. Eight bytes.
Unique	Autonumber (counter, autoincrement, GUID)	counter and autoincrement use a long field type with a unique constraint and autonumbering functionality. Size: 4 bytes. This is the default size when generating an autonumbering field in Access table design view. GUID stores up to 16 bytes.
Other	Hyperlink (longtext, longchar, memo, note)	Stores up to 2,048 bytes of Uniform Resource Locator (URL) data.