Understanding SQL Data Types
SQL data types define the kind of values 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 categorized into the following groups:
- 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. - Special Data Types such as
CLOB
,BLOB
,JSON
,XML
,GEOMETRY
, etc.
In this article, you will learn about the various categories of SQL data types.
Relational Database Vendor Differences
Not all data types are supported by every relational database provider. For example:
- Oracle does not support
DATETIME
but providesTIMESTAMP
instead. - MySQL does not support a native
CLOB
type but usesTEXT
equivalents. - Microsoft SQL Server offers specific types such as
MONEY
andSMALLMONEY
, which are not supported by other databases.
Note: Each provider has specific size limits for data types. Check the documentation of the respective provider for details.
SQL Numeric Data Types
These data types are used for numerical values:
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 a date in the format YYYY-MM-DD . |
TIME | Stores a time in the format HH:MM:SS . |
DATETIME | Stores date and time in the format YYYY-MM-DD HH:MM:SS . |
TIMESTAMP | Stores the number of seconds since the Unix epoch (1970-01-01 00:00:00 ). |
YEAR | Stores a year in 2- or 4-digit format (e.g., 1970 to 2069 ). |
SQL Character and String Data Types
These data types are used for text strings:
Data Type | Description |
---|---|
CHAR | Fixed length, up to 8,000 characters. |
VARCHAR | Variable length, up to 8,000 characters. |
VARCHAR(max) | Variable length, theoretically up to 2 GB (not supported in MySQL). |
TEXT | Variable length, maximum of 2 GB. |
Note: Do not use these data types for Unicode data. Use Unicode-specific data types instead.
SQL Unicode Character String Data Types
Data Type | Description |
---|---|
NCHAR | Fixed length, up to 4,000 characters. |
NVARCHAR | Variable length, up to 4,000 characters. |
NVARCHAR (MAX) | Variable length, maximum of 2 GB. |
Note: These data types are not supported in MySQL databases.
SQL Binary Data Types
Data Type | Description |
---|---|
BINARY | Fixed length, up to 8,000 bytes. |
VARBINARY | Variable length, up to 8,000 bytes. |
VARBINARY (MAX) | Variable length, maximum of 2 GB of binary data. |
IMAGE | Variable length, maximum of 2 GB of binary data (deprecated in newer SQL versions). |
Special Data Types
Data Type | Description |
---|---|
CLOB | Stores up to 2 GB of text data (only available in some databases). |
BLOB | Stores large binary data (e.g., images or videos). |
XML | Stores XML data. |
JSON | Stores JSON data (natively supported in MySQL, PostgreSQL, SQL Server, and Oracle).Conclusion |
Practical Example
CREATE TABLE ExampleTable (
ID INT PRIMARY KEY,
Name NVARCHAR(100),
Age TINYINT,
Salary DECIMAL(10, 2),
JoinDate DATE,
ProfilePicture BLOB
);