In SQL, each column (in a table) has a data type. This restricts the type of data that can be stored in a column.
For example, if the data type of a column is INTEGER, we can only store integer values such as 0, 1, -1 etc. in that column.
Various databases support various data types and some of most used types are discussed here.
Let's take a look at commonly used data types used in SQL.
SQL Server Data Types
Different data types supported in SQL Server are,
Integer Data Types
Data Type | Description |
---|---|
BIT |
can store single bit (0 or 1) or NULL |
TINYINT |
can store numbers from 0 to 255 |
SMALLINT |
can store numbers between -32,768 to 32,767 |
INT |
can store numbers between -2,147,483,648 and 2,147,483,647 |
BIGINT |
can store numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 |
String Data Types
Data Type | Description |
---|---|
CHAR(x) |
can store characters of fixed length (max 8000 chars) |
VARCHAR(x) |
can store characters up to given length (max 8000 chars) |
TEXT |
can store characters up to 2GB size |
IMAGE |
can store binary string up to 2 GB size |
Date and Time Data Types
Data Type | Description |
---|---|
DATETIME |
can store date from January 1, 1753 to December 31, 9999 with time |
DATETIME2 |
can store date from January 1, 0001 to December 31, 9999 with time |
DATE |
can store only date from January 1, 0001 to December 31, 9999 |
TIME |
can store only time |
Note: There are many other data types supported in SQL Server. To read more, visit SQL Server documentation.
MySQL Data Types
Different data types supported in MySQL are,
Integer Data Types
Data Type | Description |
---|---|
BIT(x) |
can store x-bit values. x can range from 1 to 64 |
TINYINT |
can store numbers from -128 to 127 |
SMALLINT |
can store numbers from -32768 to 32767 |
MEDIUMINT |
can store numbers from -8,388,608 to 8,388,607 |
INT |
can store numbers from -2,147,483,648 to 2,147,483,647 |
BIGINT |
can store numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
DECIMAL(x, y) |
can store decimal number of total x digits (max up to 65) and y digits (max up to 30) among them is followed by decimal point |
String Data Types
Data Type | Description |
---|---|
CHAR(x) |
can store characters of fixed length (max 8000 chars) |
VARCHAR(x) |
can store characters up to given length (max 8000 chars) |
BINARY(x) |
can store binary strings of fixed length |
VARBINARY(x) |
can store binary strings up to given length |
TINYTEXT |
can store characters up to 255 characters |
TEXT(x) |
can store characters up to given characters (max upto 65,535 bytes) |
MEDIUMTEXT |
can store characters up to 16,777,215 characters |
LONGTEXT |
can store characters up to 4,294,967,295 characters |
BLOB(x) |
can store binary large object up to 65,535 bytes |
MEDIUMBLOB |
can store binary large object up to 16,777,215 bytes |
LONGBLOB |
can store binary large object up to 4,294,967,295 bytes |
Date and Time Data Types
Data Type | Description |
---|---|
DATE |
can store date in format of YYYY-MM-DD and ranged from 1000-01-01 to 9999-12-31 |
DATETIME |
can store date and time in format of YYYY-MM-DD hh:mm:ss |
TIME |
can store only time in format of hh:mm:ss and ranged from -838:59:59 to 838:59:59 |
YEAR |
can store year in 4 digits format and ranged from 1901 to 2155 |
TIMESTAMP |
can store timestamp from the current time zone to UTC |
Note: There are more data types supported in MySQL. To read more, visit MySQL documentation.
PostgreSQL Data Types
Different data types supported in PostgreSQL are,
Integer Data Types
Data Type | Description |
---|---|
SMALLINT |
can store numbers between -32,768 to 32,767 |
INTEGER |
can store numbers between -2,147,483,648 and 2,147,483,647 |
BIGINT |
can store numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 |
DECIMAL |
can store numbers up to 131072 digits before the decimal point; up to 16383 digits after the decimal point |
SMALLSERIAL |
can store small auto incrementing number from 1 to 32767 |
SERIAL |
can store auto incrementing number from 1 to 2147483647 |
BIGSERIAL |
can store big auto incrementing number from 1 to 9223372036854775807 |
String Data Types
Data Type | Description |
---|---|
CHAR(x) |
can store characters of fixed length |
VARCHAR(x) |
can store characters up to given length |
TEXT |
can store characters without limitation |
Date and Time Data Types
Data Type | Description |
---|---|
TIMESTAMP |
can store date and time |
DATE |
can store only date |
TIME |
can store only time |
Note: There are more data types supported in PostgreSQL. To read more, visit PostgreSQL documentation
Oracle Data Types
Different data types supported in Oracle are,
Integer Data Types
Data Type | Description |
---|---|
NUMBER |
can store numbers |
String Data Types
Data Type | Description |
---|---|
CHAR(x) |
can store characters of fixed length up to 2000 bytes or characters |
VARCHAR(x) |
can store characters up to given length (max is 4000 bytes or characters) |
LONG |
can store characters up to 2 GB |
Date and Time Data Types
Data Type | Description |
---|---|
TIMESTAMP |
can store date and time |
DATE |
can store only date from January 1, 4712 BC to December 31, 9999 AD |
TIME |
can store only time |
Note: There are more data types supported in Oracle. To read more, visit Oracle documentation