SQL Data Types define the type of value that can be stored in a table column. For example, if we want a column to store only integer values, then we can define it’s data type as int.
Table of Contents
SQL Data Types
SQL data types can be broadly divided into following categories.
- Numeric data types such as int, tinyint, bigint, float, real etc.
- Date and Time data types such as Date, Time, Datetime etc.
- Character and String data types such as char, varchar, text etc.
- Unicode character string data types, for example nchar, nvarchar, ntext etc.
- Binary data types such as binary, varbinary etc.
- 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
moneyandsmallmoneydata 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.
Reference: Oracle Database Data Types, mySQL Data Types
Mr SQL says
Type BIT is not a numeric data type, its binary. Also it is no longer part of SQL standard. So better to remove it.
Type YEAR does not exist in SQL standard. Also never use year numbers with only 2 digits (remember year2k bug?)
You miss the type BOOLEAN which can have values true or false. It is fundamentally different from BIT.
You miss the type INTERVAL of which there are 2 basic ones: INTERVAL SECONDS (can include fractions) and INTERVAL MONTHS.
Type TEXT and NTEXT are old synonyms for CLOB, so use CLOB (or CHARACTER LARGE OBJECT).
Type IMAGE is an old synonym for BLOB, so use BLOB (or BINARY LARGE OBJECT).
sadiqullah says
thank’s so much
Blabla says
Very good my son..
God bless you
Shirajul says
Hello,
Is it possible to enter an Array for a value in a table?
Is there an ARRAY column type?
Kind Regards,
Sew says
But why?? I think better idea is create another table and make refference