SQL Data Types for Various Databases
Data types, description and ranges for Microsoft Access, MySQL and Microsoft SQL Server.
There are lots of various data types in all databases. But we will show you the few important data types and their range and descriptions.
Microsoft Access Data Types
| Data type | Description | Storage Size |
|---|---|---|
| Text | Use for text or combinations of text and numbers. | 0 to 255 characters |
| Memo | Stores up to 65,536 characters. | 2 bytes |
| Byte | Supports whole integer numbers from 0 to 255 | 1 byte |
| Integer | Supports whole integer numbers between -32,768 and 32,767 | 2 bytes |
| Long | Supports whole integer numbers between -2,147,483,648 and 2,147,483,647 |
4 bytes |
| Single | Single precision floating-point numbers. | 4 bytes |
| Double | Double precision floating-point numbers. | 8 bytes |
| AutoNumber | Automatically give each record its own unique number. | 4 bytes |
| Date/Time | Use for dates and times | 8 bytes |
Related Links
SQL Server Data Types
String types:
| Data type | Description | Storage |
|---|---|---|
| char(n) | Fixed width character string. Maximum 8,000 characters | Defined width |
| varchar(n) | Variable width character string. Maximum 8,000 characters | 2 bytes + number of chars |
| varchar(max) | Variable width character string. Maximum 1,073,741,824 characters | 2 bytes + number of chars |
| text | Variable width character string. Maximum 2GB of text data | 4 bytes + number of chars |
| image | Variable width binary string. Maximum 2GB |
Number types:
| Data type | Description | Storage |
|---|---|---|
| tinyint | Supports whole integer numbers from 0 to 255 | 1 byte |
| smallint | Supports whole integer numbers between -32,768 and 32,767 | 2 bytes |
| int | Supports whole integer numbers between -2,147,483,648 and 2,147,483,647 | 4 bytes |
| bigint | Supports whole integer 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. |
5-17 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 a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53. |
4 or 8 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 |
| smalldatetime | From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute | 4 bytes |
| date | Store a date only. From January 1, 0001 to December 31, 9999 | 3 bytes |
| time | Store a time only to an accuracy of 100 nanoseconds | 3-5 bytes |
Related Links
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). The fixed size is specified in parenthesis. Can store up to 255 characters |
| VARCHAR(size) | Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type |
| TEXT | Holds a string with a maximum length of 65,535 characters |
| LONGTEXT | Holds a string with a maximum length of 4,294,967,295 characters |
Number types:
| Data type | Description |
|---|---|
| SMALLINT(size) | -32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may be specified in parenthesis |
| INT(size) | -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis |
| BIGINT(size) | -9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum number of digits may be specified in parenthesis |
| FLOAT(size,d) | A small number with a floating decimal point. The maximum number of digits may be 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) | A large number with a floating decimal point. The maximum number of digits may be 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) | A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter |
*The integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative to positive value. Adding the UNSIGNED attribute will move that range up so it starts at zero instead of a negative number.
Date types:
| Data type | Description |
|---|---|
| DATE() | A date. Format: YYYY-MM-DD Note: The supported range is from '1000-01-01' to '9999-12-31' |
| DATETIME() | *A date and time combination. Format: YYYY-MM-DD HH:MI:SS Note: The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59' |
| TIME() | A time. Format: HH:MI:SS Note: The supported range is from '-838:59:59' to '838:59:59' |
*Even if DATETIME and TIMESTAMP return the same format, they work very differently. In an INSERT or UPDATE query, the TIMESTAMP automatically set itself to the current date and time. TIMESTAMP also accepts various formats, like YYYYMMDDHHMISS, YYMMDDHHMISS, YYYYMMDD, or YYMMDD.