SQL Data Types

What are SQL Data Types?

The Structured Query Language (SQL) comprises several different data types that allow it to store different types of information in a database. SQL data types assist in the data validation process and are essential to the mechanics of the database engine. SQL comes in different dialects, and each dialect shares common data types, as well as including some that are unique.

Curious how SQL fits into the big picture? Use the form below to download our World of BI cheat sheet to get an overview of the various roles within BI and data analysis, and the key tools needed to advance your career.

Numeric Data Types

Numeric data types are used to store numeric data such as prices, accounting values, and ratios.

INT

The integer data type stores positive and negative whole numbers like -1, 0, 1, 2. The INT data type cannot store fractional or decimal values like 2.5. It is useful in storing information such as serial numbers or IDs.

An integer takes 4 bytes of storage and has a range in signed integers, i.e., allowing for negative numbers starting from -2,147,483,648 to 2,147,483,647. In unsigned integers, i.e., using only positive integers and zero, the range is from 0 to 4,294,967,295. In the case of finance, it can store data like the number of shares, which is usually a whole number.

SMALLINT

SMALLINT stands for small integer. It is the same as the INT data type, except that it takes only 2 bytes of storage and has a range of -32,768 to 32,767 in signed integers and a range of 0 to 65535 in unsigned integers.

The SMALLINT data type is useful if the user knows that the data will be in a certain range. Using the right integer type helps in reducing the amount and cost of storage for the database.

BIGINT

BIGINT stands for big integer and is the same as the integer data type except that it can hold values up to double in size and takes 8 bytes of storage. It means the range is an order of magnitude larger.

The range in signed integers is 2^63 to 2^63 -1, and in signed integers, it is 0 to 2^64 -1. It is used to store large values and should be used sparingly given the high storage requirement. In the case of finance, the BIGINT type is apt for accounting values on the financial statements, though most values can be accommodated by the INT data type.

A summary of integer data types can be found in the table below:

DECIMAL

The DECIMAL data type is used to store numerical values that are not whole numbers. The DECIMAL data type requires the user to provide a level of precision or the number of digits after the decimal point. The precision level is fixed, and the database will ignore any digits beyond the precision level.

FLOAT

FLOAT stands for the floating-point numbers. The FLOAT data type is like the DECIMAL data type but without the need to define a set precision level. It is appropriate for calculated fields, which come with varying precision.

Character and String Data Types

Character and string data types are used to store alphabetical or alpha-numeric data like names and addresses.

CHAR

CHAR stands for the character data type. The CHAR data type is used to store alphanumeric values of a fixed length. Using the CHAR data type requires the user to specify the length of the strings that are allowed. CHAR should be used when the entered data is of a consistent length.

VARCHAR

VARCHAR stands for the variable character data type. The VARCHAR data type is used to store alphanumeric values of a variable length. It should be used when the entered data varies in length between the different records, and there is no reasonable upper bound on the length of data being stored.

For example, VARCHAR can be used to store a field like addresses. They will be all different lengths for different users and will include different details based on location.

Date and Time Data Types

Date data types are used to store date and time objects in a variety of formats. They are essential for time series data, as well as for storing timestamps in the database. The date and time data types are very powerful as they make it possible to compare two dates and query the database based on dates.

DATETIME

The DATETIME data type is used to store dates and timestamps. Even though dates can be stored as strings in a CHAR or VARCHAR data type, this will reduce the date functionality that we can use with them. The DATETIME data type makes it easy to compare dates and times as well as build queries based on dates.

The DATETIME data type can be set to various granularities from day, down to a fraction of a second, depending on what data is being stored. For example, closing prices can be stored at a daily frequency, but quotes can be stored at minute or smaller intervals.

Unique Data Types

All data types are common across all SQL dialects, but some dialects also include a few data types that are unique. For example, Microsoft SQL Server uses a “money” data type that allows users to store currencies and format them with symbols.

Another example of a unique data type is MySQL. It supports a special data type, “JSON,” which stores JSON objects. JSON, which stands for JavaScript Object Notation, is a widely used data format to relay data between different parts of an application.

More Resources

Check out CFI’s SQL Fundamentals course to learn more about SQL Data Types and other SQL concepts. To keep learning and developing your knowledge base, please explore the additional relevant resources below:

• Business Intelligence vs. Data Science
• Python Variables
• Structured Query Language (SQL)
• VBA Variable Types
• See all data science resources