Introduction
SQL data types define the type of value that can be stored in a table column. For example, if you want a column to store only integer values, you can define its data type as INT.
SQL data types can be broadly divided into the following categories.
- Numeric data types such as:
INT, TINYINT, BIGINT, FLOAT, REAL, etc. - Date and Time data types such as:
DATE, TIME, DATETIME, etc. - Character and String data types such as:
CHAR, VARCHAR, TEXT, etc. - Unicode character string data types such as:
NCHAR, NVARCHAR, NTEXT, etc. - Binary data types such as:
BINARY, VARBINARY, etc. - Miscellaneous data types -
CLOB, BLOB, XML, CURSOR, TABLE, etc.
In this article, you will learn about different categories of SQL data types.
Relational Database Vendor Differences
Note: Not all data types are supported by every relational database vendor.
For example, the Oracle database doesn’t support DATETIME, and MySQL doesn’t support CLOB. When designing database schemas and writing SQL queries, make sure to check if the data types are supported.
Note: Data types listed here don’t include all the data types. These are the most commonly used data types. Some relational database vendors have their own data types that might not be listed here.
For example, Microsoft SQL Server has MONEY and SMALLMONEY data types, but since they’re not supported by other popular database vendors, they’re not listed here.
Note: Every relational database vendor has its own maximum size limit for different data types.
Be sure to select the appropriate data type for your particular scenario.
SQL Numeric Data Types
| Data Type | From | To |
|---|
| BIT | 1 | 0 |
| TINYINT | 0 | 255 |
| SMALLINT | -32,768 | 32,767 |
| INT | -2,147,483,648 | 2,147,483,647 |
| BIGINT | -9,223,372,036,854,775,808 | 9,223,372,036,854,775,807 |
| DECIMAL | -10^38 + 1 | 10^38 - 1 |
| NUMERIC | -10^38 + 1 | 10^38 - 1 |
| FLOAT | -1.79E+308 | 1.79E+308 |
| REAL | -3.40E+38 | 3.40E+38 |
SQL Date and Time Data Types
| Data Type | Description |
|---|
| DATE | Stores date in the format YYYY-MM-DD |
| TIME | Stores time in the format HH:MI:SS |
| DATETIME | Stores date and time information in the format YYYY-MM-DD HH:MI:SS |
| TIMESTAMP | Stores number of seconds passed since the Unix epoch ('1970-01-01 00:00:00' UTC) |
| YEAR | Stores year in a 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 Type | Description |
|---|
| CHAR | Fixed length with a maximum length of 8,000 characters |
| VARCHAR | Variable-length storage with a maximum length of 8,000 characters |
| VARCHAR(max) | Variable-length storage with provided max characters, not supported in MySQL |
| TEXT | Variable-length storage with a maximum size of 2GB data |
Note: These data types are for character streams. They should not be used with Unicode data.
SQL Unicode Character and String Data Types
| Data Type | Description |
|---|
| NCHAR | Fixed length with a maximum length of 4,000 characters |
| NVARCHAR | Variable-length storage with a maximum length of 4,000 characters |
| NVARCHAR(max) | Variable-length storage with provided max characters |
| NTEXT | Variable-length storage with a maximum size of 1GB data |
Note: These data types are not supported in MySQL databases.
SQL Binary Data Types
| Data Type | Description |
|---|
| BINARY | Fixed length with a maximum length of 8,000 bytes |
| VARBINARY | Variable-length storage with a maximum length of 8,000 bytes |
| VARBINARY(max) | Variable-length storage with provided max bytes |
| IMAGE | Variable-length storage with a maximum size of 2 GB binary data |
SQL Miscellaneous Data Types
| Data Type | Description |
|---|
| CLOB | Character large objects that can hold up to 2 GB |
| BLOB | For large binary objects |
| XML | For storing XML data |
| JSON | For storing JSON data |
Conclusion
In this article, you learned about different categories of SQL data types.
Continue your learning with more SQL tutorials.
References
FAQs
A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on. SQL Server supplies a set of system data types that define all the types of data that can be used with SQL Server.
What are the 4 data types of DBMS? ›
Text: This includes alphanumeric characters, words, and sentences. Numeric: This includes integers, decimal numbers, and floating-point numbers. Date and time: This includes date, time, and timestamps. Boolean: This includes true/false values.
How to get table data types in SQL? ›
How to check the data type of a table's columns [Examples: MYSQL, POSTGRESQL, REDSHIFT, BIGQUERY]
- MySQL: SELECT. COLUMN_NAME, DATA_TYPE. FROM. INFORMATION_SCHEMA.COLUMNS. WHERE. ...
- PostgreSQL: pg_typeof(col_name)
- Redshift: SELECT "column", type. FROM PG_TABLE_DEF. WHERE tablename = 'table_name' AND "column" = 'column_name'
Some common data types are as follows: integers, characters, strings, floating-point numbers and arrays. More specific data types are as follows: varchar (variable character) formats, Boolean values, dates and timestamps.
How to identify data type in SQL? ›
Simply run the following SQL command, replacing "table_name" with the name of the table you're interested in: DESC table_name; This will give you a list of columns in the table, along with their data types and any other relevant information.
What are the 4 essential data types? ›
The data is classified into majorly four categories:
- Nominal data.
- Ordinal data.
- Discrete data.
- Continuous data.
The VARCHAR data type stores character strings of varying length that contain single-byte and (if the locale supports them) multibyte characters, where m is the maximum size (in bytes) of the column and r is the minimum number of bytes reserved for that column.
What is dual table in SQL? ›
The DUAL table is a special one-row, one-column table present by default in Oracle and other database installations. In Oracle, the table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'. It is suitable for use in selecting a pseudo column such as SYSDATE or USER.
What is primary key in SQL? ›
The primary key in SQL is a single, or a group of fields or columns that can uniquely identify a row in a table. Putting it simply, it is a column that accepts unique values for each row.
What is the data type of a column in SQL? ›
The data type of a column defines what value the column can hold: integer, character, money, date and time, binary, and so on.
BLOB stands for Binary Large Object. It is a data type that can store a large amount of binary data, such as images or other multimedia files, in a database. The term is often used to refer to the data itself, as well as a file stored in this format.
What are the 5 main data types in databases? ›
Some common data types are as follows: integers, characters, strings, floating-point numbers and arrays.
What is data in SQL with example? ›
SQL data types define the type of value that can be stored in a table column. For example, if you want a column to store only integer values, you can define its data type as INT . SQL data types can be broadly divided into the following categories.
What are the 5 data types? ›
Data types
- String (or str or text). Used for a combination of any characters that appear on a keyboard, such as letters, numbers and symbols.
- Character (or char). Used for single letters.
- Integer (or int). Used for whole numbers.
- Float (or Real). ...
- Boolean (or bool).
The VARCHAR data type stores character strings of varying length that contain single-byte and (if the locale supports them) multibyte characters, where m is the maximum size (in bytes) of the column and r is the minimum number of bytes reserved for that column.
What are the data types for SQL domain? ›
Domain Types
These are based on underlying system data types: char(n): fixed-length character string, with user-specified length. varchar(n): variable-length character string, with user-specified maximum length. int: integer (length is machine-dependent).
How many data types are there? ›
Most modern computer languages recognize five basic categories of data types: Integral, Floating Point, Character, Character String, and composite types, with various specific subtypes defined within each broad category.
 - Shopify Australia)