SQL Data Types denotes the type of data that can be stored in a table column. Each column in a table is associated with a data type. For example, for storing integer types data in a column, you need to define it’s datatype as int.
Whenever you are creating a table in the database, you need to specify datatypes for each of column. You can choose the datatypes as per your requirement.
SQL Data Types
SQL Data Types can be broadly divided into the following categories.
- Numeric Data Types such as int, bigint, tinyint, float, real, etc.
- Character String Data Types such as char, varchar, string, etc.
- Date and Time Data Types such as Date, Time, DateTime, etc.
- Unicode Character Data Types such as nchar, nvarchar, etc.
- Binary Data Types such as binary, varbinary, etc.
- Miscellaneous data types – clob, blob, xml, cursor, table etc.
SQL Data Types Important points
Following are the some of the important points regarding SQL Data Types.
- All the data types are not supported by every database vendors. For example, Microsoft SQL Server database has
moneyandsmallmoneydatatypes which are not supported by other relational database vendors. - Each database vendor has own maximum size limits for different data types. However you don’t have to remember all the limits.
List of the most popular SQL Data Types
SQL Numeric Data Types
| Datatype | From | To |
| bit | 0 | 1 |
| tinyint | 0 | 255 |
| smallint | -32,768 | 32,767 |
| int | -2,14,74,83,648 | 2,14,74,83,647 |
| bigint | -9,223,372,036, 854,775,808 | 9,223,372,036, 854,775,807 |
| decimal | 1E+38 | 10^38 -1 |
| numeric | 1E+38 | 10^38 -1 |
| float | -1.79E + 308 | 1.79E + 308 |
| real | -3.40E + 38 | 3.40E + 38 |
SQL Character 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 length of 2,147,483,647 characters. |
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 Unicode Character 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 |
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 objects that can hold up to 2GB |
| BLOB | For binary large objects |
| XML | for storing xml data |
| JSON | for storing JSON data |
Summary: In this tutorial, you have learned about the basic idea about SQL Data Types and their uses.
1 thought on “SQL Data Types”