The data type of a column defines what value the column can hold: integer, character, money, date and time, binary, and so on.

SQL data types can be broadly divided into following categories.

1. Numeric data types such as int, tinyint, bigint, float, real etc.
2. Date and Time data types such as Date, Time, Datetime etc.
3. Character and String data types such as char, varchar, text etc.
4. Unicode character string data types, for example nchar, nvarchar, ntext etc.
5. Binary data types such as binary, varbinary etc.
6. Miscellaneous data types – clob, blob, xml, cursor, table etc.

SQL Data Types important points

• Not all data types are supported by every relational database vendors. For example, Oracle database doesn’t support DATETIME and MySQL doesn’t support CLOB data type. So while designing database schema and writing sql queries, make sure to check if the data types are supported or not.
• Data types listed here doesn’t include all the data types, these are the most popularly used data types. Some relational database vendors have their own data types that might be not listed here. For example, Microsoft SQL Server has money and smallmoney data types but since it’s not supported by other popular database vendors, it’s not listed here.
• Every relational database vendor has it’s own maximum size limit for different data types, you don’t need to remember the limit. Idea is to have the knowledge of what data type to be used in a specific scenario.

Let’s look into different categories of sql data types in detail.

SQL Numeric Data Types

Datatype	From	To
bit	0	1
tinyint	0	255
smallint	-32,768	32,767
int	-2,147,483,648	2,147,483,647
bigint	-9,223,372,036,854,775,808	9,223,372,036,854,775,807
decimal	-10^38 +1	10^38 -1
numeric	-10^38 +1	10^38 -1
float	-1.79E + 308	1.79E + 308
real	-3.40E + 38	3.40E + 38

SQL Date and Time Data Types

Datatype	Description
DATE	Stores date in the format YYYY-MM-DD
TIME	Stores time in the format HH:MI:SS
DATETIME	Stores date and time information in the format YYYY-MM-DD HH:MI:SS
TIMESTAMP	Stores number of seconds passed since the Unix epoch (‘1970-01-01 00:00:00’ UTC)
YEAR	Stores year in 2 digit or 4 digit format. Range 1901 to 2155 in 4-digit format. Range 70 to 69, representing 1970 to 2069.

SQL Character and String Data Types

Datatype	Description
CHAR	Fixed length with maximum length of 8,000 characters
VARCHAR	Variable length storage with maximum length of 8,000 characters
VARCHAR(max)	Variable length storage with provided max characters, not supported in MySQL
TEXT	Variable length storage with maximum size of 2GB data

Note that all the above data types are for character stream, they should not be used with unicode data.

SQL Unicode Character and String Data Types

Datatype	Description
NCHAR	Fixed length with maximum length of 4,000 characters
NVARCHAR	Variable length storage with maximum length of 4,000 characters
NVARCHAR(max)	Variable length storage with provided max characters
NTEXT	Variable length storage with maximum size of 1GB data

Note that above data types are not supported in MySQL database.

SQL Binary Data Types

Datatype	Description
BINARY	Fixed length with maximum length of 8,000 bytes
VARBINARY	Variable length storage with maximum length of 8,000 bytes
VARBINARY(max)	Variable length storage with provided max bytes
IMAGE	Variable length storage with maximum size of 2GB binary data

SQL Miscellaneous Data Types

Datatype	Description
CLOB	Character large objets that can hold up to 2GB
BLOB	For binary large objects
XML	for storing xml data
JSON	for storing JSON data

That’s all for a quick roundup on SQL data types.