SQL General Data Types

Data types define the kind of values stored in a column.

SQL General Data Types

Each column in a database table is required to have a name and a data type.

SQL developers must decide what types of data will be stored in each column when creating an SQL table. A data type is a label, a guide that helps SQL understand what type of data each column is expected to store, and it also identifies how SQL interacts with the stored data.

The following table lists the common data types in SQL:

Data type Description
CHARACTER(n) Character/string. Fixed length n.
VARCHAR(n) or
CHARACTER VARYING(n)		 Character/string. Variable length. Maximum length n.
BINARY(n) Binary string. Fixed length n.
BOOLEAN Stores TRUE or FALSE values
VARBINARY(n) or
BINARY VARYING(n)		 Binary string. Variable length. Maximum length n.
INTEGER(p) Integer value (no decimal point). Precision p.
SMALLINT Integer value (no decimal point). Precision 5.
INTEGER Integer value (no decimal point). Precision 10.
BIGINT Integer value (no decimal point). Precision 19.
DECIMAL(p,s) Exact numeric value, precision p, scale s. For example: decimal(5,2) is a number with 3 digits before the decimal point and 2 digits after the decimal point.
NUMERIC(p,s) Exact numeric value, precision p, scale s. (Same as DECIMAL)
FLOAT(p) Approximate numeric value, mantissa precision p. A floating-point number in base-10 exponential notation. The size parameter of this type consists of a single number specifying the minimum precision.
REAL Approximate numeric value, mantissa precision 7.
FLOAT Approximate numeric value, mantissa precision 16.
DOUBLE PRECISION Approximate numeric value, mantissa precision 16.
DATE Stores year, month, and day values.
TIME Stores hour, minute, and second values.
TIMESTAMP Stores year, month, day, hour, minute, and second values.
INTERVAL Consists of several integer fields, representing a period of time, depending on the type of interval.
ARRAY Fixed-length ordered collection of elements
MULTISET Variable-length unordered collection of elements
XML Stores XML data

SQL Data Types Quick Reference Manual

However, different databases offer different choices for data type definitions.

The following table shows the common names of some data types on various database platforms:

Data type Access SQLServer Oracle MySQL PostgreSQL
boolean Yes/No Bit Byte N/A Boolean
integer Number (integer) Int Number Int
Integer		 Int
Integer
float Number (single) Float
Real		 Number Float Numeric
currency Currency Money N/A N/A Money
string (fixed) N/A Char Char Char Char
string (variable) Text (<256)
Memo (65k+)		 Varchar Varchar
Varchar2		 Varchar Varchar
binary object OLE Object Memo Binary (fixed up to 8K)
Varbinary (<8K)
Image (<2GB)		 Long
Raw		 Blob
Text		 Binary
Varbinary

lamp

Comments:In different databases, the same data type may have different names. Even if the name is the same, the size and other details may differ!Always check the documentation!


Other extensions