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
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
About the author(s)
Java and Python Developer for 20+ years, Open Source Enthusiast, Founder of https://www.askpython.com/, https://www.linuxfordevices.com/, and JournalDev.com (acquired by DigitalOcean). Passionate about writing technical articles and sharing knowledge with others. Love Java, Python, Unix and related technologies. Follow my X @PankajWebDev
Still looking for an answer?
Hello, Is it possible to enter an Array for a value in a table? Is there an ARRAY column type? Kind Regards,
- Shirajul
Type BIT is not a numeric data type, its binary. Also it is no longer part of SQL standard. So better to remove it. Type YEAR does not exist in SQL standard. Also never use year numbers with only 2 digits (remember year2k bug?) You miss the type BOOLEAN which can have values true or false. It is fundamentally different from BIT. You miss the type INTERVAL of which there are 2 basic ones: INTERVAL SECONDS (can include fractions) and INTERVAL MONTHS. Type TEXT and NTEXT are old synonyms for CLOB, so use CLOB (or CHARACTER LARGE OBJECT). Type IMAGE is an old synonym for BLOB, so use BLOB (or BINARY LARGE OBJECT).
- Mr SQL
Hello everyone here I am new at sql How can i insert data into a table which contain both Character and letter (For eg A001) Which datatype should i use here. Please let me know
- Saurabh Rawat
Good Evening Shri.Pankaj Sir, very very useful & beneficial of your SQL lecture. Great thanks full for your Guide in SQL study materials. by Rajaram
- Rajaram
Hi Pankaj, The tutorial contents and the graphical presentation are absolutely fantastic. The amount of efforts you have taken is highly appreciated and I would like to express my sincere gratitude. God Bless you dear !!
- Sylvester Marshall
some columns like salary will not take int ,smallint data types .it takes only char and varchar why???
- prasanna lakshmi
This work is licensed under a Creative Commons Attribution-NonCommercial- ShareAlike 4.0 International License.
Become a contributor for community
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
DigitalOcean Documentation
Full documentation for every DigitalOcean product.
Resources for startups and SMBs
The Wave has everything you need to know about building a business, from raising funding to marketing your product.
The developer cloud
Scale up as you grow — whether you're running one virtual machine or ten thousand.
Get started for free
Sign up and get $200 in credit for your first 60 days with DigitalOcean.*
*This promotional offer applies to new accounts only.