WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL Data Types for Various DBs



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


Microsoft Access data type

The
Data type Description Storage
Text Used for text or a combination of text and numbers. Up to 255 characters.
Memo Memo is for a larger amount of text. Stores up to 65,536 characters. Note: The memo field cannot be sorted. But they are searchable.
Byte Allows numbers from 0 to 255. 1 byte
Integer Allow all numbers 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 digits plus 4 decimal places. Tip: You can choose which country's currency to use. 8 bytes
AutoNumber 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 Logic field, which can be displayed as Yes /No, True /False, or On /Off. In your code, use the constants True and False (equivalent to 1 and 0). Note: Null values ​​are not allowed in the Yes /No field 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 a list of options from which you can select from a drop-down list. 4 bytes


MySQL data type

In MySQL, there are three main types: Text, Number, and Date /Time.

Text type:

Data type Description
CHAR (size) Saves fixed-length strings (can contain letters, numbers, and special characters). Specify the length of the string in parentheses. Up to 255 characters.
VARCHAR (size) Saves variable-length strings (can contain letters, numbers, and special characters). Specify the maximum length of the string in parentheses. Up to 255 characters. Note: If the length of the value is greater than 255, it is converted to TEXT.
TINYTEXT Holds a string of up to 255 characters.
TEXT Holds a string with a maximum length of 65,535 characters.
BLOB Used for BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data.
MEDIUMTEXT Holds a string with a maximum length of 16,777,215 characters.
MEDIUMBLOB Used for BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data.
LONGTEXT Holds a string with a maximum length of 4,294,967,295 characters.
LONGBLOB Used for BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data.
ENUM(x,y,z,etc.) allows you to enter a list of possible values. A maximum of 65535 values ​​can be listed in the ENUM list. If the inserted value does not exist in the list, a null value is inserted.

Note: The values ​​are sorted in the order you entered them.

You can enter possible values ​​in this format: ENUM ('X', 'Y', 'Z')

SET Similar to ENUM, except that SET can only contain up to 64 list items and SET can store more than one selection.

Number type:

Data type Description
TINYINT (size) with sign -128 to 127, unsigned 0 to 255.
SMALLINT (size) Signed range -32768 to 32767, unsigned 0 to 65535, size defaults to 6.
MEDIUMINT (size) The signed range is -8388608 to 8386607, and the unsigned range is 0 to 16777215. size defaults to 9
INT (size) The signed range is -2147483648 to 2147483647, and the unsigned range is 0 to 4294967295. size defaults to 11
BIGINT (size) The signed range is -9223372036854775808 to 9223372036854775807, and the unsigned range is 0 to 18446744073709551615. size defaults to 20
FLOAT (size, d) A decimal number with a floating decimal point. Specify the maximum number of digits to display 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) Large number with floating decimal point. The maximum number of digits is 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) DOUBLE type stored as a string, allowing a fixed decimal point. Specify the maximum number of digits to display in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter.

Note: The size above does not represent the specific length stored in the database. For example, int (4) does not only store numbers of 4 lengths.

Actually it doesn't matter how much storage space is occupied by int (size). int (3), int (4), int (8) all occupy 4 btyes of storage space on disk. The difference is that the int (M) and int data types are the same except that they are displayed to the user.

Example:

1, the value of int is 10 (specify a zerofill)

  int   (  9  ) The result is   000000010  < span class = "pln">
  int   (  3  ) The result is   010  

It ’s just that the length of the display is different. It all takes up four bytes of space.

Date type:

Data type Description
DATE () Date. Format: YYYY-MM-DD

Note: The 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: The supported range 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 number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). 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

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

YEAR () Year in 2- or 4-digit format.

Note: Allowed values ​​for the 4-bit format: 1901 to 2155. Allowed values ​​for the 2-bit format: 70 to 69, representing 1970 to 2069.

* Even though DATETIME and TIMESTAMP return the same format, they work very differently. 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 types

String type:

Data type Description Storage
char (n) A fixed-length string. Up to 8,000 characters. Defined width
varchar (n) A variable-length string. Up to 8,000 characters. 2 bytes + number of chars
varchar (max) A variable-length string. Up to 1,073,741,824 characters. 2 bytes + number of chars
text A variable-length string. Up to 2GB of text data. 4 bytes + number of chars
nchar A fixed-length Unicode string. Up to 4,000 characters. Defined width x 2 td>
nvarchar A variable-length Unicode string. Up to 4,000 characters.
nvarchar (max) A variable-length Unicode string. Up to 536,870,912 characters.
ntext A variable-length Unicode string. Up to 2GB of text data.
bit Allow 0, 1, or NULL
binary (n) A fixed-length binary string. Up to 8,000 bytes.
varbinary A variable-length binary string. Up to 8,000 bytes.
varbinary (max) A variable-length binary string. Up to 2GB.
image A variable-length binary string. Up to 2GB.

Number type:

Data type Description Storage
tinyint Allow all numbers from 0 to 255. 1 byte
smallint Allow 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 All numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 are allowed. 8 bytes
decimal (p, s) Number with fixed precision and scale.

Allows numbers from -10 ^ 38 +1 to 10 ^ 38 -1.

The

p parameter indicates the maximum number of digits (left and right of the decimal point) that can be stored. 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) Number with fixed precision and scale.

Allows numbers from -10 ^ 38 +1 to 10 ^ 38 -1.

The

p parameter indicates the maximum number of digits (left and right of the decimal point) that can be stored. 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 Monetary data between -922,337,203,685,477.5808 and 922,337,203,685,477.5807. 8 bytes
float (n) Floating precision numeric data from -1.79E + 308 to 1.79E + 308. The

n parameter indicates whether the field holds 4 bytes or 8 bytes. float (24) holds 4 bytes, while float (53) holds 8 bytes. The default value of n is 53.

4 or 8 bytes
real Floating precision numeric 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 precision is 1 minute. 4 bytes
date Store dates only. From January 1, 0001 to December 31, 9999. 3 bytes
time Store time only. The accuracy is 100 nanoseconds. 3-5 bytes
datetimeoffset Same as datetime2, plus the time zone offset. 8-10 bytes
timestamp stores a unique number that is updated whenever a row is created or modified The timestamp value 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 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 Store the result set for later processing.