YouTip

* * * Data types and their ranges used by Microsoft Access, MySQL, and SQL Server. * * * ## Microsoft Access Data Types | Data Type | Description | Storage | | --- | --- | --- | | Text | Used for text or combinations of text and numbers. Maximum 255 characters. | | | Memo | Memo is used for larger amounts of text. Stores up to 65,536 characters. **Note:** Memo fields cannot be sorted. However, they are searchable. | | | Byte | Allows numbers from 0 to 255. | 1 byte | | Integer | Allows all 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 fractional numbers. | 4 bytes | | Double | Double-precision floating point. Handles most fractional numbers. | 8 bytes | | Currency | Used for monetary data. Supports 15 digits before the decimal point, plus 4 digits after. **Tip:** You can choose which country's currency to use. | 8 bytes | | AutoNumber | AutoNumber fields automatically assign a number to each record, usually starting from 1. | 4 bytes | | Date/Time | Used for dates and times | 8 bytes | | Yes/No | Logical field that can be displayed as Yes/No, True/False, or On/Off. In code, use the 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 1 GB | | Hyperlink | Contains links to other files, including web pages. | | | Lookup Wizard | Allows you to create a list of options selectable from a drop-down list. | 4 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 length of the string is specified in parentheses. Maximum 255 characters. | | VARCHAR(size) | Holds a variable-length string (can contain letters, numbers, and special characters). The maximum length of the string is specified in parentheses. Maximum 255 characters. **Note:** If the value exceeds 255 characters, it is converted to TEXT type. | | TINYTEXT | Holds strings with a maximum length of 255 characters. | | TEXT | Holds strings with a maximum length of 65,535 characters. | | BLOB | Used for BLOBs (Binary Large OBjects). Stores up to 65,535 bytes of data. | | MEDIUMTEXT | Holds strings with a maximum length of 16,777,215 characters. | | MEDIUMBLOB | Used for BLOBs (Binary Large OBjects). Stores up to 16,777,215 bytes of data. | | LONGTEXT | Holds strings with a maximum length of 4,294,967,295 characters. | | LONGBLOB | Used for BLOBs (Binary Large OBjects). Stores up to 4,294,967,295 bytes of data. | | ENUM(x,y,z,etc.) | Allows you to enter a list of possible values. Up to 65,535 values can be listed in an ENUM list. If a value being inserted is not present in the list, a null value is inserted. **Note:** These values are sorted in the order you specify them. Possible values can be entered in this format: ENUM('X','Y','Z') | | SET | Similar to ENUM, except that SET can contain up to 64 list items and can store more than one selection. | **Number Types:** | Data Type | Description | | --- | --- | | TINYINT(size) | Signed range from -128 to 127, unsigned range from 0 to 255. | | SMALLINT(size) | Signed range from -32768 to 32767, unsigned range from 0 to 65535; size defaults to 6. | | MEDIUMINT(size) | Signed range from -8388608 to 8388607, unsigned range from 0 to 16777215; size defaults to 9. | | INT(size) | Signed range from -2147483648 to 2147483647, unsigned range from 0 to 4294967295; size defaults to 11. | | BIGINT(size) | Signed range from -9223372036854775808 to 9223372036854775807, unsigned range from 0 to 18446744073709551615; size defaults to 20. | | FLOAT(size,d) | Small number with a floating decimal point. The size parameter specifies the maximum display width. The d parameter specifies the maximum number of digits to the right of the decimal point. | | DOUBLE(size,d) | Large number with a floating decimal point. The size parameter specifies the maximum display width. The d parameter specifies the maximum number of digits to the right of the decimal point. | | DECIMAL(size,d) | DOUBLE stored as a string, allowing for a fixed decimal point. The size parameter specifies the maximum display width. The d parameter specifies the maximum number of digits to the right of the decimal point. | > **Note:** The size above does not represent the actual storage length in the database—for example, int(4) does not mean only 4-digit numbers can be stored. > > > Actually, int(size) has no relation to how much storage space it occupies on disk. int(3), int(4), and int(8) all occupy exactly 4 bytes of storage space on disk. Aside from slight differences in display formatting, int(M) is identical to the int data type. > > > For example: > > > 1. If the int value is 10 (with zerofill specified) > > int(9) displays as 000000010; int(3) displays as 010 > It’s just different display lengths—both occupy four bytes of space. **Date Types:** | Data Type | Description | | --- | --- | | DATE() | Date. Format: YYYY-MM-DD **Note:** 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:** Supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59' | | TIMESTAMP() | *Timestamp. TIMESTAMP values are stored as seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD HH:MM:SS **Note:** 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:** Supported range is from '-838:59:59' to '838:59:59' | | YEAR() | Year in 2-digit or 4-digit format. **Note:** Permissible values for 4-digit format: 1901 to 2155. Permissible values for 2-digit format: 70 to 69, representing years 1970 to 2069. | *Even though DATETIME and TIMESTAMP return the same format, they behave very differently. In INSERT or UPDATE queries, TIMESTAMP automatically sets itself to the current date and time. TIMESTAMP also accepts various formats, such as YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD. * * * ## SQL Server Data Types **String Types:** | Data Type | Description | Storage | | --- | --- | --- | | char(n) | Fixed-length string. Maximum 8,000 characters. | Defined width | | varchar(n) | Variable-length string. Maximum 8,000 characters. | 2 bytes + number of chars | | varchar(max) | Variable-length string. Maximum 1,073,741,824 characters. | 2 bytes + number of chars | | text | Variable-length string. Maximum 2GB of text data. | 4 bytes + number of chars | | nchar | Fixed-length Unicode string. Maximum 4,000 characters. | Defined width x 2 | | nvarchar | Variable-length Unicode string. Maximum 4,000 characters. | | | nvarchar(max) | Variable-length Unicode string. Maximum 536,870,912 characters. | | | ntext | Variable-length Unicode string. Maximum 2GB of text data. | | | bit | Allows 0, 1, or NULL | | | binary(n) | Fixed-length binary string. Maximum 8,000 bytes. | | | varbinary | Variable-length binary string. Maximum 8,000 bytes. | | | varbinary(max) | Variable-length binary string. Maximum 2GB. | | | image | Variable-length binary string. Maximum 2GB. | | **Number Types:** | Data Type | Description | Storage | | --- | --- | --- | | tinyint | Allows all numbers from 0 to 255. | 1 byte | | smallint | Allows 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 | Allows all numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. | 8 bytes | | decimal(p,s) | Exact numeric, fixed precision and scale. Allows numbers from -10^38 +1 to 10^38 -1. The p parameter indicates the maximum total number of decimal digits (both to the left and right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of decimal digits to the right of the decimal point. s must be a value from 0 to p. Default is 0. | 5–17 bytes | | numeric(p,s) | Exact numeric, fixed precision and scale. Allows numbers from -10^38 +1 to 10^38 -1. The p parameter indicates the maximum total number of decimal digits (both to the left and right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of decimal digits to the right of the decimal point. s must be a value from 0 to p. Default is 0. | 5–17 bytes | | smallmoney | Monetary data ranging from -214,748.3648 to 214,748.3647. | 4 bytes | | money | Monetary data ranging from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. | 8 bytes | | float(n) | Floating precision number data ranging from -1.79E + 308 to 1.79E + 308. The n parameter indicates whether the field stores 4 bytes or 8 bytes. float(24) stores 4 bytes, while float(53) stores 8 bytes. Default value for n is 53. | 4 or 8 bytes | | real | Floating precision number data ranging from -3.40E + 38 to 3.40E + 38. | 4 bytes | **Date Types:** | Data Type | Description | Storage | | --- | --- | --- | | datetime | From January 1, 1753 to December 31, 9999, with accuracy to 3.33 milliseconds. | 8 bytes | | datetime2 | From January 1, 1753 to December 31, 9999, with accuracy to 100 nanoseconds. | 6–8 bytes | | smalldatetime | From January 1, 1900 to June 6, 2079, with accuracy to 1 minute. | 4 bytes | | date | Stores only the date. From January 1, 0001 to December 31, 9999. | 3 bytes | | time | Stores only the time. Accuracy to 100 nanoseconds. | 3–5 bytes | | datetimeoffset | Same as datetime2, plus time zone offset. | 8–10 bytes | | timestamp | Stores a unique number that updates every time a row is created or modified. timestamp values are based on an internal clock and do not correspond to real time. Each table can have only one timestamp column. | | **Other Data Types:** | Data Type | Description | | --- | --- | | sql_variant | Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp. | | uniqueidentifier | Stores globally unique identifiers (GUIDs). | | xml | Stores XML-formatted data. Maximum 2GB. |