Code has been added to clipboard!

SQL Data Types With a Quick Reference to Different Databases

Reading time 3 min
Published Aug 9, 2017
Updated Oct 3, 2019

SQL data types make it easy to specify data in a particular column of the table more accurately. You can set a column to store only dates, only characters, etc.

The data type that you set enables SQL to identify how to interact with that part of data. Data types in SQL vary due to different database systems and slight differences in the syntax used. You should always check with the specific database documentation to see whether the data type you're planning to set is supported.

SQL Data Types: Main Tips

  • A data type describes the type of value to store in a column.
  • Each database column must have data type and name specified – this needs to be done before an SQL table can be created.
  • Data types help SQL interact with column data properly.
  • Different databases offer different data type definitions.
  • Data types might not act the same when used on different databases, regardless of their names being the same.

General Data Types

Data type Description
CHARACTER(n) Fixed-length n. Character string.
CHARACTER VARYING(n) or VARCHAR(n) Character string. Maximum length is n. Variable length.
BINARY(n) Fixed-length n. Binary string.
BOOLEAN Stores FALSE or TRUE values.
BINARY VARYING(n) or VARBINARY(n) Binary string. Maximum length n. Variable length.
INTEGER(p) Precision p. No decimal (integer numerica).
SMALLINT Precision 5. No decimal (integer numerica).
INTEGER Precision 10. No decimal (integer numerica).
BIGINT Precision 19. No decimal (integer numerica).
DECIMAL(p,s) Exact numerical, scale s., precision p, scale s. Example: decimal(6,2) is a number that has 4 digits before the decimal and 3 digits after the decimal.
NUMERIC(p,s) Same as DECIMAL. Exact numerical, scale s., precision p, scale s.
FLOAT(p) Approximate numerical, mantissa precision p. The size argument consists of the minimum precision number.
REAL Approximate numerical, mantissa precision 7.
FLOAT Approximate numerical, mantissa precision 16.
DOUBLE PRECISION Approximate numerical, mantissa precision 16.
DATE Stores day, month, and year values.
TIME Stores second, minute, and hour values.
TIMESTAMP Stores second, minute, hour, day, month, year values.
INTERVAL Composed of a number of integer fields, representing a period of time, depending on the interval type.
ARRAY An ordered collection of elements and a set-length.
MULTISET An unordered collection of elements and a variable-length.
XML XML data is storage.
Theory is great, but we recommend digging deeper!

Quick Reference to SQL Data Types

In this table, you see some of the most popular data type names in different 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

SQL Data Types: Summary

  • Data types describe the type of data stored in a particular column.
  • Prior to creating an SQL table, you must specify each column data type and name.
  • Data types make it very easy to interact with data in certain columns.
  • There are different data type definitions for different databases.
  • Despite having identical names, data types might work differently on different databases.