SQL Data Type is an attribute which specifies the type of data that will be stored inside each column when creating a table. The data type is a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.
Data Types in Databases
MySQL Data Types
MySQL String Data Types
| Data type | Description |
|---|
| CHAR(size) | A FIXED length string that can contain letters, numbers, and special characters. The size parameter specifies the column length in characters. It can be from 0 to 255. Default is 1. |
| VARCHAR(size) | A VARIABLE length string that can contain letters, numbers, and special characters. The size parameter specifies the maximum column length in characters. It can be from 0 to 65535. |
| BINARY(size) | Equal to CHAR(), but stores binary byte strings. The size parameter specifies the column length in bytes. Default is 1. |
| VARBINARY(size) | Equal to VARCHAR(), but stores binary byte strings. The size parameter specifies the maximum column length in bytes. |
| TINYTEXT(size) | Holds a string with a maximum length of 255 characters. |
| TEXT(size) | Holds a string with a maximum length of 65,535 bytes. |
| MEDIUMTEXT(size) | Holds a string with a maximum length of 16,777,215 characters. |
| LONGTEXT(size) | Holds a string with a maximum length of 4,294,967,295 characters. |
| ENUM(val1, val2, val3, ...) | A string object that can have only one value, chosen from a list of possible values. It is possible to list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted. The values are sorted in the order it is entered. |
| SET(val1, val2, val3, ...) | A string object that can have 0 or more values, chosen from a list of possible values. It is possible to list up to 64 values in a SET list. |
| TINYBLOB | Used for BLOBs (Binary Large Objects). Max length: 255 bytes. |
| BLOB(size) | Used for BLOBs (Binary Large Objects). Holds up to 65,535 bytes of data. |
| MEDIUMBLOB | Used for BLOBs (Binary Large Objects). Holds up to 16,777,215 bytes of data. |
| LONGBLOB | Used for BLOBs (Binary Large Objects). Holds up to 4,294,967,295 bytes of data. |
MySQL Numeric Data Types
| Data type | Description |
|---|
| BOOL | Zero is considered as false, nonzero values are considered as true. |
| BOOLEAN | Equal to BOOL |
| BIT(size) | A bit-value type. The number of bits per value is specified in size. The size parameter can hold a value from 1 to 64. Default is 1. |
| TINYINT(size) | A very small integer. Signed range is from -128 to 127. Unsigned range is from 0 to 255. The size parameter specifies the maximum display width (which is 255) |
| 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) | An integer value. 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. It is deprecated in MySQL 8.0.17, and it will be removed in future 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 |
| DOUBLE PRECISION(size, d) | Synonyms for DOUBLE. |
| REAL(size, d) | Synonyms for DOUBLE. |
| 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) |
| NUMERIC(size, d) | Equal to DECIMAL(size, d) |
| FIXED(size, d) | Equal to DECIMAL(size, d) |
Note: All the numeric data types may have an extra option: UNSIGNED or ZEROFILL. If UNSIGNED option is added, MySQL disallows negative values for the column. If ZEROFILL option is added, MySQL automatically also adds the UNSIGNED attribute to the column.
MySQL Date and Time Data Types
| Data type | Description |
|---|
| DATE | A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31'. |
| DATETIME(fsp) | A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Adding DEFAULT and ON UPDATE in the column definition to get automatic initialization and updating to the current date and time. |
| TIMESTAMP(fsp) | A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition. |
| TIME(fsp) | A time. Format: hh:mm:ss. The supported range is from '-838:59:59' to '838:59:59'. |
| YEAR | A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000.
Since MySQL 8.0, it does not support year in two-digit format. |
SQL Server Data Types
SQL Server String Data Types
| Data type | Description |
|---|
| CHAR(size) | A fixed width character string data type. Its size can be up to 8000 characters. |
| VARCHAR(size) | A variable width character string data type. Its size can be up to 8000 characters. |
| VARCHAR(max) | A variable width character string data types. Its size can be up to 231-1 characters. |
| TEXT | A variable width character string data type. Its size can be up to 2GB of text data. |
| NCHAR(size) | A fixed width Unicode string data type. Its size can be up to 4000 characters. |
| NVARCHAR(size) | A variable width Unicode string data type. Its size can be up to 4000 characters. |
| NVARCHAR(max) | A variable width Unicode string data type. Its size can be up to 231-1 characters. |
| NTEXT | A variable width Unicode string data type. Its size can be up to 2GB of text data. |
| BINARY(size) | A fixed Binary string data type. Its size can be up to 8000 bytes. |
| VARBINARY(size) | A variable width Binary string data type. Its size can be up to 8000 bytes. |
| VARBINARY(max) | A variable width Binary string data type. Its size can be up to 231-1 characters. |
| IMAGE | A variable width Binary string data type. Its size can be up to 2GB. |
SQL Server Numeric Data Types
| Data type | Description |
|---|
| BIT | An integer that can be 0, 1 or null. |
| TINYBIT | A whole numbers from 0 to 255. |
| SMALLINT | A whole numbers between -32,768 and 32,767. |
| INT | A whole numbers between -2,147,483,648 and 2,147,483,647. |
| BIGINT | A whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. |
| FLOAT(n) | A 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. Default value of n is 53. |
| REAL | A floating precision number data from -3.40E+38 to 3.40E+38. |
| SMALLMONEY | Used to specify monetary data from - 214,748.3648 to 214,748.3647. |
| MONEY | Used to specify monetary data from -922,337,233,685,477.5808 to 922,337,203,685,477.5807. |
| DECIMAL(p, s) | A Fixed precision and scale numbers. The maximum total number of decimal digits to be stored is specified by p. The number of decimal digits that are stored to the right of the decimal point is specified by s. When maximum precision is used, valid values are from - 1038 +1 through 1038 - 1. |
| DEC(p, s) | Identical to DECIMAL(p, s). |
| NUMERIC(p, s) | Identical to DECIMAL(p, s). |
SQL Server Date and Time Data Type
| Data type | Description |
|---|
| DATETIME | A date and time combination. Format: 'YYYY-MM-DD hh:mm:ss[.mmm]'. It supports range from January 1, 1753, to December 31, 9999 with an accuracy of 3.33 milliseconds. |
| DATETIME2 | A date and time combination. Format: 'YYYY-MM-DD hh:mm:ss[.fractional seconds]'. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds. |
| DATE | A date. Format: 'YYYY-MM-DD'. It supports range from January 1, 0001 to December 31, 9999. |
| TIME | A time. Format: 'YYYY-MM-DD hh:mm:ss[.nnnnnnn]'. Used to store time only to an accuracy of 100 nanoseconds. Values range from '00:00:00.0000000' to '23:59:59.9999999'. |
| DATETIMEOFFSET | A date and time combination and adds time zone awareness based on UTC (Universal Time Coordinate or Greenwich Mean Time). Format: 'YYYY-MM-DD hh:mm:ss[.nnnnnnn]' [{+|-}hh:mm]. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds. |
| SMALLDATETIME | A date and time combination. Format: 'YYYY-MM-DD hh:mm:ss'. It supports range from January 1, 1900 to June 6, 2079 with an accuracy of 1 minute. |
SQL Server Other Data Types
| Data type | Description |
|---|
| sql_variant | Used for various data types except for text, timestamp, and ntext. It stores up to 8000 bytes of data. |
| XML | Stores XML formatted data. Maximum 2GB. |
| cursor | Stores a reference to a cursor used for database operations. |
| hierarchyid | A variable length, system data type. Used to represent position in a hierarchy. |
| rowversion | Generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. |
| table | Stores result set for later processing. |
| uniqueidentifier | Stores GUID (Globally unique identifier). |
| Spatial Geometry Types | Used to represent data in a flat coordinate system. |
| Spatial Geography Types | Used to store ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates. |
Oracle Data Types
Oracle String Data Types
| Data type | Description |
|---|
| CHAR(size) | Stores character data within the predefined length. It can be stored up to 2000 bytes. |
| NCHAR(size) | Stores national character data within the predefined length. It can be stored up to 2000 bytes. |
| VARCHAR2(size) | Stores variable string data within the predefined length. It can be stored up to 4000 byte. |
| VARCHAR(size) | Same as VARCHAR2(size). VARCHAR(size) can be used, but it is recommended to use VARCHAR2(size). |
| NVARCHAR2(size) | Stores Unicode string data within the predefined length. It is must to specify the size of NVARCHAR2 data type. It can be used to store up to 4000 bytes. |
Oracle Numeric Data Types
| Data type | Description |
|---|
| 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 byte. |
| BINARY_DOUBLE | It is used for double binary precision (64-bit). It requires 9 bytes, including length byte. |
Oracle Date and Time Data Type
| Data type | Description |
|---|
| 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. |
Oracle Large Object Data Types (LOB Types)
| Data type | Description |
|---|
| 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. Its range up to 231-1 bytes or 2 GB, per row. |
MS Access Data Types
| Data type | Description | Size |
|---|
| Text (Short Text) | Used for alphanumeric data (names, titles, etc.) | Up to 255 characters. |
| Memo (Long Text) | Used for larger amounts of alphanumeric data: sentences and paragraphs. | Up to about 1 gigabyte (GB), but controls to display a long text are limited to the first 64,000 characters. |
| Number | Used for Numeric data. | 1, 2, 4, 8, or 16 bytes. |
| Large Number | Used for Numeric data. | 8 bytes |
| Date/Time | Used for Dates and times. | 8 bytes |
| Date/Time Extended | Used for Dates and times. | Encoded string of 42 bytes. |
| Currency | Used for monetary data, stored with 4 decimal places of precision. | 8 bytes |
| AutoNumber | AutoNumber fields automatically give each record its own number, usually starting at 1 | 4 bytes |
| Yes/No | Used for Boolean (true/false) data. Access stores the numeric value zero (0) for false, and -1 for true. | 1 byte |
| Ole Object | Can store pictures, audio, video, or other ActiveX objects from another Windows-based application. | Up to about 2 GB. |
| Hyperlink | Used link address to a document or file on the Internet, on an intranet, on a local area network (LAN), or on the local computer | Up to 8,192 (each part of a Hyperlink data type can contain up to 2048 characters). |
| Attachment | Used for attached files such as pictures, documents, spreadsheets, or charts. Each Attachment field can contain an unlimited number of attachments per record, up to the storage limit of the size of a database file. Note, the Attachment data type isn't available in MDB file formats. | Up to about 2 GB. |
| Calculated | You can create an expression that uses data from one or more fields. You can designate different result data types from the expression. Note, the Calculated data type isn't available in MDB file formats. | Dependent on the data type of the Result Type property. Short Text data type result can have up to 243 characters. Long Text, Number, Yes/No, and Date/Time should match their respective data types. |
| Lookup Wizard | The Lookup Wizard entry in the Data Type column in Design view is not actually a data type. When you choose this entry, a wizard starts to help you define either a simple or complex lookup field. A simple lookup field uses the contents of another table or a value list to validate the contents of a single value per row. A complex lookup field allows you to store multiple values of the same data type in each row. | Dependent on the data type of the lookup field. |
