SQL - Data Types

SQL Data Type is an attribute which specifies the type of data that will be stored inside each column when creating a table. The data type is a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.

MySQL Data Types

MySQL String Data Types

Data type	Description
CHAR(size)	A FIXED length string that can contain letters, numbers, and special characters. The size parameter specifies the column length in characters. It can be from 0 to 255. Default is 1.
VARCHAR(size)	A VARIABLE length string that can contain letters, numbers, and special characters. The size parameter specifies the maximum column length in characters. It can be from 0 to 65535.
BINARY(size)	Equal to CHAR(), but stores binary byte strings. The size parameter specifies the column length in bytes. Default is 1.
VARBINARY(size)	Equal to VARCHAR(), but stores binary byte strings. The size parameter specifies the maximum column length in bytes.
TINYTEXT(size)	Holds a string with a maximum length of 255 characters.
TEXT(size)	Holds a string with a maximum length of 65,535 bytes.
MEDIUMTEXT(size)	Holds a string with a maximum length of 16,777,215 characters.
LONGTEXT(size)	Holds a string with a maximum length of 4,294,967,295 characters.
ENUM(val1, val2, val3, ...)	A string object that can have only one value, chosen from a list of possible values. It is possible to list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted. The values are sorted in the order it is entered.
SET(val1, val2, val3, ...)	A string object that can have 0 or more values, chosen from a list of possible values. It is possible to list up to 64 values in a SET list.
TINYBLOB	Used for BLOBs (Binary Large Objects). Max length: 255 bytes.
BLOB(size)	Used for BLOBs (Binary Large Objects). Holds up to 65,535 bytes of data.
MEDIUMBLOB	Used for BLOBs (Binary Large Objects). Holds up to 16,777,215 bytes of data.
LONGBLOB	Used for BLOBs (Binary Large Objects). Holds up to 4,294,967,295 bytes of data.

MySQL Numeric Data Types

Data type	Description
BOOL	Zero is considered as false, nonzero values are considered as true.
BOOLEAN	Equal to BOOL
BIT(size)	A bit-value type. The number of bits per value is specified in size. The size parameter can hold a value from 1 to 64. Default is 1.
TINYINT(size)	A very small integer. Signed range is from -128 to 127. Unsigned range is from 0 to 255. The size parameter specifies the maximum display width (which is 255)
SMALLINT(size)	A small integer. Signed range is from -32768 to 32767. Unsigned range is from 0 to 65535. The size parameter specifies the maximum display width (which is 255)
MEDIUMINT(size)	A medium integer. Signed range is from -8388608 to 8388607. Unsigned range is from 0 to 16777215. The size parameter specifies the maximum display width (which is 255)
INT(size)	An integer value. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255)
INTEGER(size)	Equal to INT(size)
BIGINT(size)	A large integer. Signed range is from -9223372036854775808 to 9223372036854775807. Unsigned range is from 0 to 18446744073709551615. The size parameter specifies the maximum display width (which is 255)
FLOAT(size, d)	A floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. It is deprecated in MySQL 8.0.17, and it will be removed in future versions.
FLOAT(p)	A floating point number. MySQL uses the p value to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT(). If p is from 25 to 53, the data type becomes DOUBLE().
DOUBLE(size, d)	A normal-size floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter
DOUBLE PRECISION(size, d)	Synonyms for DOUBLE.
REAL(size, d)	Synonyms for DOUBLE.
DECIMAL(size, d)	An exact fixed-point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. The maximum number for size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d is 0.
DEC(size, d)	Equal to DECIMAL(size, d)
NUMERIC(size, d)	Equal to DECIMAL(size, d)
FIXED(size, d)	Equal to DECIMAL(size, d)

Note: All the numeric data types may have an extra option: UNSIGNED or ZEROFILL. If UNSIGNED option is added, MySQL disallows negative values for the column. If ZEROFILL option is added, MySQL automatically also adds the UNSIGNED attribute to the column.

MySQL Date and Time Data Types

Data type	Description
DATE	A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31'.
DATETIME(fsp)	A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Adding DEFAULT and ON UPDATE in the column definition to get automatic initialization and updating to the current date and time.
TIMESTAMP(fsp)	A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition.
TIME(fsp)	A time. Format: hh:mm:ss. The supported range is from '-838:59:59' to '838:59:59'.
YEAR	A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000. Since MySQL 8.0, it does not support year in two-digit format.

SQL Server Data Types

SQL Server String Data Types

Data type	Description
CHAR(size)	A fixed width character string data type. Its size can be up to 8000 characters.
VARCHAR(size)	A variable width character string data type. Its size can be up to 8000 characters.
VARCHAR(max)	A variable width character string data types. Its size can be up to 2³¹-1 characters.
TEXT	A variable width character string data type. Its size can be up to 2GB of text data.
NCHAR(size)	A fixed width Unicode string data type. Its size can be up to 4000 characters.
NVARCHAR(size)	A variable width Unicode string data type. Its size can be up to 4000 characters.
NVARCHAR(max)	A variable width Unicode string data type. Its size can be up to 2³¹-1 characters.
NTEXT	A variable width Unicode string data type. Its size can be up to 2GB of text data.
BINARY(size)	A fixed Binary string data type. Its size can be up to 8000 bytes.
VARBINARY(size)	A variable width Binary string data type. Its size can be up to 8000 bytes.
VARBINARY(max)	A variable width Binary string data type. Its size can be up to 2³¹-1 characters.
IMAGE	A variable width Binary string data type. Its size can be up to 2GB.

SQL Server Numeric Data Types

Data type	Description
BIT	An integer that can be 0, 1 or null.
TINYBIT	A whole numbers from 0 to 255.
SMALLINT	A whole numbers between -32,768 and 32,767.
INT	A whole numbers between -2,147,483,648 and 2,147,483,647.
BIGINT	A whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.
FLOAT(n)	A floating precision number data from -1.79E+308 to 1.79E+308. The n parameter indicates whether the field should hold the 4 or 8 bytes. Default value of n is 53.
REAL	A floating precision number data from -3.40E+38 to 3.40E+38.
SMALLMONEY	Used to specify monetary data from - 214,748.3648 to 214,748.3647.
MONEY	Used to specify monetary data from -922,337,233,685,477.5808 to 922,337,203,685,477.5807.
DECIMAL(p, s)	A Fixed precision and scale numbers. The maximum total number of decimal digits to be stored is specified by p. The number of decimal digits that are stored to the right of the decimal point is specified by s. When maximum precision is used, valid values are from - 10³⁸ +1 through 10³⁸ - 1.
DEC(p, s)	Identical to DECIMAL(p, s).
NUMERIC(p, s)	Identical to DECIMAL(p, s).

SQL Server Date and Time Data Type

Data type	Description
DATETIME	A date and time combination. Format: 'YYYY-MM-DD hh:mm:ss[.mmm]'. It supports range from January 1, 1753, to December 31, 9999 with an accuracy of 3.33 milliseconds.
DATETIME2	A date and time combination. Format: 'YYYY-MM-DD hh:mm:ss[.fractional seconds]'. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds.
DATE	A date. Format: 'YYYY-MM-DD'. It supports range from January 1, 0001 to December 31, 9999.
TIME	A time. Format: 'YYYY-MM-DD hh:mm:ss[.nnnnnnn]'. Used to store time only to an accuracy of 100 nanoseconds. Values range from '00:00:00.0000000' to '23:59:59.9999999'.
DATETIMEOFFSET	A date and time combination and adds time zone awareness based on UTC (Universal Time Coordinate or Greenwich Mean Time). Format: 'YYYY-MM-DD hh:mm:ss[.nnnnnnn]' [{+\|-}hh:mm]. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds.
SMALLDATETIME	A date and time combination. Format: 'YYYY-MM-DD hh:mm:ss'. It supports range from January 1, 1900 to June 6, 2079 with an accuracy of 1 minute.

SQL Server Other Data Types

Data type	Description
sql_variant	Used for various data types except for text, timestamp, and ntext. It stores up to 8000 bytes of data.
XML	Stores XML formatted data. Maximum 2GB.
cursor	Stores a reference to a cursor used for database operations.
hierarchyid	A variable length, system data type. Used to represent position in a hierarchy.
rowversion	Generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes.
table	Stores result set for later processing.
uniqueidentifier	Stores GUID (Globally unique identifier).
Spatial Geometry Types	Used to represent data in a flat coordinate system.
Spatial Geography Types	Used to store ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.

Oracle Data Types

Oracle String Data Types

Data type	Description
CHAR(size)	Stores character data within the predefined length. It can be stored up to 2000 bytes.
NCHAR(size)	Stores national character data within the predefined length. It can be stored up to 2000 bytes.
VARCHAR2(size)	Stores variable string data within the predefined length. It can be stored up to 4000 byte.
VARCHAR(size)	Same as VARCHAR2(size). VARCHAR(size) can be used, but it is recommended to use VARCHAR2(size).
NVARCHAR2(size)	Stores Unicode string data within the predefined length. It is must to specify the size of NVARCHAR2 data type. It can be used to store up to 4000 bytes.

Oracle Numeric Data Types

Data type	Description
NUMBER(p, s)	It contains precision p and scale s. The precision p can range from 1 to 38, and the scale s can range from -84 to 127.
FLOAT(p)	It is a subtype of the NUMBER data type. The precision p can range from 1 to 126.
BINARY_FLOAT	It is used for binary precision( 32-bit). It requires 5 bytes, including length byte.
BINARY_DOUBLE	It is used for double binary precision (64-bit). It requires 9 bytes, including length byte.

Oracle Date and Time Data Type

Data type	Description
DATE	It is used to store a valid date-time format with a fixed length. Its range varies from January 1, 4712 BC to December 31, 9999 AD.
TIMESTAMP	It is used to store the valid date in YYYY-MM-DD with time hh:mm:ss format.

Oracle Large Object Data Types (LOB Types)

Data type	Description
BLOB	It is used to specify unstructured binary data. Its range goes up to 232-1 bytes or 4 GB.
BFILE	It is used to store binary data in an external file. Its range goes up to 232-1 bytes or 4 GB.
CLOB	It is used for single-byte character data. Its range goes up to 232-1 bytes or 4 GB.
NCLOB	It is used to specify single byte or fixed length multibyte national character set (NCHAR) data. Its range is up to 232-1 bytes or 4 GB.
RAW(size)	It is used to specify variable length raw binary data. Its range is up to 2000 bytes per row. Its maximum size must be specified.
LONG RAW	It is used to specify variable length raw binary data. Its range up to 231-1 bytes or 2 GB, per row.

MS Access Data Types

Data type	Description	Size
Text (Short Text)	Used for alphanumeric data (names, titles, etc.)	Up to 255 characters.
Memo (Long Text)	Used for larger amounts of alphanumeric data: sentences and paragraphs.	Up to about 1 gigabyte (GB), but controls to display a long text are limited to the first 64,000 characters.
Number	Used for Numeric data.	1, 2, 4, 8, or 16 bytes.
Large Number	Used for Numeric data.	8 bytes
Date/Time	Used for Dates and times.	8 bytes
Date/Time Extended	Used for Dates and times.	Encoded string of 42 bytes.
Currency	Used for monetary data, stored with 4 decimal places of precision.	8 bytes
AutoNumber	AutoNumber fields automatically give each record its own number, usually starting at 1	4 bytes
Yes/No	Used for Boolean (true/false) data. Access stores the numeric value zero (0) for false, and -1 for true.	1 byte
Ole Object	Can store pictures, audio, video, or other ActiveX objects from another Windows-based application.	Up to about 2 GB.
Hyperlink	Used link address to a document or file on the Internet, on an intranet, on a local area network (LAN), or on the local computer	Up to 8,192 (each part of a Hyperlink data type can contain up to 2048 characters).
Attachment	Used for attached files such as pictures, documents, spreadsheets, or charts. Each Attachment field can contain an unlimited number of attachments per record, up to the storage limit of the size of a database file. Note, the Attachment data type isn't available in MDB file formats.	Up to about 2 GB.
Calculated	You can create an expression that uses data from one or more fields. You can designate different result data types from the expression. Note, the Calculated data type isn't available in MDB file formats.	Dependent on the data type of the Result Type property. Short Text data type result can have up to 243 characters. Long Text, Number, Yes/No, and Date/Time should match their respective data types.
Lookup Wizard	The Lookup Wizard entry in the Data Type column in Design view is not actually a data type. When you choose this entry, a wizard starts to help you define either a simple or complex lookup field. A simple lookup field uses the contents of another table or a value list to validate the contents of a single value per row. A complex lookup field allows you to store multiple values of the same data type in each row.	Dependent on the data type of the lookup field.