SQL Tutorial SQL Advanced SQL Database SQL References

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.

Data Types in Databases

MySQL Data Types

MySQL String Data Types

Data typeDescription
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.
TINYBLOBUsed 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.
MEDIUMBLOBUsed for BLOBs (Binary Large Objects). Holds up to 16,777,215 bytes of data.
LONGBLOBUsed for BLOBs (Binary Large Objects). Holds up to 4,294,967,295 bytes of data.

MySQL Numeric Data Types

Data typeDescription
BOOLZero is considered as false, nonzero values are considered as true.
BOOLEANEqual 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 typeDescription
DATEA 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'.
YEARA 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 typeDescription
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 231-1 characters.
TEXTA 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 231-1 characters.
NTEXTA 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 231-1 characters.
IMAGEA variable width Binary string data type. Its size can be up to 2GB.

SQL Server Numeric Data Types

Data typeDescription
BITAn integer that can be 0, 1 or null.
TINYBITA whole numbers from 0 to 255.
SMALLINTA whole numbers between -32,768 and 32,767.
INTA whole numbers between -2,147,483,648 and 2,147,483,647.
BIGINTA 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.
REALA floating precision number data from -3.40E+38 to 3.40E+38.
SMALLMONEYUsed to specify monetary data from - 214,748.3648 to 214,748.3647.
MONEYUsed 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 - 1038 +1 through 1038 - 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 typeDescription
DATETIMEA 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.
DATETIME2A 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.
DATEA date. Format: 'YYYY-MM-DD'. It supports range from January 1, 0001 to December 31, 9999.
TIMEA 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'.
DATETIMEOFFSETA 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.
SMALLDATETIMEA 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 typeDescription
sql_variantUsed for various data types except for text, timestamp, and ntext. It stores up to 8000 bytes of data.
XMLStores XML formatted data. Maximum 2GB.
cursorStores a reference to a cursor used for database operations.
hierarchyidA variable length, system data type. Used to represent position in a hierarchy.
rowversionGenerally used as a mechanism for version-stamping table rows. The storage size is 8 bytes.
tableStores result set for later processing.
uniqueidentifierStores GUID (Globally unique identifier).
Spatial Geometry TypesUsed to represent data in a flat coordinate system.
Spatial Geography TypesUsed to store ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.

Oracle Data Types

Oracle String Data Types

Data typeDescription
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 typeDescription
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_FLOATIt is used for binary precision( 32-bit). It requires 5 bytes, including length byte.
BINARY_DOUBLEIt is used for double binary precision (64-bit). It requires 9 bytes, including length byte.

Oracle Date and Time Data Type

Data typeDescription
DATEIt 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.
TIMESTAMPIt 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 typeDescription
BLOBIt is used to specify unstructured binary data. Its range goes up to 232-1 bytes or 4 GB.
BFILEIt is used to store binary data in an external file. Its range goes up to 232-1 bytes or 4 GB.
CLOBIt is used for single-byte character data. Its range goes up to 232-1 bytes or 4 GB.
NCLOBIt 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 RAWIt 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 typeDescriptionSize
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.
NumberUsed for Numeric data.1, 2, 4, 8, or 16 bytes.
Large NumberUsed for Numeric data.8 bytes
Date/TimeUsed for Dates and times.8 bytes
Date/Time ExtendedUsed for Dates and times.Encoded string of 42 bytes.
CurrencyUsed for monetary data, stored with 4 decimal places of precision.8 bytes
AutoNumberAutoNumber fields automatically give each record its own number, usually starting at 14 bytes
Yes/NoUsed for Boolean (true/false) data. Access stores the numeric value zero (0) for false, and -1 for true.1 byte
Ole ObjectCan store pictures, audio, video, or other ActiveX objects from another Windows-based application.Up to about 2 GB.
HyperlinkUsed link address to a document or file on the Internet, on an intranet, on a local area network (LAN), or on the local computerUp to 8,192 (each part of a Hyperlink data type can contain up to 2048 characters).
AttachmentUsed 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.
CalculatedYou 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 WizardThe 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.

5