Structured Query Language (SQL) is a powerful tool for managing and manipulating relational databases, and one of its fundamental features is the use of SQL data types.
SQL data types define the kind of values that a column in a table can store, ensuring data integrity and optimizing storage.
Types of SQL Data Types
Data types are mainly classified into three categories for every database.
- String Data types
- Numeric Data types
- Date and time Data types
Data Types in MySQL, SQL Server, and Oracle Database
Delving into the intricacies of data types, this discussion will focus on MySQL, SQL Server, and Oracle (PL/SQL) Databases. A comprehensive examination of the three principal types—String, Numeric, and Date and Time—of SQL data types is provided below.
MySQL Data Types
MySQL, one of the world’s most popular open-source relational database management systems, relies on a robust system of data types to store and manage various kinds of information.
MySQL String Data Types
| Data Type | Description |
|---|---|
| CHAR(Size) | Specifies a fixed-length string that can contain numbers, letters, and special characters. Size range: 0 to 255 characters. Default is 1. |
| VARCHAR(Size) | Specifies a variable-length string that can contain numbers, letters, and special characters. Size range: 0 to 65535 characters. |
| BINARY(Size) | Equivalent to CHAR(), but stores binary byte strings. Size parameter specifies the column length in bytes. Default is 1. |
| VARBINARY(Size) | Equivalent to VARCHAR(), but stores binary byte strings. Size parameter specifies the maximum column length in bytes. |
| TEXT(Size) | Holds a string with a maximum length of 255 characters. |
| TINYTEXT | Holds a string with a maximum length of 255 characters. |
| MEDIUMTEXT | Holds a string with a maximum length of 16,777,215 characters. |
| LONGTEXT | Holds a string with a maximum length of 4,294,967,295 characters. |
| ENUM(val1, …) | Used when a string object has only one value chosen from a list of possible values. Contains 65535 values in an ENUM list. If not in the list, a blank value is inserted. |
| SET(val1, …) | Specifies a string that can have 0 or more values chosen from a list. Up to 64 values can be listed in a SET list. |
| BLOB(Size) | Used for Binary Large Objects (BLOBs). Can hold up to 65,535 bytes. |
MySQL Numeric Data Types
| Data Type | Description |
|---|---|
| BIT(Size) | Used for a bit-value type. The number of bits per value is specified in size. Size range: 1 to 64. Default is 1. |
| INT(size) | Used for the integer value. Signed range: -2147483648 to 2147483647, Unsigned range: 0 to 4294967295. Size parameter specifies max display width (255). |
| INTEGER(size) | Equivalent to INT(size). |
| FLOAT(size, d) | Specifies a floating-point number. Size parameter specifies the total number of digits. d parameter specifies digits after the decimal point. |
| FLOAT(p) | Specifies a floating-point number. MySQL uses p parameter to determine FLOAT or DOUBLE. p 0 to 24 becomes FLOAT(), p 25 to 53 becomes DOUBLE(). |
| DOUBLE(size, d) | A normal size floating-point number. Size parameter specifies total digits. d parameter specifies digits after the decimal point. |
| DECIMAL(size, d) | Specifies a fixed-point number. Size parameter specifies total digits. d parameter specifies digits after the decimal point. Max size: 65, default: 10. Max d: 30, default: 0. |
| DEC(size, d) | Equivalent to DECIMAL(size, d). |
| BOOL | Specifies Boolean values true and false. Zero is false, nonzero values are true. |
MySQL Date and Time Data Types
| Data Type | Description |
|---|---|
| DATE | Specifies date format YYYY-MM-DD. Supported range: ‘1000-01-01’ to ‘9999-12-31’. |
| DATETIME(fsp) | Specifies date and time combination. Format: YYYY-MM-DD hh:mm:ss. Supported range: ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. |
| TIMESTAMP(fsp) | Specifies timestamp. Value stored as seconds since Unix epoch (‘1970-01-01 00:00:00’ UTC). Format: YYYY-MM-DD hh:mm:ss. Supported range: ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC. |
| TIME(fsp) | Specifies time format. Format: hh:mm:ss. Supported range: ‘-838:59:59’ to ‘838:59:59’. |
| YEAR | Specifies year in four-digit format. Values allowed from 1901 to 2155, and 0000. |
SQL Server Data Types
SQL Server String Data Type
| Data Type | Description |
|---|---|
| char(n) | Fixed width character string data type. Size can be up to 8000 characters. |
| varchar(n) | Variable width character string data type. Size can be up to 8000 characters. |
| varchar(max) | Variable width character string data types. Size can be up to 1,073,741,824 characters. |
| text | Variable width character string data type for text. Size can be up to 2GB of text data. |
| nchar | Fixed width Unicode string data type. Size can be up to 4000 characters. |
| nvarchar | Variable width Unicode string data type. Size can be up to 4000 characters. |
| ntext | Variable width Unicode string data type for text. Size can be up to 2GB of text data. |
| binary(n) | Fixed width Binary string data type. Size can be up to 8000 bytes. |
| varbinary | Variable width Binary string data type. Size can be up to 8000 bytes. |
| image | Variable width Binary string data type. Size can be up to 2GB. |
SQL Server Numeric Data Types
| Data Type | Description |
|---|---|
| bit | Represents an integer that can be 0, 1, or null. |
| tinyint | Allows whole numbers from 0 to 255. |
| smallint | Allows whole numbers between -32,768 and 32,767. |
| int | Allows whole numbers between -2,147,483,648 and 2,147,483,647. |
| bigint | Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. |
| float(n) | Specifies floating-point number data from -1.79E+308 to 1.79E+308. n parameter indicates 4 or 8 bytes storage. Default n is 53. |
| real | Represents floating-point number data from -3.40E+38 to 3.40E+38. |
| money | Specifies monetary data from -922,337,233,685,477.5808 to 922,337,203,685,477.5807. |
SQL Server Date and Time Data Type
| Data Type | Description |
|---|---|
| datetime | Specifies date and time combination. Range: January 1, 1753, to December 31, 9999. Accuracy: 3.33 milliseconds. |
| datetime2 | Specifies date and time combination. Range: January 1, 0001, to December 31, 9999. Accuracy: 100 nanoseconds. |
| date | Stores date only. Range: January 1, 0001, to December 31, 9999. |
| time | Stores time only. Accuracy: 100 nanoseconds. |
| timestamp | Stores a unique number when a new row gets created or modified. Time stamp value is based on an internal clock and doesn’t correspond to real time. Each table may contain only one timestamp variable. |
SQL Server Other Data Types
| Data Type | Description |
|---|---|
| Sql_variant | Used for various data types except text, timestamp, and ntext. Stores up to 8000 bytes of data. |
| XML | Stores XML formatted data. Maximum storage: 2GB. |
| cursor | Stores a reference to a cursor used for database operations. |
| table | Stores result set for later processing. |
| uniqueidentifier | Stores GUID (Globally unique identifier). |
Oracle Data Types
Oracle String data types
| Data Type | Description |
|---|---|
| CHAR(size) | Stores character data within the predefined length. Can be stored up to 2000 bytes. |
| NCHAR(size) | Stores national character data within the predefined length. Can be stored up to 2000 bytes. |
| VARCHAR2(size) | Stores variable string data within the predefined length. Can be stored up to 4000 bytes. |
| VARCHAR(SIZE) | Same as VARCHAR2(size). Can be stored up to 4000 bytes. Suggested to use VARCHAR2(size). |
| NVARCHAR2(size) | Stores Unicode string data within the predefined length. Must specify the size. Can be stored up to 4000 bytes. |
Oracle Numeric Data Types
| Data Type | Description |
|---|---|
| NUMBER(p, s) | Contains precision p and scale s. Precision p can range from 1 to 38, and scale s can range from -84 to 127. |
| FLOAT(p) | Subtype of the NUMBER data type. Precision p can range from 1 to 126. |
| BINARY_FLOAT | Used for binary precision (32-bit). Requires 5 bytes, including length byte. |
| BINARY_DOUBLE | Used for double binary precision (64-bit). Requires 9 bytes, including length byte. |
Oracle Date and Time Data Types
| Data Type | Description |
|---|---|
| DATE | Stores a valid date-time format with a fixed length. Range: January 1, 4712 BC, to December 31, 9999 AD. |
| TIMESTAMP | Stores a valid date in YYYY-MM-DD with time hh:mm:ss format. |
Oracle Large Object Data Types (LOB Types)
| Data Type | Description |
|---|---|
| BLOB | Specifies unstructured binary data. Range goes up to 2^32-1 bytes or 4 GB. |
| BFILE | Stores binary data in an external file. Range goes up to 2^32-1 bytes or 4 GB. |
| CLOB | Used for single-byte character data. Range goes up to 2^32-1 bytes or 4 GB. |
| NCLOB | Specifies single byte or fixed length multibyte national character set (NCHAR) data. Range is up to 2^32-1 bytes or 4 GB. |
| RAW(size) | Specifies variable length raw binary data. Range is up to 2000 bytes per row. Maximum size must be specified. |
| LONG RAW | Specifies variable length raw binary data. Range up to 2^31-1 bytes or 2 GB per row. |