Common types of databases

Reference URL: Http://www.w3school.com.cn/sql/sql_datatypes.aspMicrosoft Access data type

Data Type	Description	Storage
Text	Used for text or a combination of text and numbers. A maximum of 255 characters.
Memo	Memo is used for a larger number of text. Stores a maximum of 65,536 characters. Note: The memo field cannot be sorted. However, they are searchable.
Byte	Allow numbers from 0 to 255.	1 bytes
Integer	Allows a number between 32,768 and 32,767.	2 bytes
Long	Allow 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-bit, plus 4 decimal places. Tip: You can choose which country's currency to use.	8 bytes
AutoNumber	The AutoNumber field automatically assigns a number to each record, usually starting at 1.	4 bytes
Date/time	For date and time	8 bytes
Yes/no	A logical field that can be displayed as yes/no, True/false, or on/off. In code, use constant True and False (equivalent to 1 and 0) Note: Null values are not allowed in the Yes/no field	1 bits
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 a list of options that can be selected from the following list.	4 bytes

MySQL data type

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

Text Type:

Data Type	Description
CHAR (size)	Holds a fixed-length string (which can contain letters, numbers, and special characters). Specifies the length of the string in parentheses. A maximum of 255 characters.
VARCHAR (size)	Saves variable-length strings (which can contain letters, numbers, and special characters). Specifies the maximum length of the string in parentheses. A maximum of 255 characters. Note: If the value is longer than 255, it is converted to the 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	For BLOBs (Binary Large OBjects). Store up to 65,535 bytes of data.
Mediumtext	Holds a string with a maximum length of 16,777,215 characters.
Mediumblob	For BLOBs (Binary Large OBjects). Store up to 16,777,215 bytes of data.
Longtext	Holds a string with a maximum length of 4,294,967,295 characters.
Longblob	For BLOBs (Binary Large OBjects). Store up to 4,294,967,295 bytes of data.
ENUM (x,y,z,etc.)	Allows you to enter a list of possible values. The maximum of 65,535 values can be listed in the ENUM list. If the inserted value does not exist in the list, a null value is inserted. Note: These values are stored in the order that you entered them. Possible values can be entered in this format: ENUM (' X ', ' Y ', ' Z ')
SET	Similar to ENUM, set can contain up to 64 list items, but set can store more than one value.

Number type: -128 to 127 conventional. 0 to 255 unsigned *. Specify the maximum number of digits in parentheses.

data type	description
TINYINT (size)
SMALLINT (size)	-32768 to 32767 general. 0 to 65535 unsigned *. Specify the maximum number of digits in parentheses.
mediumint (size)	-8388608 to 8388607 normal. 0 to 16777215 unsigned *. Specify the maximum number of digits in parentheses.
INT (size)	-2147483648 to 2147483647 general. 0 to 4294967295 unsigned *. Specify the maximum number of digits in parentheses.
BIGINT (size)	-9223372036854775808 to 9223372036854775807 general. 0 to 18446744073709551615 unsigned *. Specify the maximum number of digits in parentheses.
float (size,d)	A small number with a floating decimal point. Specify the maximum number of digits in parentheses. Specifies the maximum number of digits to the right of the decimal point in the D parameter.
double (size,d)	A large number with a floating decimal point. Specify the maximum number of digits in parentheses. Specifies the maximum number of digits to the right of the decimal point in the D parameter.
Decimal (size,d)	is a DOUBLE type stored as a string, allowing a fixed decimal point.

* These integer types have an additional option of UNSIGNED. Generally, integers can be negative or positive. If you add the UNSIGNED property, the range will start at 0 instead of a negative number.

Date Type:

Data Type	Description
DATE ()	Date. Format: YYYY-MM-DD Note: The scope of support is from ' 1000-01-01 ' to ' 9999-12-31 '
DATETIME ()	* Combination of date and time. Format: Yyyy-mm-dd HH:MM:SS Note: The range of support is from ' 1000-01-01-00:00:00 ' to ' 9999-12-31 23:59:59 '
TIMESTAMP ()	* Time stamp. The TIMESTAMP value is stored using the description of the Unix era (' 1970-01-01 00:00:00 ' UTC) to date. Format: Yyyy-mm-dd HH:MM:SS Note: The 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 Comment: The supported range is from ' -838:59:59 ' to ' 838:59:59 '
Year ()	Year of 2-bit or 4-bit format. Note: The 4-bit format allows values from 1901 to 2155. The 2-bit format allows values from 70 to 69, representing 1970 to 2069.

* Even if DATETIME and TIMESTAMP return the same format, they work in a different way. In an INSERT or UPDATE query, 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	Storage
CHAR (n)	A fixed-length string. A maximum of 8,000 characters.	N
VARCHAR (n)	A variable-length string. A maximum of 8,000 characters.
varchar (max)	A variable-length string. A maximum of 1,073,741,824 characters.
Text	A variable-length string. Up to 2GB character data.

Unicode string:

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

Binary Type:

Data Type	Description	Storage
Bit	Allow 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	Storage
tinyint	All numbers from 0 to 255 are allowed.	1 bytes
smallint	All numbers from 32,768 to 32,767 are allowed.	2 bytes
Int	All numbers from 2,147,483,648 to 2,147,483,647 are allowed.	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 proportional numbers. Allows a number from -10^38 +1 to 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 between 1 and 38. The 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 between 0 and P. The default is 0.	5-17 bytes
Numeric (P,s)	Fixed-precision and proportional numbers. Allows a number from -10^38 +1 to 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 between 1 and 38. The 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 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 precision digital data from -1.79e + 308 to 1.79E + 308. The parameter n indicates whether the field holds 4 bytes or 8 bytes. Float (24) holds 4 bytes, while float (53) holds 8 bytes. The default value for N is 53.	4 or 8 bytes
Real	Floating precision digital data from -3.40e + 38 to 3.40E + 38.	4 bytes

Date Type:

Data Type	Description	Storage
Datetime	From January 1, 1753 to December 31, 9999, the accuracy is 3.33 milliseconds.	8 bytes
DateTime2	From January 1, 1753 to December 31, 9999, the accuracy is 100 nanoseconds.	6-8 bytes
smalldatetime	From January 1, 1900 to June 6, 2079, the accuracy is 1 minutes.	4 bytes
Date	Only dates are stored. From January 1, 01 to December 31, 9999.	3 bytes
Time	Storage time only. The accuracy is 100 nanoseconds.	3-5 bytes
DateTimeOffset	Same as datetime2, plus time zone offset.	8-10 bytes
Timestamp	Stores a unique number that is updated whenever a row is created or modified. The timestamp is based on the internal clock and does not correspond to real time. There can be only one timestamp variable per table.

Other data types:

Data Type	Description
sql_variant	Stores data of up to 8,000 bytes of different data types, except text, ntext, and timestamp.
uniqueidentifier	Stores the global identifier (GUID).
Xml	Stores XML formatted data. Up to 2GB.
Cursor	Stores a reference to a pointer used for a database operation.
Table	Stores the result set for later processing.

Common types of databases