A guide to SQL Data Types

A guide to SQL Data Types
0

Each column in a table must have a data type. It indicates the type of value that the column stores.

Each database can accept differents data types, but in general, the list of data types is:

Strings Types

Data type Description
CHAR(n) Character string. Fixed-length n. Minimal length is 1. If you assign a value to a CHAR column containing fewer characters than the defined length, the remaining space is filled with blanks characters.
VARCHAR(n) Character string. Variable length. Maximum length n. Minimal length is 1
BINARY(n) Binary string. Fixed-length n
VARBINARY(n) or BINARY VARYING(n) Binary string. Variable length. Maximum length n

Numerics Types

Data type Description
INTEGER Integer numerical. From -2,147,483,648 to 2,147,483,647.
SMALLINT Integer numerical. From -32,768 to 32,767
BIGINT Integer numerical. From -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
DECIMAL(p,s) or NUMERIC(p,s) Exact numerical, precision p, scale s. Precision is the maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. Scale is the number of decimal digits that will be stored to the right of the decimal point. This number is subtracted from p to determine the maximum number of digits to the left of the decimal point. Example: decimal(5,2) is a number that has 3 digits before the decimal and 2 digits after the decimal.
FLOAT(p) Approximate numerical, mantissa precision p. A floating number in base 10 exponential notation. The size argument for this type consists of a single number specifying the minimum precision
REAL Approximate numerical, mantissa precision 7
FLOAT Approximate numerical, mantissa precision 16
DOUBLE PRECISION Approximate numerical, mantissa precision 16

Date and Time Types

Data type Description
DATE Stores year, month, and day values
TIME Stores hour, minute, and second values
DATETIME Stores year, month, day, hour, minute, and second values
TIMESTAMP Stores the number of seconds since the Unix epoch
TIME WITH TIME ZONE Stores time of day with time zone
TIMESTAMP WITH TIME ZONE Stores timestamp with time zone

Other Data Types

Data type Description
BOOLEAN Stores TRUE or FALSE values
ARRAY A set-length and ordered collection of elements
MULTISET A variable-length and unordered collection of elements
XML Stores XML data