SQL Data Types

SQL Data Types denotes the type of data that can be stored in a table column. Each column in a table is associated with a data type. For example, for storing integer types data in a column, you need to define it’s datatype as int.

Whenever you are creating a table in the database, you need to specify datatypes for each of column. You can choose the datatypes as per your requirement.

SQL Data Types

SQL Data Types can be broadly divided into the following categories.

  1. Numeric Data Types such as int, bigint, tinyint, float, real, etc.
  2. Character String Data Types such as char, varchar, string, etc.
  3. Date and Time Data Types such as Date, Time, DateTime, etc.
  4. Unicode Character Data Types such as nchar, nvarchar, etc.
  5. Binary Data Types such as binary, varbinary, etc.
  6. Miscellaneous data types – clob, blob, xml, cursor, table etc.
SQL Data Types
SQL Data Types

SQL Data Types Important points

Following are the some of the important points regarding SQL Data Types.

  1. All the data types are not supported by every database vendors. For example, Microsoft SQL Server database has money and smallmoney datatypes which are not supported by other relational database vendors.
  2. Each database vendor has own maximum size limits for different data types. However you don’t have to remember all the limits.

List of the most popular SQL Data Types

SQL Numeric Data Types

Datatype From To
bit 0 1
tinyint 0 255
smallint -32,768 32,767
int -2,14,74,83,648 2,14,74,83,647
bigint -9,223,372,036, 854,775,808 9,223,372,036, 854,775,807
decimal 1E+38 10^38 -1
numeric 1E+38 10^38 -1
float -1.79E + 308 1.79E + 308
real -3.40E + 38 3.40E + 38

SQL Character String Data Types

Datatype Description
CHAR Fixed length with maximum length of 8,000 characters.
VARCHAR Variable length storage with maximum length of 8,000 characters.
VARCHAR(max) Variable length storage with provided max characters, not supported in MySQL.
TEXT Variable length storage with maximum length of 2,147,483,647 characters.

SQL Date and Time Data Types

Datatype 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 2 digit or 4 digit format. Range 1901 to 2155 in 4-digit format. Range 70 to 69, representing 1970 to 2069.

SQL Unicode Character Data Types

Datatype Description
NCHAR Fixed length with maximum length of 4,000 characters
NVARCHAR Variable length storage with maximum length of 4,000 characters
NVARCHAR(max) Variable length storage with provided max characters
NTEXT Variable length storage with maximum size of 1GB data

SQL Binary Data Types 

Datatype Description
BINARY Fixed length with maximum length of 8,000 bytes
VARBINARY Variable length storage with maximum length of 8,000 bytes
VARBINARY(max) Variable length storage with provided max bytes
IMAGE Variable length storage with maximum size of 2GB binary data

SQL Miscellaneous data types 

Datatype Description
CLOB Character large objects that can hold up to 2GB
BLOB For binary large objects
XML for storing xml data
JSON for storing JSON data

Summary: In this tutorial, you have learned about the basic idea about SQL Data Types and their uses.