SQL Data Types

A SQL data type is associated with each column in table. It determines the type of data the column can hold.

We, the SQL developer needs to decide the data type for each column. Once data type is specified for a column then SQL understands and allows the column to hold only that type of data.

The following table lists the general data types in SQL:

SQL Data Types
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
INTEGER Integer numerical (no decimal). Precision 10
DECIMAL(p,s) Exact numerical, precision p, scale s. Example: decimal(5,2) is a number that has 3 digits before the decimal and 2 digits after the decimal
BIGINT Integer numerical (no decimal). Precision 19
SMALLINT Integer numerical (no decimal). Precision 5
VARBINARY(n) or BINARY VARYING(n) Binary string. Variable length. Maximum length n
BOOLEAN Stores TRUE or FALSE values
INTEGER(p) Integer numerical (no decimal). Precision p
REAL Approximate numerical, mantissa precision 7
NUMERIC(p,s) Exact numerical, precision p, scale s. (Same as 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
FLOAT Approximate numerical, 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 Composed of a number of integer fields, representing a period of time, depending on the type of interval
ARRAY A set-length and ordered collection of elements
MULTISET A variable-length and unordered collection of elements
XML Stores XML data

There are different definitions of data types among different Databases.

Note: Data types might have different names in different database and even if the names are same they may vary in the size and other details! So Always check for the corresponding documentation before getting started!

SQL Data Type Quick Reference - SQL General Data Types

Comments