SQL Data Types For MySQL, SQL Server, Oracle

❮ PreviousNext ❯

SQL Data Types

Data types define the nature of the data that a column can hold.

In SQL, each column of a table should have a name and a data type.

Data types can be classified into three categories:

  • String data types.
  • Numeric data types.
  • Date and Time data types.

Note: Data types can have different names in various database systems.

MySQL Data Types

The list of Data Types used in MySQL (version 8.0) can be given as follows:

MySQL String Data Types

Data Type     Description
CHAR(size)
  • It is used to define a fixed length String that can contain letters, numbers, and special characters.
  • The size can be from 0 to 255 characters. The default size is 1.
VARCHAR(size)
  • It used to define a variable length String that can contain letters, numbers, and special characters.
  • The size can be from 0 to 65535 characters.
BINARY(size)
  • It is equivalent to CHAR(), but stores binary byte strings.
  • The size parameter specifies the column length in bytes. The default size is 1.
VARBINARY(size)
  • It is equivalent to VARCHAR(), but stores binary bytes strings.
  • The size parameter specifies the maximum column length in bytes.
TEXT(size)
  • It holds a String.
  • The maximum length is 65,535 bytes.
TINYTEXT
  • It holds a String.
  • The maximum size is 255 characters.
MEDIUMTEXT
  • It holds a String.
  • The maximum length is 16,777,215 characters.
LONGTEXT
  • It holds a String.
  • The maximum length is 4,294,967,295 characters.
BLOB(size)
  • It is used for BLOBs (Binary Large Objects).
  • It can hold up to 56,535 bytes of data.
MEDIUMBLOB
  • It is used for BLOBs (Binary Large Objects).
  • It can hold up to 16,777,215 bytes of data.
LONGBLOB
  • It is used for BLOBs (Binary Large Objects).
  • It can hold up to 4,294,967,256 bytes of data.
ENUM(val1, val2, val3, ...)
  • It is used when a String object can only have one value, chosen from a list of possible values.
  • It can contain up to 65,535 values in an ENUM list.
  • If you insert a value that is not in the list, a blank value will be inserted.
  • The values are sorted in the order you enter them.
SET(val1, val2, val3, ...)
  • It is used to specify a String that can have 0 or more values, chosen from a lit of possible values.
  • It can hold up to 64 values.

MySQL Numeric Data Types

Data Type     Description
BIT(size)
  • It is used for a bit-value type.
  • The number of bits per value is specified in size.
  • The size parameter can be a value from 1 to 64. The default value of size is 1.
INT(size)
  • It is used for 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)
  • It is equivalent to INT(size).
TINYINT(size)
  • It is 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 with which is 255.
SMALLINT(size)
  • It is 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)
  • It is 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.
BIGINT(size)
  • It is 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)
  • It is used to define a floating point number.
  • The size parameter specifies the total number of digits.
  • The d parameter specifies the number of digits after the decimal point.
  • This syntax is deprecated in MySQL 8.0.17, and it will removed in the future versions of MySQL.
FLOAT(p)
  • It is used to define a floating point number.
  • MySQL uses the p parameter to determine whether to use FLOAT or DOUBLE for the resulting data type.
  • If p is between 0 to 24, the data type becomes FLOAT().
  • If p is between 25 to 53, the data type becomes DOUBLE().
DOUBLE(size, d)
  • It is a normal-size floating point number.
  • The size parameter specifies the total number of digits.
  • The d parameter specifies number of digits after the decimal point.
DECIMAL(size, d)
  • It is used to define a fixed point number.
  • The size parameter specifies the total number of digits.
  • The d parameter specifies the number of digits after the decimal parameter.
  • The maximum value for the size parameter is 65, and the default value is 10.
  • The maximum value for the d parameter is 30, and the default value is 0.
DEC(size, d)
  • It is equivalent to DECIMAL(size, d).
BOOL
  • It is used to define Boolean values (True and False).
  • Zero is considered as False, and nonzero values are considered as True.

MySQL Date and Time Data Types

Data Type     Description
DATE
  • It is used to specify a date format: YYYY-MM-DD
  • The supported range is from '1000-01-01' to '999-12-31'.
DATETIME(fsp)
  • It is used to specify Date and Time combination.
  • Its format is 'YYYY-MM-DD hh:mm:ss'.
  • The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
  • By Adding DEFAULT and ON UPDATE in the column definition, you can get automatic initialization and updating to the current Date and Time.
TIMESTAMP(fsp)
  • It is used to specify the timestamp.
  • Its value is stored as the number of seconds since the Unix epoch('1970-01-01 00:00:00' UTC).
  • Its format is '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.
TIME(fsp)
  • It is used to specify the time format.
  • Its format is 'hh:mm:ss'.
  • The supported range is from '-838:59:59' to '838:59:59'.
YEAR
  • It is used to specify a year in four-digit format.
  • The values allowed in four-digit format is '1901' to '2155', and '0000'.
  • MySQL 8.0 does not support year in two-digit format.

SQL Server Data Types

SQL Server String Data Types

Data Type     Description
char(n)
  • It is a fixed width character String data type.
  • Its size can be up to 8000 characters.
varchar(n)
  • It is a variable width character String data type.
  • Its size can be up to 8000 characters.
varchar(max)
  • It is a variable width character String data type.
  • Its size can be up to 1,073,741,824 characters.
text
  • It is a variable width character String data type.
  • Its size can be up to 2GB of text data.
nchar
  • It is a fixed width Unicode String data type.
  • Its size can be up to 4000 characters.
nvachar
  • It is a variable width Unicode String data type.
  • Its size can be up to 4000 characters.
nvarchar(max)
  • It is a variable width Unicode String data type.
  • Its size can be up to 536,870,912 characters.
ntext
  • It is a variable width Unicode String data type.
  • Its size can be up to 2GB of text data.
binary(n)
  • Its is a fixed width Binary String data type.
  • Its size can be up to 8000 bytes.
varbinary
  • It is a variable width Binary String data type.
  • Its size can be up to 8000 bytes.
varbinary(max)
  • It is a variable width Binary String data type.
  • Its size can be up to 2GB.
image
  • It is a variable width Binary String data type.
  • Its size can be up to 2GB.

SQL Server Numeric Data Types

Data Type     Description
bit
  • It is an integer that can be 0, 1 or NULL.
tinyint
  • It allows whole numbers from 0 to 255.
smallint
  • It allows whole numbers between -32,768 and 32,767.
int
  • It allows whole numbers between -2,147,483,648 and 2,147,483,647.
bigint
  • It allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.
decimal(p,s)
  • It is a fixed precision and scale numbers.
  • It allows numbers from -10^38 +1 to 10^38 -1.
  • The p parameter indicates the maximum total number of digits that can be stored. The p parameter must be a value from 1 to 38. The default value is 18.
  • The s parameter indicates the maximum number of digits stored to the right of the decimal point. The parameter s must be a value from 0 to p. The default value is 0.
numeric(p,s)
  • It is a fixed precision and scale numbers.
  • It allows numbers from -10^38 +1 to 10^38 -1.
  • The p parameter indicates the maximum total number of digits that can be stored. The p parameter must be a value from 1 to 38. The default value is 18.
  • The s parameter indicates the maximum number of digits stored to the right of the decimal point. The parameter s must be a value from 0 to p. The default value is 0.
smallmoney
  • It is used to specify monetary data from -214,748.3648 to 214,748.3647.
money
  • It is used to specify monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
float(n)
  • It is used to specify floating precision number data from -1.79E + 308 to 1.79E + 308.
  • The n parameter indicates whether the field should hold 4 or 8 bytes. The default value of n is 53.
real
  • It is a floating precision number data from -3.40E + 38 to 3.40E + 38.

SQL Server Date and Time Data Types

Data Type     Description
datetime
  • It is used to specify date and time combination.
  • It supports range from January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds.
datetime2
  • It is used to specify date and time combination.
  • It supports from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds.
date
  • It is used to store date only.
  • It supports range from January 1, 0001 to December 31, 9999.
time
  • It stores time only to an accuracy of 100 nanoseconds.
timestamp
  • It stores a unique number when a new row get created or modified.
  • The timestamp value us based upon an internal clock and does not correspond to real time.
  • Each table may contain only one timestamp variable.
datetimeoffset
  • It is equal to datetime2 with the addition of a time zone offset.

SQL Server Other Data types

Data Type     Description
sql_variant
  • It is used for various data types except for text, ntext, and timestamp.
  • It stores up to 8000 bytes of data.
XML
  • It stores XML formatted data.
  • It stores up to 2GB.
cursor
  • It stores a reference to a cursor used for database operations.
table
  • It stores a result-set for later processing.
uniqueidentifier
  • It stores a globally unique identifier (GUID).

Oracle Data Types

Oracle String Data Types

Data Type     Description
CHAR(size)
  • It is used to store character data within the predefined length.
  • It can be stored up to 2000 bytes.
NCHAR(size)
  • It is used to store national character data within the predefined length.
  • It can be stored up to to 2000 bytes.
VARCHAR2(size)
  • It is used to store variable String data within the predefined length.
  • It can be stored up to 4000 bytes.
VARCHAR(size)
  • It is the same as VARCHAR2(size).
  • You can also use VARCHAR(size), but it recommended to use VARCHAR2(size).
NVARCHAR2(size)
  • It is used to store Unicode String data within the predefined length.
  • You have to specify the size of NVARCHAR2 data type.
  • It can sotre up to 4000 bytes.

Oracle Numeric Data Types

Data Type    Description
NUMBER(p,s)
  • It used to specify number precision p and scale s.
  • The precision p can range from 1 to 38.
  • 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 Types

Data Type    Description
DATE
  • It is used to store a valid date-time format width 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 2^32 - 1 bytes or 4GB.
BFILE
  • It is used to store binary data in an external file.
  • Its range goes up to 2^32 -1 bytes or 4GB.
CLOB
  • It is used for single-byte character data.
  • Its range goes up to 2^32 -1 bytes or 4GB.
NCLOB
  • It is used to specify single byte or fixed length multibyte national character set (NCHAR) data.
  • Its range is up to 2^32 -1 bytes or 4GB.
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 type.
  • Its range up to 2^31 -1 bytes or 2GB, per row.


❮ PreviousNext ❯

ExpectoCode is optimized for learning. Tutorials and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using this site, you agree to have read and accepted our terms of use, cookie and privacy policy.
Copyright 2020-2021 by ExpectoCode. All Rights Reserved.