All You Need to Know About 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:

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:

• 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 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.

• 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.

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. 

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.

Learn the Ins & Outs of Software Development

Caltech Coding BootcampExplore Program

Character String Data Types

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

• 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:

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.

Learn From The Best Mentors in the Industry!

Automation Testing Masters ProgramExplore Program

Binary Data Types

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

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

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

• 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.

SQL Data Types in Popular Databases

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.”

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 Post Graduate Program In Full Stack Web Development
, 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.