SQL data types specify the type of data that a column or variable can hold in a SQL database. These data types include numeric, character and string, date and time, binary, boolean, enumerated, array, and JSON types. Each data type has a specific range of values and uses. Choosing the appropriate data type for a column or variable is essential for accurate and efficient data storage and retrieval.

Types of SQL Data Types

SQL data types can be broadly categorized into several categories:

  1. Numeric data types: It stores numeric values, such as integers and floating-point numbers. Examples include INT, BIGINT, FLOAT, NUMERIC, and DECIMAL.
  2. Character and string data types: It stores character strings, such as names and addresses. Examples include CHAR, VARCHAR, and TEXT.
  3. Date and time data types: It stores date and time values. Examples include DATE, TIME, DATETIME, and TIMESTAMP.
  4. Binary data types: It stores binary data, such as images and files. Examples include BLOB, BINARY, and VARBINARY.
  5. Boolean data types: It stores true/false values. Examples include BOOL and BOOLEAN.
  6. Enumerated data types: It stores a predefined set of values. Examples include ENUM.
  7. Array data types: It stores multiple values of the same data type. Examples include ARRAY.
  8. JSON data types: It stores JSON data. Examples include JSON and JSONB.

SQL Data Types

Here are some examples of common SQL data types and their uses:

Data Type Description
INT The INT data type stores integer values. It can hold whole numbers, such as items in stock or orders placed. INT values can range from -2147483648 to 2147483647.
BIGINT The BIGINT data type stores large integers. It can be used to hold whole numbers that are larger than the range of values that can be stored in the INT data type. BIGINT values can range from -9223372036854775808 to 9223372036854775807.
SMALLINT The SMALLINT data type stores smaller integers. It can be used to hold whole numbers that are smaller than the range of values that can be stored in the INT data type. SMALLINT values can range from -32768 to 32767.
TINYINT The TINYINT data type stores very small integers. It can be used to hold whole numbers that are smaller than the range of values that can be stored in the SMALLINT data type. TINYINT values can range from 0 to 255.
NUMERIC The NUMERIC data type stores exact numeric values with a fixed precision and scale. The precision is the total number of digits in a number, and the scale is the number of digits to the right of the decimal point. For example, 124.56 has a precision of 5 and a scale of 2.
DECIMAL The DECIMAL data type is similar to the NUMERIC data type that stores exact numeric values with a fixed precision and scale. It's important to note that NUMERIC and DECIMAL are synonyms in SQL, and it's up to the developer to choose which one to use.
FLOAT The FLOAT data type stores approximate numeric values with a floating-point representation. It can store very large or tiny numbers with many digits before and after the decimal point.
REAL The REAL data type stores approximate numeric values with a floating-point representation. It is similar to the FLOAT data type but uses fewer bits to represent the value, meaning it has a smaller range and less precision.
DOUBLE PRECISION The DOUBLE PRECISION data type stores approximate numeric values with a floating-point representation, similar to the FLOAT and REAL data types. DOUBLE PRECISION is often used to store floating-point numbers with higher precision than the FLOAT data type.
CHAR The CHAR data type stores fixed-length character strings. It can hold a fixed number of characters, specified by the field's length. For example, a CHAR(10) field can store a string of up to 10 characters. If the inserted string is shorter than the specified length, the remaining characters will be filled with spaces.
VARCHAR The VARCHAR data type stores variable-length character strings. It can hold a variable number of characters, specified by the field's length. For example, a VARCHAR(10) field can store a string of up to 10 characters. If the string inserted is shorter than the specified length, it will only use the necessary amount of storage.
TEXT The TEXT data type stores variable-length character strings, similar to the VARCHAR data type. It can store a large amount of character data, and the maximum length of a TEXT field is typically much larger than that of a VARCHAR field. The exact maximum length of a TEXT field can vary depending on the specific SQL implementation being used.
DATE The DATE data type stores date values. It can be used to hold a date, which includes the day, month, and year. It's defined as DATE and used to store date information such as birthdate, hiring date, order date, etc. The format of a DATE value can vary depending on the specific SQL implementation being used, but it typically follows the format of "YYYY-MM-DD" where YYYY represents the year, MM represents the month, and DD represents the day.
TIME The TIME data type stores time values. It can keep time, which includes hours, minutes, and seconds. It can be used to store information such as opening, closing, arrival, departure, etc. The format of a TIME value can vary depending on the specific SQL implementation being used, but it typically follows the format of "HH:MM:SS" where HH represents the hour, MM represents the minutes, and SS represents the seconds.
DATETIME The DATETIME data type stores both date and time values. It can be used to hold a date and time, which includes the day, month, year, and time, which consists of the hours, minutes, and seconds. It's defined as DATETIME, and it's used to store date and time information such as start time, end time, and timestamp. The format of a DATETIME value can vary depending on the specific SQL implementation, but it typically follows the format of "YYYY-MM-DD HH:MM:SS".
TIMESTAMP In SQL, the TIMESTAMP data type stores both date and time values, similar to the DATETIME data type. It can be used to store a date, which includes the day, month, year, and time, which consists of the hours, minutes, and seconds. It's defined as TIMESTAMP, which stores date and time information such as start time, end time, and timestamp. The format of a TIMESTAMP value can vary depending on the specific SQL implementation being used. Some implementations provide automatic initialization and updating for TIMESTAMP columns.
YEAR The YEAR data type stores year values. It can be used to hold a year, which includes four digits representing the year. It's defined as YEAR and used to store year information such as birth year, hire year, etc.
BINARY The BINARY data type stores fixed-length binary data. It is comparable to the CHAR data type but holds binary values instead of character strings. The BINARY data type requires a fixed length to be specified and can be used to store any binary data, such as images, audio, or other binary files.
VARBINARY The VARBINARY data type stores variable-length binary data. It is similar to the VARCHAR data type but holds binary values instead of character strings. The VARBINARY data type requires a maximum length to be specified and can be used to hold any binary data, such as images, audio, or any other binary files.
BLOB The BLOB (Binary Large OBject) data type stores extensive binary data such as images, audio, or other binary files. It can hold a large amount of binary data, and the maximum size of a BLOB field can vary depending on the specific SQL implementation being used. BLOB is typically used to store data that is not character or string data.
BOOLEAN The BOOLEAN data type stores true/false or yes/no values. It can hold only two possible values: TRUE, FALSE, YES, or NO. It's defined as BOOLEAN and used to store logical values. The exact representation of a BOOLEAN value can vary depending on the specific SQL implementation being used, but it typically follows the format of "TRUE" or "FALSE" or "1" or "0" or "YES" or "NO".
ENUM The ENUM data type stores a predefined set of values. It is a string object with a value chosen from a list of permitted values specified when the table is created. It is similar to a string object but is more restrictive in that it only allows values included in the list of permitted values.
ARRAY The ARRAY data type stores an ordered collection of elements. It is a variable-size multidimensional array and can hold a list of values with the same data type. The elements of an array can be any data type, such as integers, strings, or other complex data types. The size of an array is not fixed and can be changed dynamically.
JSON The JSON data type stores JSON (JavaScript Object Notation) data. It is a lightweight data-interchange format that is simple for humans to read and write and for machines to parse and generate.
JSONB The JSONB data type stores binary representation of JSON (JavaScript Object Notation) data. JSONB (binary JSON) is an optimized binary format of JSON data. It is similar to the JSON data type but stores the data in a binary format, making it more efficient in terms of storage and retrieval. JSONB also provides additional functionality, such as indexing and searching capabilities on the JSON data.
UUID The UUID (Universally Unique Identifier) data type stores unique identifier values. It is a 128-bit value represented as a hexadecimal string, usually in the format of "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX". It is designed to have a very low probability of the same value being generated twice, hence the term "universally unique".
IP ADDRESS The IP ADDRESS data type stores Internet Protocol (IP) address values. It is a string representation of an IP address, usually in the format of "xxx.xxx.xxx.xxx" for IPv4 addresses and "xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx" for IPv6 addresses.
CIDR The CIDR (Classless Inter-Domain Routing) data type stores IP network addresses in a compact representation. It is a string representation of an IP network address, usually in the format of "xxx.xxx.xxx.xxx/yy" for IPv4 addresses and "xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx/yy" for IPv6 addresses. The "yy" represents the number of bits of the IP address used to identify the network, also called the prefix length.

It's worth noting that SQL data types can have additional constraints and options, such as UNSIGNED for numeric types and CHARACTER SET for character and string types; also, different SQL implementations may have different data types or variations of data types.

SQL Data Types Example

When creating a table, the data types for each column must be specified. For example, the following SQL statement creates a table called "customers" with several columns, each with a specific data type:

Example:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) UNIQUE,
    date_of_birth DATE,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    last_login TIMESTAMP
);
  • In the example above, the "customer_id" column has an INT data type and is set as the primary key, meaning it must contain a unique value for each row in the table.
  • The "first_name" and "last_name" columns have VARCHAR data types with a maximum length of 50 characters and are both set as "NOT NULL", meaning they must contain a value.
  • The "email" column has a VARCHAR data type with a maximum length of 255 characters and is set as "UNIQUE", meaning it must contain a unique value across the entire table.
  • The "date_of_birth" column has a DATE data type.
  • The "is_active" column has a BOOLEAN data type and is set as "NOT NULL" and "DEFAULT TRUE".
  • The "last_login" column has a TIMESTAMP data type.