SQL Datatypes represent the nature of data stored in the database. The data type is the set of representable values. It is also known as the attribute that specifies the object’s data type.
Each column, variable, and expression has the related data type in the SQL while creating the table. For example, in the last tutorial, we have seen the SQL Date Time Functions.
SQL Datatypes
SQL Datatypes are mainly classified into six categories for every database.
- String Datatypes
- Numeric Datatypes
- Date and Time Datatypes
- Binary data types like binary, varbinary, etc.
- Unicode character string datatypes such as nchar, nvarchar, ntext, etc.
- Miscellaneous data types include clob, blob, XML, cursor, table, etc.
When creating the table, the SQL developer must decide what data will be stored inside each column. The data type is the guideline for SQL to understand what kind of data is expected inside each column, and it also identifies how the SQL will interact with the stored data.
Key points
- Relational database vendors support not all data types. For example, the Oracle database doesn’t support a DATETIME, and MySQL doesn’t support a CLOB data type. So while designing the database schema and writing the SQL queries, make sure to check if the data types are supported or not.
- Datatypes listed here don’t include all the data types; these are the most popularly used data types. In addition, some relational database vendors have data types that might not be listed here. For example, the Microsoft SQL Server has money and smallmoney data types, but since other favorite database vendors do not support it, it’s not listed here.
- Every relational database vendor has its maximum size limit for the different data types, and you don’t need to remember a limit. The idea is to know the data type to use in a specific scenario.
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 digits 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 a maximum length of 8,000 characters |
| VARCHAR | Variable-length storage with a maximum length of 8,000 characters |
| VARCHAR(max) | Variable-length storage with provided max characters is not supported in the MySQL |
| TEXT | The variable-length storage with a maximum size of 2GB of data |
Note that all the above data types are for the character stream; they should not be used with Unicode data.
SQL Unicode Character and String Data Types
| DATATYPE | DESCRIPTION |
|---|---|
| NCHAR | Fixed length with a maximum length of 4,000 characters |
| NVARCHAR | Variable-length storage with a maximum length of 4,000 characters |
| NVARCHAR(max) | Variable-length storage with provided max characters |
| NTEXT | Variable-length storage with a maximum size of 1GB of data |
Note that the above data types are not supported in the MySQL database.
SQL Binary Data Types
| DATATYPE | DESCRIPTION |
|---|---|
| BINARY | Fixed length with a maximum length of 8,000 bytes |
| VARBINARY | Variable-length storage with a maximum length of 8,000 bytes |
| VARBINARY(max) | Variable-length storage with provided max bytes |
| IMAGE | Variable-length storage with a maximum size of 2GB binary data |
SQL Miscellaneous Data Types
| DATATYPE | DESCRIPTION |
|---|---|
| CLOB | Character large objects that can hold up to 2GB |
| BLOB | For large binary objects |
| XML | for storing XML data |
| JSON | for storing JSON data |
MySQL String Datatypes
| Varchar(size) | It is used for specifying a variable-length string that can contain numbers, letters, and special characters. Its size limit is 0 to 65535 characters. |
| Char(size) |
It is used for specifying a fixed-length string that can contain numbers, letters, and special characters. By default, it can hold 1 character. Its size limit is 0 to 255 characters. |
| VARBINARY(size) |
It is similar to VARCHAR(); the only difference is that it stores binary byte strings. The size parameter specifies the maximum column in bytes. |
| Binary(size) | It is used for storing binary byte strings. The default value is 1, and its size parameter specifies the column length in bytes. |
| TINYTEXT | It holds a string with a max value of 255 characters. |
| TEXT(size) | It is used for storing a string with a max length of 255 characters, similar to CHAR(). |
| LONGTEXT | It holds the string with a max value of 4,294,967,295 characters. |
|
MEDIUMTEXT |
It holds the string with a max value of 16,777,215 characters which is relatively larger than VARCHAR() |
| ENUM(val1,val2,….) |
It is used when a string object has only one value, chosen from a list of possible values. You can list up to 65535 values in the ENUM list. If a value is inserted not in a list, the empty value will be inserted. The values are sorted in order at the time of entering. |
| SET(val1,val2,……) |
It is used to specify the string with 0 or more values, chosen from a list of possible values. At one time, 64 values can be listed. |
| BLOB(size) |
It is used for large binary objects which can hold up to 65535 bytes. |
MySQL NUMERIC DATATYPES
| BIT (size) |
Used for a bit value type. Size is used for specifying the number of bits. The range is from 1-64. By default, the value is 1. |
| INT (size) |
Used for the integer value. The range is from -2147483648-2147483647. The size parameter specifies the max display width of 255. |
| INTEGER (size) |
It is similar to INT (size). |
| FLOAT (size,d) |
Used for floating-point numbers. The size parameter specifies the total number of digits. d is used for setting the number of several digits after the decimal point. |
| Float(p) |
Used for a floating-point as well as double type. If the value of p is from 0-24, then the data becomes float, and if the value of p is from 25-53, then the data becomes double. |
| DOUBLE (size,d) |
It is similar to FLOAT(size,d). |
| DECIMAL(size,d) |
Used for specifying a fixed-point number. The maximum value size can hold 65, and by default, its value will be 10, and d can hold a maximum value of 30, and by default, the value is 0. |
| BOOL |
Used for specifying Boolean values. Zero is considered false, and the remaining non-zero values as true. |
MySQL DATE AND TIME DATATYPES
| DATE |
Used for specifying the date format. In MySQL, the format is YYYY-MM-DD. The range is from ‘1000-01-01’ to ‘9999-12-31’. |
| DATETIME(fsp) |
Used for specifying date and time combinations. The format is YYYY-MM-DD hh:mm:ss. Range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. |
| TIMESTAMP(fsp) |
Used for specifying the timestamp. The format is YYYY-MM-DD hh:mm:ss. Its supported range is ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC. |
| TIME(fsp) |
Used for specifying the time format. The format is hh:mm:ss. The range is from ‘-838:59:59’ to ‘838:59:59’. |
| YEAR |
Used for specifying the year in four-digit format. The range is from 1901 to 2155 and 0000. |
The above datatypes, as discussed above, are used for creating tables for beginners.
Let me show you where this data is used.
Suppose we want to create a table: (STUDENT)
The following attributes are:
ID, NAME, CITY, DOJ
SYNTAX:
Create table table_name (column1 datatype, column2 datatype,…………);
See the following query.
Create table student (ID integer, NAME varchar (25), CITY char (10), DOJ DATE);
Integer, varchar (25), char (10), and DATE are all datatypes describing what kind of data is to be stored.
Now let’s discuss some server data types which are used in MySQL.
SQL Server String Data Type
| Char(n) |
It is a fixed-width character string data type. The range is 8000 characters. |
| varchar(n) | It is a variable-width character string data type. The range is 8000 characters. |
| varchar(max) |
It is a variable-width character string data type. Its size can be up to 1,073,741,824 characters. |
| text | It is a variable-width character string data type. Its size can be up to 2GB of text data. |
| nchar | It is a fixed-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. |
| binary(n) |
It is a fixed-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. |
SQL Server Numeric Data Types
| Bit | It is an integer that can be 0, 1, or null. |
| Tinyint | It allows whole numbers from 0 to 255. |
| Smallint | It allows whole numbers in the range of -32,768 and 32,767. |
| Int |
It allows whole numbers between -2,147,483,648 and 2,147,483,647. |
| bigint |
It allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. |
| float(n) |
It is 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 is a floating precision number data from -3.40E+38 to 3.40E+38. |
| money |
It is used to specify monetary data from -922,337,233,685,477.5808 to 922,337,203,685,477.5807. |
SQL Server Date and Time Data Type
| datetime |
It is used to specify the date and time combination. It supports range from January 1, 1753, to December 31, 9999, with an accuracy of 3.33 milliseconds. |
| 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. |
| date |
It is used to store the date only. It supports range from January 1, 0001 to December 31, 9999 |
| Time |
It stores time only to an accuracy of 100 nanoseconds. |
| timestamp |
It stores a unique number when a new row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real-time. Therefore, each table may contain only a one-time stamp variable. |
SQL Server Other Data Types( Miscellaneous Data Types)
| Sql_variant |
It is used for various data types except 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 the result set for later processing. |
| Uniqueidentifier |
It stores GUID (Globally unique identifier). |
Oracle Datatypes
Oracle String data types
| CHAR(size) | It is used to store character data. 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). |
| NVARCHAR2(size) |
It is used to store Unicode string data within the predefined length. We must specify the size of an NVARCHAR2 data type. It can be stored up to 4000 bytes. |
Oracle Numeric Data Types
| 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 the length bytes. |
| BINARY_DOUBLE |
It is used for double binary precision (64-bit). It requires 9 bytes, including the length bytes. |
Oracle 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 stores the valid date in YYYY-MM-DD with time hh:mm:ss format. |
Oracle Large Object Data Types (LOB Types)
| BLOB |
Used for specifying unstructured binary data. Its range goes up to 232-1 bytes or 4 GB. |
| BFILE |
Used for storing binary data in an external file. Its range is from 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 a 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 the variable length of raw binary data. Its range is up to 2000 bytes per row. First, its maximum size must be determined. |
| LONG RAW |
It is used to specify the variable length of raw binary data. It ranges up to 231-1 bytes or 2 GB per row. |
We have not only written SQL Datatypes but also MySQL Datatypes, Oracle Datatypes, and SQL Server Datatypes in this tutorial.
That’s it.
