Summary -
In this topic, we described about the below sections -
SQL Data Types
In Database, each column or field of table holds certain type of value. The values may be of different types like numerical value, names, date etc… While creating table, the type for table column should be specified based on requirement. Based on the type of the data storing in the particular column, we should specify the corresponding data type to the column.
SQL Data types specifies the type of the value that can be stored in specified table column. For example, if a column is to store string values, then the column should be declared with VARCHAR (length_of_string).
SQL allows certain type of data types as listed below -
- Numeric Data type
- String Data type
- Date and Time Data type
- Unicode character string data types
- Binary Data types
- Miscellaneous Data types
Important points to Note -
- There are different relational database vendors in the market. For example - Oracle, Microsoft etc.
- The database from different vendors have different database design schemas. Every relational database does not support all the data types and supporting data types are depends on their design. For example - MySQL does not support CLOB data type and Oracle database does not support DATETIME.
- Data types specified below does not include all the data types. Those are most popularly used data types. Some relational database vendors have their own data types. However, those are not supported by other relational data base vendors. For example, Microsoft SQL server has its own data types "money" and "smallmoney".
- Every relational database vendor has its own size limits for its data types. The size limits specified below are just for your knowledge and no need to remember the limit.
Numeric Datatype
| Data Type | Description | Syntax | From | To |
|---|---|---|---|---|
| BIT | A bit-value type. The number of bits that can hold isspecified in size. The SIZE parameter can hold the range is from 1 to 64. The default value is 1. |
BIT(SIZE) | 0 | 1 |
| TINYINT | A very small integer. Uses one-byte memory. Size parameter specifies the maximum display width is 255. |
TINYINT(SIZE) | -128 | 127 |
| TINYINT(SIZE) UNSIGNED | 0 | 255 | ||
| SMALLINT | A small integer. | SMALLINT(SIZE) | -32768 | 32767 |
| SMALLINT(SIZE) UNSIGNED | 0 | 65535 | ||
| INT or INTEGER | An integer. Uses four bytes of memory. |
INT(SIZE) or INTEGER(SIZE) | -2^31 (-2147483648) |
2^31 – 1 (2147483647) |
| INT(SIZE) UNSIGNED) or INTEGER(SIZE) UNSIGNED | (-2147483648) 0 |
4294967295 | ||
| BIGINT | A large integer. Uses 8-bytes of memory. |
BIGINT(SIZE) | -9223372036854775808 | 9223372036854775807 |
| BIGINT(SIZE) UNSIGNED | 0 | 18446744073709551615 | ||
| DECIMAL | A flexi-point number. Uses 5 to 17 bytes of memory. The default value for SIZE is 10 and d is 0. |
DECIMAL (SIZE, D) |
-10^38 +1 | 10^38 +1 |
| DEC | Equals to DECIMAL | DEC (SIZE, D) | -10^38 +1 | 10^38 +1 |
| Numeric | Numeric types more than BIGINT. | NUMERIC(SIZE) | -10^38 +1 | 10^38 +1 |
| FLOAT | Floating-point types. The maximum value of n is 53. If the n value lies between 1 to 24, the float data type applied. If the n values lie between 25 to 53, double data is considered. Uses 4 to 8 bytes memory based on n value. Mantissa precision is 7 digits. |
FLOAT(n) | -1.79E + 308 | 1.79E + 308 |
| REAL | Floating-point data. Uses 4 bytes of memory. Mantissa precision is 15 digits. |
REAL | -3.40E + 38 | 3.40E + 38 |
| DOUBLE | Norma size floating-point number. The total number of digits specified in SIZE. The number of digits specified after decimal point is specified in d. |
DOUBLE (SIZE, D) | ||
| DOUBLE PRECISION | DOUBLE PRECISION (SIZE, D) |
Character and String Datatype
| Data Type | Description | Syntax | Max Length |
|---|---|---|---|
| CHAR | Fixed length character strings. | CHAR(n) | 8000 characters |
| VARCHAR | Variable length character strings. | VARCHAR(n) | 8000 characters |
| VARCHAR (max) | Variable length storage with provided max characters. This is not supported by MySQL. |
VARCHAR (max) | 2E + 31 characters |
| TEXT | Variable-length storage of text. | TEXT | 2GB data |
Date and Time Datatype
| Data Type | Description | Syntax | Format |
|---|---|---|---|
| DATE | Stores date | DATE | YYYY-MM-DD |
| TIME | Stores time | TIME | HH:MI: SS |
| DATETIME | Stores date and time information in the format | DATETIME | YYYY-MM-DD HH:MI: SS |
| TIMESTAMP | Stores number of seconds passed | TIMESTAMP | YYYY-MM-DD HHLMI: SS UTC |
| YEAR | Stores year in 2-digit or 4-digit format. | YEAR | YYYY |
Note :- The above data types are for character strings and not for Unicode character strings.
Unicode Character and string data types
| Data Type | Description | Syntax | Max length |
|---|---|---|---|
| NCHAR | Fixed length Unicode Character strings | NCHAR(n) | 4000 characters |
| NVARCHAR | Variable length Unicode Character strings | NVARCHAR(n) | 4000 characters |
| NVARCHAR (max) | Variable length storage with provided max Unicode characters. This is not supported by MySQL. | NVARCHAR (max) | 2E + 31 characters |
| TEXT | Variable-length storage of Unicode text. | TEXT | 1GB data |
Note! The above data types are for Unicode character strings and not for regular character strings.
Binary Datatype
| Data Type | Description | Syntax | Max length |
|---|---|---|---|
| BINARY | Fixed length binary data | BINARY(n) | 8000 characters |
| VARBINARY | Variable length binary data | VARBINARY(n) | 8000 characters |
| VARBINARY (max) | Variable length storage with provided max Unicode characters. This is not supported by MySQL. | VARBINARY (max) | 2E + 31 characters |
| IMAGE | Variable length storage | IMAGE | 2GB Binary data |
Miscellaneous Datatype
| Data Type | Description | Syntax | Max length |
|---|---|---|---|
| CLOB | Character large objects | CLOB | 2GB |
| BLOB | For binary large objects | BLOB | |
| XML | for storing xml data | XML | |
| JSON | for storing JSON data | JSON |