SQL Table Column Types.
Choosing Data Types for table Columns
- Beware of that different RDBMS can use the same name on data types, but their meanings are may be different.
-
In some RDBMS, it can be difficult to change a column's data type identity.
(In this case you have to empty the table and recreate that table again with a new data type on columns)
Text types
Text types is character data types and, hold letters, numbers, and special characters.
| MySQL | |
| Type | Description |
|---|---|
| CHAR(length) | Ex.: CHAR(10). Will occupy the length of characters + 1 in the database. |
| VARCHAR(length) | VARCHAR(120). Will occupy the length of the text + 2 the column contain up to the length as specified. |
| TINYTEXT | Holds a string with a maximum length of 255 characters. |
| TEXT | Holds a string with a maximum length of 65,535 characters. |
| MEDIUMTEXT | Holds a string with a maximum length of 16,777,215 characters. |
| LONGTEXT | Holds a string with a maximum length of 4,294,967,295 characters. |
| ENUM('v1','v2',...) | A string object that can have only one value, chosen from the list of values 'v1', 'v2', ...,. Can have a maximum of 65,535 distinct elements. A table can have no more than 255 unique element list definitions among its ENUM and SET columns considered as a group. |
| SET('v1','v2',...) | A string object that can have zero or more values, each of which must be chosen from the list of values 'v1', 'v2', .... Can have a maximum of 64 distinct members. A table can have no more than 255 unique element list definitions among its ENUM and SET columns considered as a group. |
| SQL server | |
| Type | Description |
|---|---|
| CHAR(length) | Fixed-length, non-Unicode string data. length defines the string length and must be a value from 1 through 8,000. |
| VARCHAR(length | max ) | Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). Will occupy the length of the text + 2 the column contain up to the length as specified. | TEXT | Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647). |
| NTEXT | Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823) bytes. |
Binary data types
Binary data types can contain code, images, and hexadecimal
data. Like character data, they comes in fixed and variable
lengths and may have long type versions
| MySQL | |
| Type | Description |
|---|---|
| BINARY(length) | Holds bytes information. Occupy length bytes in range of 0 - 255 |
| VARBINARY(length) | Holds bytes information |
| TINYBLOB | Can hold a variable amount of binary data (up to 255 bytes). |
| BLOB | Can hold a variable amount of binary data (up to 65535 bytes). |
| MEDIUMBLOB | Can hold a variable amount of binary data (up to 16,777,215 bytes). |
| LONGBLOB | Can hold a variable amount of binary data (up to 4,294,967,295 bytes). |
| SQL server | |
| Type | Description |
|---|---|
| BINARY(n) | Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes. |
| VARBINARY(n | max) | Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. |
| IMAGE | Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes. |
Whole-number data types
Whole-number data types can contain only integers (no fractions or decimals)
SIGNED (default) or UNSIGNED. This data type is used in aggregate functions.
| MySQL | |
| Type | Description |
|---|---|
| TINYINT | The signed range is –128 to 127. The unsigned range is 0 to 255. |
| SMALLINT | The signed range is –32768 to 32767. The unsigned range is 0 to 65535. |
| MEDIUMINT | The signed range is –8388608 to 8388607. The unsigned range is 0 to 16777215. |
| INT or INTEGER | The signed range is –2147483648 to 2147483647. The unsigned range is 0 to 4294967295. |
| BIGINT | The signed range is –9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615. |
| SQL server | ||
| Type | Description | |
|---|---|---|
| TINYINT | The unsigned range of 0 to 255. | |
| SMALLINT | The signed range of –32768 to 32767. | |
| INT | The signed range of –2147483648 to 2147483647. | |
| BIGINT | The signed range of –9223372036854775808 to 9223372036854775807. | |
| PostgteSQL | |
| Name | Description |
|---|---|
| SMALLINT | small-range integer, -32768 to +32767 |
| INTEGER | typical choice for integer, -2147483648 to +2147483647 |
| BIGINT | large-range integer, -9223372036854775808 to 9223372036854775807 |
| SERIAL | autoincrementing integer, 1 to 2147483647 |
| BIGSERIAL | large autoincrementing integer, 1 to 9223372036854775807 |
Decimal data types
Decimal data types can contain numbers with fractions. SIGNED (default) or UNSIGNED. This data type is used in aggregate functions.
Exact decimal numbers are known as DECIMAL or NUMERIC.
Approximate decimal numbers are known as REAL, DOUBLE or FLOAT.
| MySQL | |
| Type | Description |
|---|---|
| DECIMAL(length,decimals) | The length represents the number of significant digits that are stored for values, and the decimals represents the number of digits that can be stored following the decimal point. |
| NUMERIC(length,decimals) | Same as DECIMAL. |
| REAL [(length,decimals)] | Values can be stored with up to length digits in total, of which decimals digits may be after the decimal point. |
| DOUBLE [(length,decimals)] | Values can be stored with up to length digits in total, of which decimals digits may be after the decimal point. |
| FLOAT [(length,decimals)] | Values can be stored with up to length digits in total, of which decimals digits may be after the decimal point. Permissible values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38.. |
Approximate decimal numbers are known as REAL, DOUBLE or FLOAT.
| SQL server | |
| Type | Description |
|---|---|
| DECIMAL[ (p[ ,s] )] | Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. |
| NUMERIC[ (p[ ,s] )] | Same as DECIMAL. |
| FLOAT [ (n) ] | Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53. |
| REAL | The ISO synonym for REAL is FLOAT(24) |
| MONEY | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 (-922,337,203,685,477.58 to 922,337,203,685,477.58 for Informatica. |
| SMALLMONEY | - 214,748.3648 to 214,748.3647 |
Date & Time data types
Date data types and Time data types record date, time,
and combinations of date and time.
| MySQL | |
| Type | Description |
|---|---|
| DATE | Holds Date information. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31' |
| TIME | Holds Time information. Format: HH:MI:SS. The supported range is from '-838:59:59' to '838:59:59' |
| TIMESTAMP | Holds Date and Time information (in a small range).Format: YYYY-MM-DD HH:MI:SS. The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC |
| DATETIME | Holds Date and Time information (in a large range). Format: YYYY-MM-DD HH:MI:SS The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. |
| YEAR | Two-digit or four-digit format. Values allowed in two-digit format: 70 (1970) to 69 (2069). Values allowed in four-digit format: 1901 to 2155. |
| SQL server | |
| Type | Description |
|---|---|
| DATE | Holds Date information. Format: YYYY-MM-DD. The supported range is from 0001-01-01 through 9999-12-31 |
| TIME | Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock. Format: HH:MI:SS.nnnnnnn. nnnnnnn is fractional seconds. The supported range is from 00:00:00.0000000 through 23:59:59.9999999 |
| DATETIME | Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock. Format: YYYY-MM-DD HH:MI:SS.nnn. nnn is fractional seconds. The supported range is from 1753-01-01 00:00:00 through 9999-12-31 23:59:59.997. |
| DATETIME2 | Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision. Format: YYYY-MM-DD HH:MI:SS.nnnnnnn. nnnnnnn is fractional seconds. The supported range is from 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999. |
| DATETIMEOFFSET | Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock. Format: YYYY-MM-DD HH:MI:SS.nnnnnnn. nnnnnnn is fractional seconds. The supported range is from 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999. |
| SMALLDATETIME | Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds. Format: YYYY-MM-DD HH:MI:00. The supported range is from 1900-01-01 00:00:00 through 2079-06-06 23:59:00. |
Bit datatypes
Bit datatypes can be binary 0 or 1
| MySQL | |
| Type | Description |
|---|---|
| BOOL | Are synonyms for TINYINT(1) |
| BOOLEAN | Are synonyms for TINYINT(1) |
| SQL server | |
| Type | Description |
|---|---|
| BIT | An integer data type that can take a value of 1, 0, or NULL. |
Next in menu
© 2010 by Finnesand Data.
All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the
correctness in the pages or source codes.
The risk of using this web-site pages or any program
codes from this website is entirely at the individual user.