Data Types for Various Databases in SQL
Data types and ranges used by Microsoft Access, MySQL, and SQL Server.
Microsoft Access Data Types
| Data Type | Description | Storage |
|---|---|---|
| Text | Used for text or a combination of text and numbers. Maximum 255 characters. | |
| Memo | Used for larger amounts of text. Stores up to 65,536 characters. Note: Memo fields cannot be sorted, but they are searchable. | |
| Byte | Allows numbers from 0 to 255. | 1 byte |
| Integer | Allows whole numbers between -32,768 and 32,767. | 2 bytes |
| Long | Allows whole numbers between -2,147,483,648 and 2,147,483,647. | 4 bytes |
| Single | Single-precision floating point. Handles most decimals. | 4 bytes |
| Double | Double-precision floating point. Handles most decimals. | 8 bytes |
| Currency | Used for currency. Supports 15 digits to the left and 4 digits to the right of the decimal point. Tip: You can choose which country's currency to use. | 8 bytes |
| AutoNumber | AutoNumber fields automatically assign a unique number to each record, usually starting at 1. | 4 bytes |
| Date/Time | Used for dates and times. | 8 bytes |
| Yes/No | Logical field, which can be displayed as Yes/No, True/False, or On/Off. In code, use constants True and False (equivalent to 1 and 0). Note: Null values are not allowed in Yes/No fields. | 1 bit |
| Ole Object | Can store images, audio, video, or other BLOBs (Binary Large Objects). | Up to 1GB |
| Hyperlink | Contains links to other files, including web pages. | |
| Lookup Wizard | Allows you to create a list of options to choose from a dropdown list. | 4 bytes |
MySQL Data Types
In MySQL, there are three main types: Text, Number, and Date/Time.
Text Types:
| Data Type | Description |
|---|---|
| CHAR(size) | Holds fixed-length strings (can contain letters, numbers, and special characters). The length is specified in the parentheses. Maximum 255 characters. |
| VARCHAR(size) | Holds variable-length strings (can contain letters, numbers, and special characters). The maximum length is specified in the parentheses. Maximum 255 characters. Note: If the value exceeds 255, it is converted to TEXT type. |
| TINYTEXT | Holds a string with a maximum length of 255 characters. |
| TEXT | Holds a string with a maximum length of 65,535 characters. |
| BLOB | Used for BLOBs (Binary Large Objects). Holds up to 65,535 bytes of data. |
| MEDIUMTEXT | Holds a string with a maximum length of 16,777,215 characters. |
| MEDIUMBLOB | Used for BLOBs (Binary Large Objects). Holds up to 16,777,215 bytes of data. |
| LONGTEXT | Holds a string with a maximum length of 4,294,967,295 characters. |
| LONGBLOB | Used for BLOBs (Binary Large Objects). Holds up to 4,294,967,295 bytes of data. |
| ENUM(x,y,z,etc.) | Allows you to enter a list of possible values. You can list up to 65535 values in an ENUM list. If the value inserted does not exist in the list, it inserts a null value. Note: These values are ordered in the sequence you enter them. Possible values can be entered in this format: ENUM('X','Y','Z') |
| SET | Similar to ENUM, but a SET can contain up to 64 list items and can store more than one choice. |
Number Types:
| Data Type | Description |
|---|---|
| TINYINT(size) | Signed range is -128 to 127, unsigned range is 0 to 255. |
| SMALLINT(size) | Signed range is -32768 to 32767, unsigned range is 0 to 65535. Size defaults to 6. |
| MEDIUMINT(size) | Signed range is -8388608 to 8388607, unsigned range is 0 to 16777215. Size defaults to 9. |
| INT(size) | Signed range: -2147483648 to 2147483647, unsigned range: 0 to 4294967295. Default size is 11 |
| BIGINT(size) | Signed range: -9223372036854775808 to 9223372036854775807, unsigned range: 0 to 18446744073709551615. Default size is 20 |
| FLOAT(size,d) | Small decimal numbers with floating points. The maximum number of digits to be displayed is specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter. |
| DOUBLE(size,d) | Large decimal numbers with floating points. The maximum number of digits to be displayed is specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter. |
| DECIMAL(size,d) | DOUBLE type stored as a string, allowing a fixed decimal point. The maximum number of digits to be displayed is specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter. |
Note: The size mentioned above does not represent the actual length stored in the database, such as int(4) not being limited to storing only 4-digit numbers.
In fact, int(size) has no relation to the storage space it occupies. int(3), int(4), int(8) all occupy 4 bytes of storage space on disk. The only difference is in how they are displayed to the user; int(M) is the same as the int data type.
For example:
- If the int value is 10 (specified with zerofill)
int(9) display result: 000000010 int(3) display result: 010
The display length is different, but they all occupy four bytes of space.
Date Types:
| Data Type | Description |
|---|---|
| DATE() | Date. Format: YYYY-MM-DD. Supported range: '1000-01-01' to '9999-12-31' |
| DATETIME() | Combination of date and time. Format: YYYY-MM-DD HH:MM:SS. Supported range: '1000-01-01 00:00:00' to '9999-12-31 23:59:59' |
| 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. Supported range: '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC |
| TIME() | Time. Format: HH:MM:SS. Supported range: '-838:59:59' to '838:59:59' |
| YEAR() | Year in 2-digit or 4-digit format. Supported values for 4-digit format: 1901 to 2155. Supported values for 2-digit format: 70 to 69, representing 1970 to 2069. |
*Although DATETIME and TIMESTAMP return the same format, they work differently. In INSERT or UPDATE queries, TIMESTAMP automatically sets itself to the current date and time. TIMESTAMP also accepts different formats, such as YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD.
SQL Server Data Types
String Types:
| Data Type | Description | Storage |
|---|---|---|
| char(n) | Fixed-length string. Up to 8,000 characters. | Defined width |
| varchar(n) | Variable-length string. Up to 8,000 characters. | 2 bytes + number of chars |
| varchar(max) | Variable-length string. Up to 1,073,741,824 characters. | 2 bytes + number of chars |
| text | Variable-length string. Up to 2GB of text data. | 4 bytes + number of chars |
| nchar | Fixed-length Unicode string. Up to 4,000 characters. | Defined width x 2 |
| nvarchar | Variable-length Unicode string. Up to 4,000 characters. | |
| nvarchar(max) | Variable-length Unicode string. Up to 536,870,912 characters. | |
| ntext | Variable-length Unicode string. Up to 2GB of text data. | |
| bit | Allows 0, 1, or NULL | |
| binary(n) | Fixed-length binary string. Up to 8,000 bytes. | |
| varbinary | Variable-length binary string. Maximum 8,000 bytes. | |
| varbinary(max) | Variable-length binary string. Maximum 2GB. | |
| image | Variable-length binary string. Maximum 2GB. |
Number Types:
| Data Type | Description | Storage |
|---|---|---|
| tinyint | Allows whole numbers from 0 to 255. | 1 byte |
| smallint | Allows whole numbers between -32,768 and 32,767. | 2 bytes |
| int | Allows whole numbers between -2,147,483,648 and 2,147,483,647. | 4 bytes |
| bigint | Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. | 8 bytes |
| decimal(p,s) | Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 -1. The p parameter indicates the maximum total number of digits (both sides 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. Default is 0. | 5-17 bytes |
| numeric(p,s) | Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 -1. The p parameter indicates the maximum total number of digits (both sides 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. Default is 0. | 5-17 bytes |
| smallmoney | Monetary data from -214,748.3648 to 214,748.3647. | 4 bytes |
| money | Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. | 8 bytes |
| float(n) | Floating precision number data from -1.79E + 308 to 1.79E + 308. The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds 4 bytes, while float(53) holds 8 bytes. n defaults to 53. | 4 or 8 bytes |
| real | Floating precision number data from -3.40E + 38 to 3.40E + 38. | 4 bytes |
Date Types:
| Data Type | Description | Storage |
|---|---|---|
| datetime | From January 1, 1753, to December 31, 9999, with an accuracy of 3.33 milliseconds. | 8 bytes |
| datetime2 | From January 1, 1753, to December 31, 9999, with an accuracy of 100 nanoseconds. | 6-8 bytes |
| smalldatetime | From January 1, 1900, to June 6, 2079, with an accuracy of 1 minute. | 4 bytes |
| date | Stores only date values from January 1, 0001, to December 31, 9999. | 3 bytes |
| time | Stores only time values with an accuracy of 100 nanoseconds. | 3-5 bytes |
| datetimeoffset | Same as datetime2, plus time zone offset. | 8-10 bytes |
| timestamp | Stores a unique number that gets updated every time a row is created or modified. The timestamp value is based on an internal clock and does not correspond to real time. Each table can have only one timestamp variable. |
Other Data Types:
| Data Type | Description |
|---|---|
| sql_variant | Stores up to 8,000 bytes of data of various data types, excluding text, ntext, and timestamp. |
| uniqueidentifier | Stores a globally unique identifier (GUID). |
| xml | Stores XML formatted data. Maximum 2GB. |
| cursor | Stores a reference to a cursor used for database operations. |
| table | Stores result sets for later processing. |