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.
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..
Exact decimal numbers are known as DECIMAL or NUMERIC.
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.
© 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.