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.
Search Keys
The remote server returned an error: (404) Not Found.
Related Links
The remote server returned an error: (404) Not Found.
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 |
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 |
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.