You may have used different data types for SQL, but have you used them all? In this blog, we will be showing you different SQL data types that you can use in your next project.
A data type is an attribute of data that tells the compiler or interpreter how the programmer intends to use the data. Some data types are common in all languages, and some have their own data types.
SQL is no different here. There are some data types for SQL that all relational database vendors support. Some database vendors even have their own SQL data types. For eg, Microsoft SQL Server has money and smallmoney but this is not supported by other popular database vendors.
For SQL each column in a database table is required to have a name and a data type. A developer has to define the type of value that can be stored in a table column.
Also read: Built-in Python Functions List
Data types for SQL
Data types for SQL are mainly classified into three categories.
- String Data types
- Numeric Data types
- Date and time Data types
Following are different SQL data Types in MySQL, SQL Server and Oracle Databases
MySQL Data Types
Following are list of data types for SQL used in MySQL database. This is based on MySQL 8.0.
String data types in MySQL
| Data Type | Description |
| CHAR(Size) | Char is used to specifying a fixed-length string that can contain numbers, letters, and special characters. Its size parameter specifies column length in characters can be starting from 0 to 255 characters. Default is 1. |
| VARCHAR(Size) | Varchar is used to specify a variable-length string that can contain numbers, letters, and special characters. Its size can be from a length of 0 to 65535 characters. |
| BINARY(Size) | This data type is equal to CHAR() but stores binary byte strings. Its size parameter specifies the column length in the bytes. Default is 1. |
| VARBINARY(Size) | This data type is equal to VARCHAR() but stores binary byte strings. Its size parameter specifies the maximum column length in bytes. |
| TINYBLOB | For BLOBs (Binary Large Objects). Max length: 255 bytes |
| TEXT(Size) | It holds a string that can contain a maximum length of 65,535 bytes. |
| TINYTEXT | It holds a string with a maximum length of 255 characters. |
| BLOB(size) | For BLOBs (Binary Large Objects). Max length: 65,535 bytes of data |
| MEDIUMTEXT | It holds a string with a maximum length of 16,777,215 characters |
| LONGTEXT | It holds a string with a maximum length of 4,294,967,295 characters. |
| ENUM(val1, val2, val3,…) | This data type is used when a string object having only one value, chosen from a list of possible values. It contains 65535 values in an ENUM list. If you insert a value that is not in the list, a blank value will be inserted. |
| SET( val1,val2,val3,….) | It is used to specify a string that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values at one time in a SET list. |
Numeric Data Types in MySQL
All the numeric data types may have an extra option that is UNSIGNED or ZEROFILL. If you add the UNSIGNED option, MySQL disallows negative values for the column. If you add the ZEROFILL option, MySQL automatically also adds the UNSIGNED attribute to the column.
| BIT(size) | A bit-value type. The number of bits per value is specified in size. Its size can be 1 to 64. The default value is 1. |
| TINYINT(size) | A very small integer. The signed range is from -2147483648 to 2147483647. The unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255) |
| BOOL | Zero is considered as false, nonzero values are considered as true. |
| BOOLEAN | Equal to BOOL |
| SMALLINT(size) | A small integer. Signed range is from -32768 to 32767. Unsigned range is from 0 to 65535. The size parameter specifies the maximum display width (which is 255) |
| MEDIUMINT(size) | A medium integer. Signed range is from -8388608 to 8388607. Unsigned range is from 0 to 16777215. The size parameter specifies the maximum display width (which is 255) |
| INT(size) | A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255) |
| INTEGER(size) | Equal to INT(size) |
| BIGINT(size) | A large integer. Signed range is from -9223372036854775808 to 9223372036854775807. Unsigned range is from 0 to 18446744073709551615. The size parameter specifies the maximum display width (which is 255) |
| FLOAT(size, d) | A floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. This syntax is deprecated in MySQL 8.0.17, and it will be removed in future MySQL versions |
| FLOAT(p) | A floating point number. MySQL uses the p value to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT(). If p is from 25 to 53, the data type becomes DOUBLE() |
| DOUBLE(size, d) | A normal-size floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter |
| DECIMAL(size, d) | An exact fixed-point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. The maximum number for size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d is 0. |
| DEC(size, d) | Equal to DECIMAL(size,d) |
Date and Time Data Types in MySQL
| DATE | It is used to specify date format YYYY-MM-DD. Its supported range is from ‘1000-01-01’ to ‘9999-12-31’. |
| DATETIME(fsp) | It is used to specify date and time combination. Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from ‘1000-01-01 00:00:00′ to 9999-12-31 23:59:59’. |
| TIMESTAMP(fsp) | It is used to specify the timestamp. Its value is stored as the number of seconds since the Unix epoch(‘1970-01-01 00:00:00’ UTC). Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC. |
| TIME(fsp) | A data type used to specify a time format. Format: hh:mm:ss. The supported range is from ‘-838:59:59’ to ‘838:59:59’ |
| YEAR | It is used to specify a year in four-digit format. Values allowed in four digit format from 1901 to 2155, and 0000. |
SQL Server Data Types
Following are data types for SQL Server.
String Data Types
| char(n) | It is a defined width character string data type. Its size can be up to 8000 characters. |
| varchar(n) | It is a variable width character string data type. Its size can be up to 8000 characters with a storage of 2 bytes + number of characters |
| varchar(max) | It is a variable width character string data type. Its size can be up to 1,073,741,824 characters with a storage of 2 bytes + number of characters |
| text | It is a variable width character string data type. Its size can be up to 2GB of text data with a storage of 4 bytes + number of characters |
| nchar | It is a defined width Unicode string data type. Its size can be up to 4000 characters. |
| nvarchar | It is a variable width Unicode string data type. Its size can be up to 4000 characters. |
| ntext | It is a variable width Unicode string data type. Its size can be up to 2GB of text data. |
| binary(n) | It is a defined width Binary string data type. Its size can be up to 8000 bytes. |
| varbinary | It is a variable width Binary string data type. Its size can be up to 8000 bytes. |
| image | It is also a variable width Binary string data type. Its size can be up to 2GB. |
Numeric Data Types
| bit | It’s an integer that can be 0, 1, or null. |
| tinyint | It allows whole numbers from 0 to 255 with storage of 1 byte |
| Smallint | It allows whole numbers between -32,768 and 32,767 with storage of 2 bytes |
| Int | It allows whole numbers between -2,147,483,648 and 2,147,483,647 with storage of 4 bytes |
| numeric(p,s) | A Fixed precision and scale number that allows numbers from -10^38 +1 to 10^38 –1 with storage of 5-17 bytes The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. The default value is 0. |
| bigint | It allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 with storage of 8 bytes |
| float(n) | It’s used to specify floating precision number data from -1.79E+308 to 1.79E+308. The n parameter indicates whether the field should hold the 4 or 8 bytes. The default value of n is 53. |
| real | It’s a floating precision number data from -3.40E+38 to 3.40E+38 with storage of 4 bytes |
| money | It’s used to specify monetary data from -922,337,233,685,477.5808 to 922,337,203,685,477.5807 with storage of 8 bytes |
Date and Time Data Types
| datetime | It is used to specify the date and time combination. Its supports range from January 1, 1753, to December 31, 9999 with an accuracy of 3.33 milliseconds. The memory support is 8 bytes |
| datetime2 | It is used to specify the date and time combination. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds. The memory is 6-8 bytes |
| date | It is used to store date only. Its support ranges from January 1, 0001 to December 31, 9999. The memory is 3 bytes |
| time | It stores time only to an accuracy of 100 nanoseconds. The memory is 3-5 bytes |
| timestamp | It stores a unique number when a new row gets created or modified. The time stamp value is based upon an internal clock and does not correspond to real time. Each table may contain only one-time stamp variable. |
Other Data types for SQL Server
| Sql_variant | It is used for various data types except for text, timestamp, and ntext. It stores up to 8000 bytes of data. |
| XML | It stores XML formatted data. Maximum 2GB. |
| cursor | It stores a reference to a cursor used for database operations. |
| table | It stores result set for later processing. |
| uniqueidentifier | It stores GUID (Globally unique identifier). |
Oracle Data Types
Following are data types for Oracle SQL
Numeric Data type
| NUMBER(p, s) | It contains precision p and scale s. The precision p can range from 1 to 38, and the scale s can range from -84 to 127. |
| FLOAT(p) | It is a subtype of the NUMBER data type. The precision p can range from 1 to 126. |
| BINARY_FLOAT | It is used for binary precision( 32-bit). It requires 5 bytes, including length bytes. |
| BINARY_DOUBLE | It is used for double binary precision (64-bit). It requires 9 bytes, including length byte. |
String data types
| CHAR(size) | It is used to store character data within the predefined length. It can be stored up to 2000 bytes. |
| NCHAR(size) | It is used to store national character data within the predefined length. It can be stored up to 2000 bytes. |
| VARCHAR2(size) | It is used to store variable string data within the predefined length. It can be stored up to 4000 bytes. |
| VARCHAR(SIZE) | It is the same as VARCHAR2(size). You can also use VARCHAR(size), but it is suggested to use VARCHAR2(size) |
| NVARCHAR2(size) | It is used to store Unicode string data within the predefined length. We have to must specify the size of NVARCHAR2 data type. It can be stored up to 4000 bytes. |
Date and Time Data Types
| DATE | It is used to store a valid date-time format with a fixed length. Its range varies from January 1, 4712 BC to December 31, 9999 AD. |
| TIMESTAMP | It is used to store the valid date in YYYY-MM-DD with time hh:mm:ss format. |
Date and Time Data Types
| DATE | It is used to store a valid date-time format with a fixed length. Its range varies from January 1, 4712 BC to December 31, 9999 AD. |
| TIMESTAMP | It is used to store the valid date in YYYY-MM-DD with time hh:mm:ss format. |
Large Object Data Types (LOB Types)
| BLOB | It is used to specify unstructured binary data. Its range goes up to 232-1 bytes or 4 GB. |
| BFILE | It is used to store binary data in an external file. Its range goes up to 232-1 bytes or 4 GB. |
| CLOB | It is used for single-byte character data. Its range goes up to 232-1 bytes or 4 GB. |
| NCLOB | It is used to specify single-byte or fixed-length multibyte national character set (NCHAR) data. Its range is up to 232-1 bytes or 4 GB. |
| RAW(size) | It is used to specify variable length raw binary data. Its range is up to 2000 bytes per row. Its maximum size must be specified. |
| LONG RAW | It is used to specify variable length raw binary data |
Above were some of the datatypes from three of the popular SQL database vendors, there are others as well which you can read into like Microsoft Access, etc.
If you like this blog do bookmark this blog for your future reference.
