SQL Data types for various databases
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 combinations of text and numbers. Maximum 255 characters. | |
| Memo | Memo is used for larger amounts of text. Stores up to 65,536 characters.Note:Memo fields cannot be sorted. However, they are searchable. | |
| Byte | Allows numbers from 0 to 255. | 1 byte |
| Integer | Allows all numbers between -32,768 and 32,767. | 2 bytes |
| Long | Allows all 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 for dollars, plus 4 decimal places.Tip:You can choose which country's currency to use. | 8 bytes |
| AutoNumber | AutoNumber fields automatically assign a number to each record, usually starting at 1. | 4 bytes |
| Date/Time | Used for dates and times | 8 bytes |
| Yes/No | Logical fields, can display as Yes/No, True/False, or On/Off. In code, use the 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 pictures, 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 an option list that can be selected from a drop-down list. | 4 bytes |
MySQL Data Types
In MySQL, there are three main types: Text, Number, and Date/Time types.
Text types:
| Data type | Description |
|---|---|
| CHAR(size) | Holds a fixed-length string (can contain letters, numbers, and special characters). Specify the string length in parentheses. Up to 255 characters. |
| VARCHAR(size) | Holds a variable-length string (can contain letters, numbers, and special characters). Specify the maximum string length in parentheses. Up to 255 characters.Note:If the value's length is greater than 255, it is converted to a TEXT type. |
| TINYTEXT | Stores a string with a maximum length of 255 characters. |
| TEXT | Stores a string with a maximum length of 65,535 characters. |
| BLOB | Used for BLOBs (Binary Large OBjects). Stores up to 65,535 bytes of data. |
| MEDIUMTEXT | Stores a string with a maximum length of 16,777,215 characters. |
| MEDIUMBLOB | Used for BLOBs (Binary Large OBjects). Stores up to 16,777,215 bytes of data. |
| LONGTEXT | Stores a string with a maximum length of 4,294,967,295 characters. |
| LONGBLOB | Used for BLOBs (Binary Large OBjects). Stores 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 65,535 values in an ENUM list. If an inserted value does not exist in the list, a null value is inserted. Note:These values are sorted in the order you enter them. Possible values can be entered in this format: ENUM('X','Y','Z') |
| SET | Similar to ENUM, except that SET can contain at most 64 list items and SET can store more than one selection. |
Number types:
| Data type | Description |
|---|---|
| TINYINT(size) | Signed from -128 to 127, unsigned from 0 to 255. |
| SMALLINT(size) | Signed range from -32768 to 32767, unsigned from 0 to 65535, size defaults to 6. |
| MEDIUMINT(size) | Signed range from -8388608 to 8388607, unsigned range is 0 to 16777215. size defaults to 9. |
| INT(size) | Signed range from -2147483648 to 2147483647, unsigned range is 0 to 4294967295. size defaults to 11. |
| BIGINT(size) | Signed range is -9223372036854775808 to 9223372036854775807, unsigned range is 0 to 18446744073709551615. size defaults to 20. |
| FLOAT(size,d) | Small numbers with a floating decimal point. Specify the maximum number of display digits in the size parameter. Specify the maximum number of digits to the right of the decimal point in the d parameter. |
| DOUBLE(size,d) | Large numbers with a floating decimal point. Specify the maximum number of display digits in the size parameter. Specify the maximum number of digits to the right of the decimal point in the d parameter. |
| DECIMAL(size,d) | A DOUBLE type stored as a string, allowing a fixed decimal point. Specify the maximum number of display digits in the size parameter. Specify the maximum number of digits to the right of the decimal point in the d parameter. |
Note:The size mentioned above does not represent the actual length stored in the database; for example, int(4) does not mean it can only store numbers of 4 digits in length.
Actually, int(size) has nothing to do with how much storage space is used. int(3), int(4), and int(8) all occupy 4 bytes of storage on disk. Aside from a slight difference in the way they are displayed to the user, int(M) is the same as the int data type.
Example:
1. int value 10 (with zerofill specified)
int（9）显示结果为000000010 int（3）显示结果为010It's just that the displayed length is different; they all occupy four bytes of space.
Date types:
| Data type | Description |
|---|---|
| DATE() | Date. Format: YYYY-MM-DD Note:Supported range is from '1000-01-01' to '9999-12-31' |
| DATETIME() | *A combination of date and time. Format: YYYY-MM-DD HH:MM:SS Note:Supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59' |
| TIMESTAMP() | *Timestamp. TIMESTAMP values are stored using the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD HH:MM:SS Note:Supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC |
| TIME() | Time. Format: HH:MM:SS Note:Supported range is from '-838:59:59' to '838:59:59' |
| YEAR() | Year in 2-digit or 4-digit format. Note:Values allowed in 4-digit format: 1901 to 2155. Values allowed in 2-digit format: 70 to 69, representing 1970 to 2069. |
*Even though DATETIME and TIMESTAMP return the same format, they work very 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 strings. Up to 8,000 characters. | Defined width |
| varchar(n) | Variable-length strings. Up to 8,000 characters. | 2 bytes + number of chars |
| varchar(max) | Variable-length strings. Up to 1,073,741,824 characters. | 2 bytes + number of chars |
| text | Variable-length strings. 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. Up to 8,000 bytes. | |
| varbinary(max) | Variable-length binary string. Up to 2GB. | |
| image | Variable-length binary string. Up to 2GB. |
Number types:
| Data type | Description | Storage |
|---|---|---|
| tinyint | Allows all numbers from 0 to 255. | 1 byte |
| smallint | Allows all numbers between -32,768 and 32,767. | 2 bytes |
| int | Allows all numbers between -2,147,483,648 and 2,147,483,647. | 4 bytes |
| bigint | Allows all 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 between -10^38 + 1 and 10^38 - 1. The p parameter indicates the maximum number of digits that can be stored (left and 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. Default is 0. |
5-17 bytes |
| numeric(p,s) | Fixed precision and scale numbers. Allows numbers between -10^38 + 1 and 10^38 - 1. The p parameter indicates the maximum number of digits that can be stored (left and 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. Default is 0. |
5-17 bytes |
| smallmoney | Currency data between -214,748.3648 and 214,748.3647. | 4 bytes |
| money | Currency data between -922,337,203,685,477.5808 and 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 stores 4 bytes or 8 bytes. float(24) stores 4 bytes, while float(53) stores 8 bytes. The default value of n is 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 the date. From January 1, 0001 to December 31, 9999. | 3 bytes |
| time | Stores only the time. Accuracy of 100 nanoseconds. | 3-5 bytes |
| datetimeoffset | Same as datetime2, plus a time zone offset. | 8-10 bytes |
| timestamp | Stores a unique number that updates whenever 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 different data types, except text, ntext, and timestamp. |
| uniqueidentifier | Stores a globally unique identifier (GUID). |
| xml | Stores XML formatted data. Up to 2GB. |
| cursor | Stores a reference to a pointer used for database operations. |
| table | Stores a result set for later processing. |