SQL data types

SQL data types

Data types are used to represent a kind of data that can be stored in the database. The data type is the set of representable values. It is also known as an attribute which specifies a data type of the object. Each column, variable and expression has the associated data type in the SQL code when the table was created.

Important points about SQL data types

  •   Relational database providers do not support all types of data. For example, the Oracle database does not support DATETIME and MySQL does not support the CLOB data type. So when designing the database schema and writing SQL queries, be sure to check whether the data types are supported or not.
  •   The data types listed here do not include all types of data; These are the most commonly used data types. Some providers of relational databases have their data types which may not be listed here. For example, Microsoft SQL Server has money and smallmoney data types, but since other preferred database providers do not support them, they are not listed here.
  •   Each relational database provider has its maximum size for different types of data and you don't need to remember that. The idea is to know what type of data to use in a specific scenario.

SQL data types

The SQL developer must decide the type of data to store in each column when creating the table. The data type is the guide SQL uses to understand the type of data expected in each column. It also indicates how SQL will interact with the stored data.

SQL data types mainly classified into six categories for each database.

  •  String data types
  •  Numerics data types
  •  Date and time
  •  Binary data types such as binary, varbinary, etc.
  •   Unicode character string data types such as nchar, nvarchar, ntext, etc.
  •   Other types of data such as clob, blob, XML, cursor, table, etc.

Numerics data types

Data typeStart fromTo
bit01
tinyint0255
int-2,147,483,6482,147,483,647
decimal-10^38 +110^38 -1
numeric-10^38 +110^38 -1
float-1.79E + 3081.79E + 308
real-3.40E + 383.40E + 38

Date and time

Data typeDescription
DATEStores date in YYYY-MM-DD format
TIMEStores the time in HH:MI:SS format
DATETIMEStores date and time information in YYYY-MM-DD HH:MI:SS format
TIMESTAMPStores the number of seconds since the Unix era (Timestamp) (‘1970-01-01 00:00:00’ UTC)
YEARStores the year in 2-digit or 4-digit format. Range 1901 to 2155 in 4-digit format. Range 70 to 69, representing 1970 to 2069.

SQL Character and String data types

Data typesDescription
CHARFixed length with a maximum length of 8000 characters
VARCHARVariable length storage with a maximum length of 8000 characters
VARCHAR(max)Variable-length storage with the maximum number of characters provided, not supported in MySQL
TEXTVariable length storage with a maximum size of 2 GB of data

Binary SQL data types

Data typesDescription
BINARYFixed length with a maximum length of 8000 bytes
VARBINARYVariable length storage with a maximum length of 8000 bytes
VARBINARY(max)Variable length storage with the maximum number of bytes provided
IMAGEVariable length storage with a maximum size of 2 GB of binary data

Other SQL data types

Type de donnéesDescription
CLOBLarge character objects that can hold up to 2 GB
BLOBFor large binary objects
XMLTo store XML data
JSONTo store JSON data

MySQL RDBMS

String data types

Data typesDescription
Varchar(size)It is used to specify a variable length string that can contain numbers, letters and special characters. Its maximum size is between 0 and 65535 characters.
Char(size)It is used to specify a fixed length string that can contain numbers, letters and special characters. By default, it can contain 1 character. Its maximum size is between 0 and 255 characters.
VARBINARY(size)It is as similar as VARCHAR(), and the only difference is that it stores binary byte strings. The size parameter specifies the maximum column in bytes
Binary(size)It is used to store strings of binary bytes. The default value is 1 and its size parameter specifies the length of the column in bytes.
TINYTEXTIt contains a string with a maximum value of 255 characters.
TEXT(size)It is used to store a string with a maximum length of 255 characters, similar to CHAR().
LONGTEXTIt contains a string with a maximum value of 4,294,967,295 characters.
ENUM(val1,val2,….)It is used when a string object has only one value, chosen from a list of possible values. You can list up to 65,535 values in the ENUM list. If a value is inserted, it does not appear in a list and the empty value will be inserted. The values are sorted in order when entered.
SET(val1,val2,……)It is used to specify the string that can contain 0 or more values, chosen from a list of possible values. At the same time, 64 values can be listed.
BLOB(size)It is used for large binary objects that can contain up to 65,535 bytes.

Numerics data types

Data typesDescription
BIT(size)Used for a bit value type. Size is used to specify the number of bits. The range is 1-64. By default, the value is 1.
INT(size)Used for the whole value. The range is from -2147483648-2147483647. The size parameter specifies the maximum display width of 255.
INTEGER(size)It is similar to INT (size).
FLOAT(size,d)Used for a floating point number. The size parameter specifies the total number of digits. d is used to define the number digits after the decimal point.
Float(p)Used for a floating point and double number. If the value of p is between 0 and 24, the data becomes floating and if the value of p is between 25 and 53, the data becomes double.
DOUBLE(size,d)It is similar to FLOAT (size, d).
DECIMAL(size,d)Used to specify a fixed point number. The maximum value size can contain 65, and by default its value will be 10 and d may contain a maximum value of 30, and by default the value is 0.
BOOLUsed to specify Boolean values. 0 is considered false and the remaining non-zero values are true.

Date and Time data types

Data typesDescription
DATEUsed to specify the date format. In MySQL, the format is YYYY-MM-DD. The range is from "1000-01-01" to "9999-12-31".
DATETIMEUsed to specify the combination of date and time. The format is YYYY-MM-DD hh:mm:ss. The range is from "1000-01-01 00:00:00" to "9999-12-31 23:59:59".
TIMESTAMPUsed to specify the time stamp. The format is YYYY-MM-DD hh:mm:ss. The supported range is "1970-01-01 00:00:01" UTC to "2038-01-09 03:14:07" UTC.
TIMEUsed to specify the time format. The format is hh:mm:ss. The range is from “-838:59:59” to “838:59:59”.
YEARUsed to specify the year in four-digit format. The range is from 1901 to 2155 and 0000.

Share this course with your friends :

This course is written by M. ESSADDOUKI Mostafa

Many people realize their hearts desires late in life. Continue learning, never stop striving and keep your curiosity sharp, and you will never become too old to appreciate life.

0 Comment(s)

To leave a comment you must have an account Sign up, or Sign in