When it comes to any language, whether it is a coding language like C or a query language like SQL, the data type is one of its most integral parts. In fact, SQL has more than 30 data types that are capable of storing all kinds of real-world data. To start working with data, you need to have a clear understanding of SQL data types. 

What Is a Data Type?

SQL data types define the nature of data that can be stored in database objects, like tables. Every table has columns, and each column has a name and data type associated with it. 

You can define both of these things during the creation of the table. The data type lets the database know what to expect from each column and also determines the kind of interactions that can occur. For example, if you want a column to contain only integers, you can use the “int” data type for it.

SQL contains a variety of data types, so let’s have a closer look at the most important categories of SQL data types that are available to us.

Categories of SQL Data Types

There are six primary categories of pre-defined SQL data types that you can choose from when creating a table. These categories are as follows:

Categories_of_data_types-SQL_Data_Types

Numeric Data Types

These contain numbers and are divided into two categories: exact and approximate. 

Character String Data Types

These can contain numbers, alphabets, and symbols, and have types like “char” and “varchar.”

Unicode Character String Data Types

These are similar to character string data types, but take up twice as much storage space.

Binary Data Types

The characters in these data types are in the hexadecimal format.

Date and Time Data Types

These are used to store date and time information in data types, such as “timestamp” and “year.”

Miscellaneous Data Types

Remaining SQL data types that serve a variety of functions are grouped together in this category. For example, “CLOB” is used for large character objects and “xml” for XML data.

Let’s get a more in-depth insight into each of these categories.

Numeric Data Types

The numeric data types are divided into two subcategories:

Numeric_Data_Types-SQL_Data_Types

  • Exact Data Types
  • Approximate Data Types

The literal representation of the data value is stored in exact data types. The exact data types, the range of numbers that can be stored in each of them, and the storage space required are specified below.

Data Type

From

To

Storage Space

Example

bigint

-9,223,372,036,854,775,808

9,223,372,036,854,775,807

8 bytes

Population bigint 

int

-2,147,483,648

2,147,483,647

4 bytes

ID  int 

smallint

-32,768

32,767

2 bytes

ID smallint 

tinyint

0

255

1 byte

Age tinyint 

bit

0

1

1 bit

Present bit 

decimal

-10^38 +1

10^38 -1

5 - 17 bytes

Balance decimal(8,2) 

numeric

-10^38 +1

10^38 -1

5 - 17 bytes

Balance numeric(8,2)

  • Data types “bigint,” “int,” “smallint,” and “tinyint” can store integers, and the difference between these is their range and the amount of storage space required. 

For example, if we want to store an attribute like Student ID, “int” or “smallint” would be the most suitable data type. To store a person’s age, “tinyint” should be used. For a large number, such as the world population, you should use “bigint”

  • Data type “bit” is a single bit integer and can be valued at zero or one. It can be used for yes or no questions; for example,if a student is present in class, the number one would be used to represent yes, whereas zero would represent no. 
  • Data types “numeric” and “decimal” are written as numeric(p,s) and decimal(p,s) and contain decimal point numbers.

Precision, or p, is the total count of numbers in the data. Scale, or s, is the count of digits after the decimal point in positive numbers, and before the decimal point in negative numbers. If you use numeric, you have to define the exact precision(p) and scale(s), but in decimal, precision(p) is flexible; only scale(s) is exact.

For example, if you define an attribute balance with a numerical value of (7,2), you can store numbers, such as 34215.23, in this column.

Precision_and_scale_exact_numeric_data_type.

  • Approximate data types contain real numbers, but the data is not stored as a literal representation of the actual values.

The details of these data types are included below.

Data Type

Storage

Value of “n”

Precision

Minimum Range

Maximum Range

Example

float(n)

4 bytes

1-24

7 digits

-1.79E+308 to -2.23E-308

2.23E-308 to 1.79E+308

Pi float(6) 

float(n)

8 bytes

25-53

15 digits

-1.79E+308 to -2.23E-308

2.23E-308 to 1.79E+308

Pi float(34) 

real

4 bytes

N/A

7 digits

-3.40E+38 to -1.18E-38

1.18E-38 to 3.40E+38

Money real 

The “float” and “real” data type belong to this category. These data types also consist of  “precision,” which is the exponent of 10. It also specifies how many precise digits are significant in the number and which are signed numeric values. For example, a number like 12789.23 will be expressed as 1.278923*10^4, in this notation. Here, the number four is the precision, and 1.278923 is significant. 

Approximate_numeric_data_type

The difference between float(n) and “real” is that the variable “n” (which was created when the table was) defines the precision of the float(n), and the precision of “real” is fixed.

Introduction To Data Analytics (FREE)

Mastery to Data Analytics Basics is a Click Away!Start Learning
Introduction To Data Analytics (FREE)

Character String Data Types

All these data types can contain alphabets, numbers, and symbols. The details of these data types are shown below. 

Data Type

Type

Maximum Size

Example

char

Fixed length 

8000 characters

Roll_No char(3)

varchar

Variable length

8000 characters

Name varchar(255)

varchar(max)

Variable length

2^31-1 bytes or 2,147,483,645 characters

LargeData varchar(max)

text

Variable length 

2^31-1 bytes or 2,147,483,645 characters

LargeData text

  • char: If you want to fix the length of all data entries of a column in a table, you can define it using the “char(n)” data type in which the variable “n” determines the length. 

For example, if you define an attribute, Roll_Number char(3), all entries in this column must contain three characters.

  • varchar: For attributes like Name and Address, you should use “varchar(n)”, where “n” is used to define the maximum length, as it gives flexibility for the length of data. 

For example, the attribute Name varchar(255) can contain both names: Tom and Hannah.

  • If you want to store data that contains more than 8000 characters, you can use either varchar(max) or text. 

The difference between these two data types is that “varchar(max)” has a variable maximum storage size, whereas the maximum storage size for “text” is fixed.

Unicode Character String Data Types

The details on different data types in this category are provided below:

Data Type

Type 

Maximum Size

nchar

Fixed length

4000 characters

nvarchar

Variable length

4000 characters

nvarchar(max)

Variable length

2^31-1 bytes or 1,073,741,822 characters

ntext

Variable length

2^31-1 bytes or 1,073,741,822 characters

These data types are similar to character string data types, but each Unicode character takes up two bytes of storage space, whereas each non-Unicode character takes up one byte of storage space. Unicode characters require twice the storage space, as each character is converted into a 16-bit number that can be easily stored in any computer system. This is crucial, as it removes the real-world language barrier by encoding every character using a uniform standard.

  • All these data types “nchar”, “nvarchar”, “nvarchar(max)”, and “ntext” behave the same as “char”, “varchar”, “varchar(max)”, and text respectively.

These data types are used to store characters of various languages, such as Arabic and German.

Binary Data Types

The name, type, storage space, and an example of this category's data types are as follows:

Data Type

Type

Maximum Storage

Example

binary

Fixed length

8000 bytes

IPv4 binary(2)

varbinary

Variable length

8000 bytes

IP_Address varbinary(4)

varbinary(max)

Variable length

2GB

PDF varbinary(max)

image

Variable length

2GB

Photo image

These data types are used to store raw data, like IP addresses. The characters in the data are represented in the hexadecimal format. Each character can assume any value from zero to nine, A, B, C, D, E, and F.

  • You should use “binary” when all the data stored is of the same uniform length.

Any attribute with binary as the data type is defined as “attribute binary(n),” where n is the data's length. 

  • The “varbinary” data type should be used when the length of the data varies. 

For example, to store the attribute IP address, you can use “varbinary(n)” where n defines the maximum length of the data.

  • You can use the data type “varbinary(max)” to store data with length exceeding 8000 bytes.

This data type can be used to store various types of data, such as images, PDF documents, and MS word files.

  • Data type “image” is used to store all kinds of image files, such as JPEG format and GIF format photos and graphics.

Date and Time Data Types

Data Type 

Description

Range

Example

date

Used to store the date in the YYYY-MM-DDformat.

The value of the year should range from zero to 9999; for month it should be from one to 12, and for day, it should be from one to 31.

The date 12th June 2020 will be stored as ”date 2020-06-12”.

time

Used to store time in the HH:MI:SS format. 

Hour should be between zero and 23, minute value between 00 and 59, and the second value between 00 and 61.999999.

5:30 p.m. will be represented as ”time  17:30:00”.

datetime

Used to store both date and time information together in the YYYY-MM-DD HH:MI:SS format.

The date and time portion follows the “date” and “time” data type rules, respectively.

9:15 p.m. 23rd January 2019 will be stored as “datetime 2019-01-23 21:15:00”.

timestamp

Used to store both date and time information together in the YYYY-MM-DD HH:MI:SS format. 

'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

9:15 p.m. 23rd January, 2019 will be stored as “datetime 2019-01-23 21:15:00”.

year

Used to store the year in a two-digit or four-digit format. 

For the two-digit format, the range is from 1901 to 2155, and for the four-digit format, it is from 1970 to 2069. 

The year 2020 will be represented as “year 2020” in the four-digit format and as “year 20” in the two-digit format.

There are other essential SQL data types other than the five primary categories discussed above. Let’s get a glimpse into these as well.

Miscellaneous Data Types

Data Type

Description

xml

Used to store XML files that contain unstructured or heterogeneous data. 

json

The data in this is stored as a string, but it can be of two types: data from an external source like a website, or semi-structured data that has been grouped. 

clob

This stores character large objects that cannot be stored in char and varchar. 

blob

This stores binary large objects.

  • xml: An XML record can hold more than one data type. 

For example, in the attribute “Employee_data xml,” we can add multiple types of information, such as name and city. 

  • json: This data is stored in a certain way so that it can be transmitted to other systems. It is also a good way to group related information in a single data item. 

For example, it is beneficial from a business perspective if you want to store data from different sectors, like finance and trade, together in “Business json”.

  • clob: This can store up to 2GB of Unicode or non-Unicode alphabets, numbers, and symbols. 

A clob object is defined as “object clob(n[K|M|G]),” where n is an unsigned integer that represents the length. K, M, and G  represent kilobyte, megabyte, and gigabyte,and if any one of these is present, then the length of the data is as follows:

  • K = n*1024
  • M = n*1,048,576
  • G = n*1,073,741,824

This is used to store data, like images, audio files, and videos.

  • blob: This can store up to 2GB of binary data, and an object using blob is defined as “object blob(n[K|M|G])”. The values of n, K, M, and G hold the same values as they do with “clob.”

It is used to store non-traditional data, like voice recordings and mixed media.

Keep in mind that database systems don’t support all data types, so you need to verify before using any given data type. For example, MySQL doesn’t support any Unicode data type, and Oracle doesn’t support “datetime.”

MySQL_logo.

Oracle_logo

Some databases also have their own additional data types that you can use. For example, Microsoft SQL Server has “money” and “smallmoney,” which can be used in place of “float” and “real,” but these are database specific and not available in any other database system.

Occasionally,  some databases also use different names for certain data types. For example, in Oracle, “decimal” is called “number” and “blob” is known as “raw.” As such, it is crucial to verify the details about each SQL data type in the database you’re using.

Next Steps

With this, we come to the end of this article. In conclusion, data types are the backbone of any database, as you cannot store and manipulate data without them. 

Now that you know more about SQL data types, you are ready to start learning how to store, query, and manipulate data to become an expert in SQL. You can take the next steps in your SQL journey by learning and practice with one of the more widely used database systems, like MySQL and Oracle. Check out our next tutorial on Limit in SQL

If you liked this article and want to get certified, you can check out Simplilearn’s Business Analyst Master’s Program, which also covers SQL in great depth.

Do you have any questions for us? Ask them in our “All You Need to Know About SQL Data Types” comments section, and we’ll have our experts in the field answer them for you.

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.