SQL Data Types

Data types and ranges used by Microsoft Access, MySQL, and SQL Server.

Microsoft Access data types

Data Type Description Store
Text Used for text or a combination of text and numbers. Up to 255 characters. 　
Memo

Memo is used for larger amounts of text. Can store up to 65,536 characters.

Note: Memo fields cannot be sorted. However, they are searchable.

 　
Byte Allows numbers between 0 and 255. 1 byte
Integer Allows 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 plus 4 decimal places.

Hint: You can choose which country's currency to use.

 8 bytes
AutoNumber AutoNumber fields automatically assign numbers to each record, usually starting from 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 the 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 Allow you to create an option list that can be selected from the following list. 4 bytes

MySQL data types

In MySQL, there are three main types: text, numeric, and date/time types.

Text type:

Data Type Description
CHAR(DECIMAL() Save fixed-length strings (which can include letters, numbers, and special characters). Specify the length of the string in parentheses. Up to 255 characters.
VARCHAR(DECIMAL()

Save variable-length strings (which can include letters, numbers, and special characters). Specify the maximum length of the string in parentheses. Up to 255 characters.

注释：如果值的长度大于 255，则被转换为 TEXT 类型。
TINYTEXT Comments: If the length of the value is greater than 255, it is converted to the TEXT type.
TINYTEXT Stores strings up to 255 characters in length.
TEXT Stores strings up to 65,535 characters in length.
BLOB Used for BLOBs (Binary Large Objects). Stores up to 65,535 bytes of data.
MEDIUMTEXT Stores strings up to 16,777,215 characters in length.
MEDIUMBLOB Used for BLOBs (Binary Large Objects). Stores up to 16,777,215 bytes of data.
LONGTEXT Stores strings up to 4,294,967,295 characters in length.
LONGBLOBUsed for BLOBs (Binary Large Objects). Stores up to 4,294,967,295 bytes of data.,ENUM(,xy

z

,etc.)

Allows you to enter a list of possible values. Up to 65535 values can be listed in an ENUM list. If the inserted value does not exist in the list, an empty value is inserted.
Comments: These values are stored in the order you enter them. Possible values can be entered in this format: ENUM('X','Y','Z')

Number type:

Data Type Description
SETDECIMAL() Similar to ENUM, a SET can only contain up to 64 list items, but a SET can store more than one value.
SMALLINT(DECIMAL() From -32768 to 32767 for common. From 0 to 65535 for unsigned*. The maximum number of digits is specified in parentheses.
MEDIUMINT(DECIMAL() From -8388608 to 8388607 for common. From 0 to 16777215 for unsigned*. The maximum number of digits is specified in parentheses.
INT(DECIMAL() From -2147483648 to 2147483647 for common. From 0 to 4294967295 for unsigned*. The maximum number of digits is specified in parentheses.
BIGINT(DECIMAL() From -9223372036854775808 to 9223372036854775807 for common. From 0 to 18446744073709551615 for unsigned*. The maximum number of digits is specified in parentheses.
FLOAT(DECIMAL(,size) Small numbers with floating decimal points. The maximum number of digits is specified in parentheses. The maximum number of digits to the right of the decimal point is specified in the d parameter.
DOUBLE(DECIMAL(,size) DOUBLE(
Large numbers with floating decimal points. Specify the maximum number of digits in parentheses. Specify the maximum number of digits to the right of the decimal point in the d parameter.DECIMAL(,size) d

As a string stored DOUBLE type, allows a fixed decimal point.

Date Type:

Data Type Description
*These integer types have additional options UNSIGNED. Normally, integers can be negative or positive. If the UNSIGNED attribute is added, the range will start from 0 instead of some negative number.

DATE()

Date. Format: YYYY-MM-DD
Note: The supported range is from '1000-01-01' to '9999-12-31'

DATETIME()

*Combination of date and time. Format: YYYY-MM-DD HH:MM:SS
Note: The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'

TIMESTAMP()

*Timestamp. TIMESTAMP values use the description of the Unix epoch ('1970-01-01 00:00:00' UTC) to the present to store. Format: YYYY-MM-DD HH:MM:SS
TIME()

Time. Format: HH:MM:SS

Note: The supported range is from '-838:59:59' to '838:59:59'
YEAR()

2-digit or 4-digit year format.

Note: Values allowed for 4-digit format: 1901 to 2155. Values allowed for 2-digit format: 70 to 69, representing from 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 type

Character string:

Data Type Description Store
char(n) Fixed-length string. Up to 8,000 characters. n
varchar(n) Variable-length string. Up to 8,000 characters. 　
varchar(max) Variable-length string. Up to 1,073,741,824 characters. 　
text Variable-length string. Up to 2GB of character data. 　

Unicode string:

Data Type Description Store
nchar(n) Fixed-length Unicode data. Up to 4,000 characters. 　
nvarchar(n) Variable-length Unicode data. Up to 4,000 characters. 　
nvarchar(max) Variable-length Unicode data. Up to 536,870,912 characters. 　
ntext Variable-length Unicode data. Up to 2GB character data. 　

Binary type:

Data Type Description Store
bit Allows 0, 1, or NULL 　
binary(n) Fixed-length binary data. Up to 8,000 bytes. 　
varbinary(n) Variable-length binary data. Up to 8,000 bytes. 　
varbinary(max) Variable-length binary data. Up to 2GB bytes. 　
image Variable-length binary data. Up to 2GB. 　

Number type:

Data Type Description Store
tinyint Allows all numbers from 0 to 255. 1 byte
smallint Allows all numbers from -32,768 to 32,767. 2 bytes
int Allows all numbers from -2,147,483,648 to 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 from -10^38 +1 to 10^38 -1.

The p parameter indicates the maximum number of digits that can be stored (both to the left and right of the decimal point). p must be a value between 1 and 38. The default is 18.

The s parameter indicates the maximum number of decimal places stored to the right of the decimal point. s must be a value between 0 and p. The 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 number of digits that can be stored (both to the left and right of the decimal point). p must be a value between 1 and 38. The default is 18.

The s parameter indicates the maximum number of decimal places stored to the right of the decimal point. s must be a value between 0 and p. The 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-point number data with precision ranging from -1.79E + 308 to 1.79E + 308.

Parameter n 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-point numeric data with precision from -3.40E + 38 to 3.40E + 38. 4 bytes

Date Type:

Data Type Description Store
datetime From January 1, 1753 to December 31, 9999, with a precision of 3.33 milliseconds. 8 bytes
datetime2 From January 1, 1753 to December 31, 9999, with a precision of 100 nanoseconds. 6-8 bytes
smalldatetime From January 1, 1900 to June 6, 2079, with a precision of 1 minute. 4 bytes
date Store date only. From January 1, 0001 to December 31, 9999. 3 bytes
time Store time only. Precision is 100 nanoseconds. 3-5 bytes
datetimeoffset Same as datetime2, plus time zone offset. 8-10 bytes
timestamp Store a unique number that is updated every time a row is created or modified. The timestamp is based on the 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 Store data of up to 8,000 bytes of different data types, except for text, ntext, and timestamp.
uniqueidentifier Store global identifiers (GUIDs).
xml Store XML formatted data. Up to 2GB.
cursor Store references to pointers used for database operations.
table Store the result set for later processing.