How to Choose Data Types for SQL Table Columns

Databases store data values of various kinds. For example, you may store the name of a product as a text string, the price of a product as a number, or the date a product is sold as a date value. These values – the product name, the price, and the sale date – are stored as different data types. In this article, we explain several SQL data types with real examples.

Creating a Table in SQL

SQL is a language for manipulating data in databases. Created in the early 1970s, it is still in use today in almost all relational databases after 50 years. Learning SQL remains wildly popular.

SQL is used as the standard data language for manipulating and querying data for the most popular databases. I don’t think I’d be wrong to say we all use applications that use SQL queries several times a day on our cell phones or computers without even realizing it.

SQL is also an easy-to-learn language, very similar to English with a shallow learning curve. Take the following SQL query for example:

SELECT employee_id, employee_name FROM employees WHERE salary > 75000

You can probably guess what data this query returns: the employee ID and name of those employees making more than $75,000. Easy, isn't it?

However, before we can query the data in a database, we need to store the data in the database. Databases use tables to store data records. A table has a set of fields (also known as columns).

To learn more about tables, I suggest the course Creating Table in SQL. It is a practical course with about 80 exercises to equip you with everything you need to start creating tables in SQL databases.

A typical table to store data about employees may have fields like employee_id, employee_name, and salary. You may include any field to the table; you may include other fields based on your needs, such as the department to which the employee belongs and the employee's birthday.

However, when you define a new field in a table, it is mandatory that you specify its data type. The main purpose of this article is to explore what data type to choose for a field when creating a table in SQL.

Let’s start by creating a simple table called foods for a carryout food company. It has just two columns or fields: food_name and food_category. Since these two columns will store text values, we use the data type VARCHAR, which is used to store text strings of any length. Let’s see the CREATE TABLE statement for this:

CREATE TABLE foods (
	food_name 		VARCHAR(50),
	food_category	VARCHAR(20)
);

Both columns (food_name and food_category) will accept text string values. However, food_name will accept values no longer than 50 characters. This limitation is specified in VARCHAR(50); similarly, food_category will accept no more than 20 characters.

Suppose we want to create a new version of the table with an additional column. We want to store the date the food item was cooked or prepared. Let’s call this additional column prepared_on and use the data type DATE for it. The CREATE TABLE statement for the new version of the table is:

CREATE TABLE foods (
	food_name 		VARCHAR(50),
	food_category	VARCHAR(20),
	prepared_on		DATE
);

Valid values for the column prepared_on include '2022-11-03' and '2022-11-02'. The exact format of the dates may vary; other common valid formats are 'mm/dd/yyyy' and 'yyyymmdd'. The following INSERT shows how to create a record in the table foods:

INSERT INTO foods
VALUES ('Chicken with mashed potatoes','Main Dish','11/08/2022');

Find more about INSERT in this article.

The SQL SELECT statement below shows how we read records in the table and see what the records look like:

SELECT food_name, food_category, prepared_on FROM foods;

In the following image, you see how the SQL client shows the column names and the data types for each column returned by the SELECT statement:

Find more about the SELECT statement here.

If you have never dealt with databases, I recommend the course Data Types in SQL. In this course, you learn about common data types in SQL Server, MySQL, Oracle, and PostgreSQL. If you have come here looking for information about building tables, a very complete course The Basics of Creating Tables in SQL teaches you the basic building blocks of creating tables in MySQL, SQL Server, Oracle, and PostgreSQL. Finally, you may want to read about the SQL Basics course in the article Why Take the “SQL Basics” Course – the reason this online course was created, the philosophy behind it, and what it contains.

Numeric Data Types

Now that the date of preparation for each food item is in the table, suppose we want to include a column to store how many days a food item may be sold before its expiration. This is a number; so far, we have not included any numeric columns in the table foods.

In SQL databases, there are several data types for numeric data. We need to analyze what type of number we have to know how to proceed.

If the values you want to store in the column are numbers without decimals, the data type to use is INTEGER. Examples of integer values are 10, 1, 1234, 302123, 0, and even negative numbers like -10 and -90. The rule is simple: any number without decimals is a valid value to be stored as an INTEGER data type. Let’s include the column valid_days as the INTEGER data type:

CREATE TABLE foods (
	food_name 		VARCHAR(50),
	food_category	VARCHAR(20),
	prepared_on		DATE,
	valid_days		INTEGER
);

Let’s say we now want to include in the table a column to store the price of each food item. We create a column called price. This column will have numeric values with decimals, so we will use the data type NUMERIC. This data type accepts decimal numbers with any number of digits after the decimal point. Then, creating the table foods looks like the following:

CREATE TABLE foods (
	food_name 		VARCHAR(50),
	food_category	VARCHAR(20),
	prepared_on		DATE,
	valid_days		INTEGER,
	price			NUMERIC(6,2)
);

In the CREATE TABLE statement above, we use the NUMERIC(6,2) data type to store prices with a maximum length of 6 digits, 2 of which are after the decimal point. For example, 10.25, 1.3, and 1234.56 are valid values for NUMERIC(6,2).

The Boolean Data Type

Another important data type is BOOLEAN. This data type is used to represent or store in a table one of only two possible values: TRUE or FALSE.

Suppose we want to identify which food items are certified organic. We may include a column named is_organic_certified of the BOOLEAN data type. As you can imagine, the value TRUE in the column is_organic_certified indicates the food item is certified organic. The new version of the CREATE TABLE statement is:

CREATE TABLE foods (
	food_name 			VARCHAR(50),
	food_category		VARCHAR(20),
	prepared_on			DATE,
	valid_days			INTEGER,
	price				NUMERIC(6,2),
	is_organic_certified	BOOLEAN
);

At this point, we have a table with various columns. Let’s create a new record using INSERT as we did earlier:

INSERT INTO foods VALUES ('Apple Pie','Dessert','11/07/2022',3,3.78, True);

And again, we do a SELECT to see the data in the table.

SELECT food_name, food_category, prepared_on,
       valid_days, price, is_organic_certified
FROM foods;

Before concluding this section, I want to recommend two articles about numeric data types. The first is “Understanding Numerical Data Types in SQL” about the several numeric data types available in SQL databases. The second is “Numeric vs. Decimal Data Types in SQL” with an interesting explanation of the differences between NUMERIC and DECIMAL SQL data types.

Data Types Related to Date and Time

We have already introduced the data type DATE for storing a single date value in an SQL table. In this section, we introduce two more data types related to date and time values.

The first is TIMESTAMP for storing a point in time with the date and the time. For example, we may store the birth date and time of a person like ’1998-03-03 11:23:15’ in a column of the data type TIMESTAMP.

Let’s try this with our table foods. Suppose we want to store not only the date the food item is prepared but also the time because the specific time may matter for food expiration in some cases. Then, the CREATE TABLE statement becomes:

CREATE TABLE foods (
	food_name 			VARCHAR(50),
	food_category		VARCHAR(20),
	prepared_at			TIMESTAMP,
	valid_days			INTEGER,
	price				NUMERIC(6,2),
	is_organic_certified	BOOLEAN
);

When we store a new record in the table foods, the value for prepared_at must follow a format like '2022-11-03 11:30:45'. That said, we do not need to store the seconds because that level of detail is not needed for food expiration. So, we may omit the seconds and use values that look like '2022-11-03 11:30'. The TIMESTAMP column will accept this value and store ‘2022-11-03 11:30:00’ in the database.

Another data type related to date and time is INTERVAL, used to represent a period of time. For example, the value ‘11:30’ of type INTERVAL represents a period of 11 hours and 30 minutes, and the value ‘1:20:35.145’ represents 1 hour, 20 minutes, 35 seconds, and 145 milliseconds.

These are not the only valid formats for INTERVAL values. There are many different formats for specifying a value of the INTERVAL type depending on which units of time (days, hours, minutes, or seconds) we want to include.

Let’s apply this data type to our table foods. Suppose we have an expiration period for each food item: 8 hours for a salad, 24 hours for mashed potatoes, etc. To store this information, we include the column expiration_period of the data type INTERVAL. The modified CREATE TABLE statement looks like this:

CREATE TABLE foods (
	food_name 			VARCHAR(50),
	food_category		VARCHAR(20),
	prepared_at			TIMESTAMP,
	valid_days			INTEGER,
	price				NUMERIC(6,2),
	is_organic_certified	BOOLEAN,
	expiration_period		INTERVAL
);

I recommend the article “What Is SQL Used For?”, which discusses why data skills like SQL are so important. There is also an interesting article titled “An Overview of MySQL Data Types” that explains the most important data types available in MySQL.

Learn SQL Data Types and Create Your Own Tables

We have covered the main SQL data types in this article. We started with text data types, then discussed numeric data types, and closed with more specific data types like BOOLEAN, INTERVAL, and TIMESTAMP.

I would also like to suggest a few more articles. The first is “What is SQL?” where you find answers to high-level questions like “what is SQL?” or “Why should I learn it?” The second is “SQL Syntax” for those who are starting the process of learning SQL. In computer sciences, language syntax is one of the main reasons for a frustrating learning experience, so getting a good start with SQL syntax is important.

There are other important topics related to data types we did not cover within the scope of this article. For example, there is a technique called casting that has to do with changing the data type of a given data to another valid data type.

SQL is an easy-to-learn language. However, there are plenty of concepts or techniques – like casting – to master for leveraging the full power of the language. Continue learning SQL, practice SQL, invest in your skills, and invest in yourself!