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 provides TIMESTAMP instead.
  • MySQL does not support a native CLOB type but uses TEXT equivalents.
  • Microsoft SQL Server offers specific types such as MONEY and SMALLMONEY, 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
);

Conclusion

This tutorial has covered the most important SQL data types and highlighted the differences between various RDBMS. When choosing a data type, consider the compatibility with your database and the specific requirements of your project.

Create a Free Account

Register now and get access to our Cloud Services.

Posts you might be interested in: