Tutorial

SQL Data Types

Updated on December 15, 2022
Default avatar

By Pankaj

SQL Data Types

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.

  1. Numeric data types such as: INT, TINYINT, BIGINT, FLOAT, REAL, etc.
  2. Date and Time data types such as: DATE, TIME, DATETIME, etc.
  3. Character and String data types such as: CHAR, VARCHAR, TEXT, etc.
  4. Unicode character string data types such as: NCHAR, NVARCHAR, NTEXT, etc.
  5. Binary data types such as: BINARY, VARBINARY, etc.
  6. 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.

Learn more about us


About the authors
Default avatar
Pankaj

author



Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
JournalDev
DigitalOcean Employee
DigitalOcean Employee badge
August 31, 2021

Thank you, informative and to the point.

- Milt

    JournalDev
    DigitalOcean Employee
    DigitalOcean Employee badge
    March 31, 2021

    Where is enum

    - Shivansh

      JournalDev
      DigitalOcean Employee
      DigitalOcean Employee badge
      September 3, 2020

      Thank you

      - Bhargav

        JournalDev
        DigitalOcean Employee
        DigitalOcean Employee badge
        August 7, 2020

        what is the data type for CSE5001

        - ezhil

          JournalDev
          DigitalOcean Employee
          DigitalOcean Employee badge
          June 3, 2020

          How to sort column VALUES in ASC order a column

          - Gowthami

            JournalDev
            DigitalOcean Employee
            DigitalOcean Employee badge
            December 2, 2019

            some columns like salary will not take int ,smallint data types .it takes only char and varchar why???

            - prasanna lakshmi

              JournalDev
              DigitalOcean Employee
              DigitalOcean Employee badge
              October 5, 2019

              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

                JournalDev
                DigitalOcean Employee
                DigitalOcean Employee badge
                August 12, 2019

                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

                  JournalDev
                  DigitalOcean Employee
                  DigitalOcean Employee badge
                  July 24, 2019

                  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

                    JournalDev
                    DigitalOcean Employee
                    DigitalOcean Employee badge
                    July 9, 2019

                    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

                      Try DigitalOcean for free

                      Click below to sign up and get $200 of credit to try our products over 60 days!

                      Sign up

                      Join the Tech Talk
                      Success! Thank you! Please check your email for further details.

                      Please complete your information!

                      Get our biweekly newsletter

                      Sign up for Infrastructure as a Newsletter.

                      Hollie's Hub for Good

                      Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

                      Become a contributor

                      Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

                      Welcome to the developer cloud

                      DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

                      Learn more
                      DigitalOcean Cloud Control Panel