SQL Data Types

In Database, each column or field of table holds certain type of value. The values may be of different types like numerical value, names, date etc… While creating table, the type for table column should be specified based on requirement. Based on the type of the data storing in the particular column, we should specify the corresponding data type to the column.

SQL Data types specifies the type of the value that can be stored in specified table column. For example, if a column is to store string values, then the column should be declared with VARCHAR (length_of_string).

SQL allows certain type of data types as listed below -

  • Numeric Data type
  • String Data type
  • Date and Time Data type
  • Unicode character string data types
  • Binary Data types
  • Miscellaneous Data types
Data Types

Important points to Note -

  • There are different relational database vendors in the market. For example - Oracle, Microsoft etc.
  • The database from different vendors have different database design schemas. Every relational database does not support all the data types and supporting data types are depends on their design. For example - MySQL does not support CLOB data type and Oracle database does not support DATETIME.
  • Data types specified below does not include all the data types. Those are most popularly used data types. Some relational database vendors have their own data types. However, those are not supported by other relational data base vendors. For example, Microsoft SQL server has its own data types "money" and "smallmoney".
  • Every relational database vendor has its own size limits for its data types. The size limits specified below are just for your knowledge and no need to remember the limit.

Numeric Datatype

Data Type Description Syntax From To
BIT A bit-value type.
The number of bits that can hold isspecified in size.
The SIZE parameter can hold the range is from 1 to 64.
The default value is 1.
BIT(SIZE) 0 1
TINYINT A very small integer.
Uses one-byte memory.
Size parameter specifies the maximum display width is 255.
TINYINT(SIZE) -128 127
TINYINT(SIZE) UNSIGNED 0 255
SMALLINT A small integer. SMALLINT(SIZE) -32768 32767
SMALLINT(SIZE) UNSIGNED 0 65535
INT or INTEGER An integer.
Uses four bytes of memory.
INT(SIZE) or INTEGER(SIZE) -2^31
(-2147483648)
2^31 – 1 (2147483647)
INT(SIZE) UNSIGNED) or INTEGER(SIZE) UNSIGNED (-2147483648)
0
4294967295
BIGINT A large integer.
Uses 8-bytes of memory.
BIGINT(SIZE) -9223372036854775808 9223372036854775807
BIGINT(SIZE) UNSIGNED 0 18446744073709551615
DECIMAL A flexi-point number. Uses 5 to 17 bytes of memory.
The default value for SIZE is 10 and d is 0.
DECIMAL
(SIZE, D)
-10^38 +1 10^38 +1
DEC Equals to DECIMAL DEC (SIZE, D) -10^38 +1 10^38 +1
Numeric Numeric types more than BIGINT. NUMERIC(SIZE) -10^38 +1 10^38 +1
FLOAT Floating-point types.
The maximum value of n is 53. If the n value lies between 1 to 24, the float data type applied.
If the n values lie between 25 to 53, double data is considered.
Uses 4 to 8 bytes memory based on n value.
Mantissa precision is 7 digits.
FLOAT(n) -1.79E + 308 1.79E + 308
REAL Floating-point data.
Uses 4 bytes of memory.
Mantissa precision is 15 digits.
REAL -3.40E + 38 3.40E + 38
DOUBLE Norma size floating-point number.
The total number of digits specified in SIZE.
The number of digits specified after decimal point is specified in d.
DOUBLE (SIZE, D)
DOUBLE PRECISION DOUBLE PRECISION (SIZE, D)

Character and String Datatype

Data Type Description Syntax Max Length
CHAR Fixed length character strings. CHAR(n) 8000 characters
VARCHAR Variable length character strings. VARCHAR(n) 8000 characters
VARCHAR (max) Variable length storage with provided max characters.
This is not supported by MySQL.
VARCHAR (max) 2E + 31 characters
TEXT Variable-length storage of text. TEXT 2GB data

Date and Time Datatype

Data Type Description Syntax Format
DATE Stores date DATE YYYY-MM-DD
TIME Stores time TIME HH:MI: SS
DATETIME Stores date and time information in the format DATETIME YYYY-MM-DD HH:MI: SS
TIMESTAMP Stores number of seconds passed TIMESTAMP YYYY-MM-DD HHLMI: SS UTC
YEAR Stores year in 2-digit or 4-digit format. YEAR YYYY

Note :- The above data types are for character strings and not for Unicode character strings.

Unicode Character and string data types

Data Type Description Syntax Max length
NCHAR Fixed length Unicode Character strings NCHAR(n) 4000 characters
NVARCHAR Variable length Unicode Character strings NVARCHAR(n) 4000 characters
NVARCHAR (max) Variable length storage with provided max Unicode characters. This is not supported by MySQL. NVARCHAR (max) 2E + 31 characters
TEXT Variable-length storage of Unicode text. TEXT 1GB data

Note! The above data types are for Unicode character strings and not for regular character strings.

Binary Datatype

Data Type Description Syntax Max length
BINARY Fixed length binary data BINARY(n) 8000 characters
VARBINARY Variable length binary data VARBINARY(n) 8000 characters
VARBINARY (max) Variable length storage with provided max Unicode characters. This is not supported by MySQL. VARBINARY (max) 2E + 31 characters
IMAGE Variable length storage IMAGE 2GB Binary data

Miscellaneous Datatype

Data Type Description Syntax Max length
CLOB Character large objects CLOB 2GB
BLOB For binary large objects BLOB
XML for storing xml data XML
JSON for storing JSON data JSON