Different data types for SQL

You may have used different data types for SQL, but have you used them all? In this blog, we will be showing you different SQL data types that you can use in your next project.

A data type is an attribute of data that tells the compiler or interpreter how the programmer intends to use the data. Some data types are common in all languages, and some have their own data types.

SQL is no different here. There are some data types for SQL that all relational database vendors support. Some database vendors even have their own SQL data types. For eg,  Microsoft SQL Server has money and smallmoney but this is not supported by other popular database vendors.

For SQL each column in a database table is required to have a name and a data type. A developer has to define the type of value that can be stored in a table column.

Also read: Built-in Python Functions List

Data types for SQL

Image by JournalDev.com

Data types for SQL are mainly classified into three categories.

  • String Data types
  • Numeric Data types
  • Date and time Data types

Following are different SQL data Types in MySQL, SQL Server and Oracle Databases

MySQL Data Types

Following are list of data types for SQL used in MySQL database. This is based on MySQL 8.0.

String data types in MySQL

Data TypeDescription
CHAR(Size)Char is used to specifying a fixed-length string that can contain numbers, letters, and special characters. Its size parameter specifies column length in characters can be starting from 0 to 255 characters. Default is 1.
VARCHAR(Size)Varchar is used to specify a variable-length string that can contain numbers, letters, and special characters. Its size can be from a length of 0 to 65535 characters.
BINARY(Size)This data type is equal to CHAR() but stores binary byte strings. Its size parameter specifies the column length in the bytes. Default is 1.
VARBINARY(Size)This data type is equal to VARCHAR() but stores binary byte strings. Its size parameter specifies the maximum column length in bytes.
TINYBLOBFor BLOBs (Binary Large Objects). Max length: 255 bytes
TEXT(Size)It holds a string that can contain a maximum length of 65,535 bytes.
TINYTEXTIt holds a string with a maximum length of 255 characters.
BLOB(size)For BLOBs (Binary Large Objects). Max length: 65,535 bytes of data
MEDIUMTEXTIt holds a string with a maximum length of 16,777,215 characters
LONGTEXTIt holds a string with a maximum length of 4,294,967,295 characters.
ENUM(val1, val2, val3,…)This data type is used when a string object having only one value, chosen from a list of possible values. It contains 65535 values in an ENUM list. If you insert a value that is not in the list, a blank value will be inserted.
SET( val1,val2,val3,….)It is used to specify a string that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values at one time in a SET list.

Numeric Data Types in MySQL

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

BIT(size)A bit-value type. The number of bits per value is specified in size. Its size can be 1 to 64. The default value is 1.
TINYINT(size)A very small integer. The signed range is from -2147483648 to 2147483647. The unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255)
BOOLZero is considered as false, nonzero values are considered as true.
BOOLEANEqual to BOOL
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)A medium integer. 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(sized)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. This syntax is deprecated in MySQL 8.0.17, and it will be removed in future MySQL 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(sized)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
DECIMAL(sized)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(sized)Equal to DECIMAL(size,d)

Date and Time Data Types in MySQL

DATEIt is used to specify date format YYYY-MM-DD. Its supported range is from ‘1000-01-01’ to ‘9999-12-31’.
DATETIME(fsp)It is used to specify date and time combination. Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from ‘1000-01-01 00:00:00′ to 9999-12-31 23:59:59’.
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. Its supported range is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC.
TIME(fsp)A data type used to specify a time format. Format: hh:mm:ss. The supported range is from ‘-838:59:59’ to ‘838:59:59’
YEARIt is used to specify a year in four-digit format. Values allowed in four digit format from 1901 to 2155, and 0000.

SQL Server Data Types

Following are data types for SQL Server.

String Data Types

char(n)It is a defined 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 with a storage of 2 bytes + number of characters
varchar(max)It is a variable width character string data type. Its size can be up to 1,073,741,824 characters with a storage of 2 bytes + number of characters
textIt is a variable width character string data type. Its size can be up to 2GB of text data with a storage of 4 bytes + number of characters
ncharIt is a defined width Unicode string data type. Its size can be up to 4000 characters.
nvarcharIt is a variable width Unicode string data type. Its size can be up to 4000 characters.
ntextIt is a variable width Unicode string data type. Its size can be up to 2GB of text data.
binary(n)It is a defined width Binary string data type. Its size can be up to 8000 bytes.
varbinaryIt is a variable width Binary string data type. Its size can be up to 8000 bytes.
imageIt is also a variable width Binary string data type. Its size can be up to 2GB.

Numeric Data Types

bitIt’s an integer that can be 0, 1, or null.
tinyintIt allows whole numbers from 0 to 255 with storage of 1 byte
SmallintIt allows whole numbers between -32,768 and 32,767 with storage of 2 bytes
IntIt allows whole numbers between -2,147,483,648 and 2,147,483,647 with storage of 4 bytes
numeric(p,s)A Fixed precision and scale number that allows numbers from -10^38 +1 to 10^38 –1 with storage of 5-17 bytes
The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.
The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. The default value is 0.
bigintIt allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 with storage of 8 bytes
float(n)It’s used to specify 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. The default value of n is 53.
realIt’s a floating precision number data from -3.40E+38 to 3.40E+38 with storage of 4 bytes
moneyIt’s used to specify monetary data from -922,337,233,685,477.5808 to 922,337,203,685,477.5807 with storage of 8 bytes

Date and Time Data Types

datetimeIt is used to specify the date and time combination. Its supports range from January 1, 1753, to December 31, 9999 with an accuracy of 3.33 milliseconds. The memory support is 8 bytes
datetime2It is used to specify the date and time combination. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds. The memory is 6-8 bytes
dateIt is used to store date only. Its support ranges from January 1, 0001 to December 31, 9999. The memory is 3 bytes
timeIt stores time only to an accuracy of 100 nanoseconds. The memory is 3-5 bytes
timestampIt stores a unique number when a new row gets created or modified. The time stamp value is based upon an internal clock and does not correspond to real time. Each table may contain only one-time stamp variable.

Other Data types for SQL Server

Sql_variantIt is used for various data types except for text, timestamp, and ntext. It stores up to 8000 bytes of data.
XMLIt stores XML formatted data. Maximum 2GB.
cursorIt stores a reference to a cursor used for database operations.
tableIt stores result set for later processing.
uniqueidentifierIt stores GUID (Globally unique identifier).

Oracle Data Types

Following are data types for Oracle SQL

Numeric Data type

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

String data types

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 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 is suggested to use VARCHAR2(size)
NVARCHAR2(size)It is used to store Unicode string data within the predefined length. We have to must specify the size of NVARCHAR2 data type. It can be stored up to 4000 bytes.

 Date and Time Data Types

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.

 Date and Time Data Types

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.

Large Object Data Types (LOB Types)

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

Above were some of the datatypes from three of the popular SQL database vendors, there are others as well which you can read into like Microsoft Access, etc.

If you like this blog do bookmark this blog for your future reference.

We will be happy to hear your thoughts

      Leave a reply

      Techs Tricks
      Logo
      Reset Password