SQL Data Types - A Practical Guide

Data Types in SQL

SQL Data types inform a compiler or interpreter about the characteristics of the data used in a program. In simple words, a data type is a particular kind of data item which helps define the operations that can be performed on the data. There are different data types available like character data type for the letters in the alphabet or Boolean data type for true or false values.

Watch this SQL Data Types video

Data type in SQL basically defines the kind of data that will go into a particular column. All entries of one particular column will be of the same data type.
Consider the salary table from an employee database. Let’s take the e_salary column, for example. The first value in this column is of an integer type; the second is also of the integer type and, similarly, all the other entries of the e_salary column are of integer type. Now, consider another column e_dept from the same table, all values entered in this column are of a character type.

These data types contain constraints and integrity. Constraints are the limitations on data. They can either be implemented to a column or to the table. When there is a violation between the data action and the constraint, then the action is aborted. There are different types of constraints like primary key, unique key, etc.

SQL Data types are divided into three major categories, namely, numeric, character, and date and time.

Go for this in-depth job-oriented SQL certification training now!

Numeric data types store all numerical values or integer values.

• Bigint data type helps store ‘really big’ values.
• Int data type is used to store reasonably big values.
• Smallint data type is used to store values that range from −32,768 to 32,767.
• Tinyint data type stores values from 0 to 255. It’s for relatively small numbers.
• Decimal data type is used to store fractional values in two arguments. First part is the size of the value that is the total number of digits, and the second part specifies the number of digits (d) after the decimal point. Let’s say, we want to store the decimal value 12.50. Here, the size would be 4 because the total number of digits is 4, and the value of ‘d’ would be 2 because there are 2 digits after the decimal point.

Any data type can be used based on the requirement. Int is the most commonly used data type.

Get familiar with the top SQL Interview Questions to get a head start in your career!

Character data types store all alphabetic values and special characters.

• Char data type takes in one argument and has fixed length. For example, consider the size of the value to be 20. This would mean that you cannot give any value having more than 20 characters. Keeping in mind the fact that char has fixed length, i.e., if the value size is to be 30 characters, but information assigned to it is of 3 characters, then the memory consumed is of 30 characters.
• Varchar data type also takes in size as the argument. But here, it is a variable length data type, unlike char. So here if the value size is to be 30 characters, and you give only 3 characters, the memory consumed would be only of 3 characters.
• The text data type can take in a string with a maximum length of 65,535 characters.

Date and Time data types store a date or a date/time value.

• Date data type in SQL helps us specify the date in a format. Let’s say, if we want to store the date, 2 January 2019, then first we will give the year which would be 2 0 1 9, then the month which would be 0 1, and finally, the day which would be 0 2.
• Time data type helps us specify the time represented in a format. Let’s say, we want to store the time 8:30:23 a.m. So, first we’ll specify the hour which would be 0 8, then the minutes which would be 3 0, and finally the seconds which would be 2 3.
• Year data type holds year values such as 1995 or 2011

This brings us to the end of this tutorial section. Here, we will discuss various data types in SQL In the next section, we will learn how to create and drop a table in SQL.