Understanding Numerical Data Types in SQL

Working with databases of any kind means working with data. This data can take a couple of predefined formats. As you start on your learning path with LearnSQL.com, you will start to understand SQL's different data types. In this article, we will cover different variations of the SQL numeric data type. We'll also examine some functions that convert data from one type to another.

Creating tables is the first step in any SQL coding project. You do this using DDL (Data Definition Language) statements like CREATE and DROP. Once you've set up your table, you start listing column names and data types in SQL. Data types tell your database what information to expect for that column.

The best way to learn how to create tables in SQL is our interactive course The Basics of Creating Tables in SQL. It contains 80 hands-on exercises that will teach you the CREATE TABLE command, primary and foreign keys, and how to modify a table in a database. In each exercise you write real SQL code that modifies the structure of the database. With each exercise solved, you increase your confidence in you SQL skills.

Let's say you have a table of ‶users”. Each user has information in a name column and a phone number column. Names would be stored in a character column. A phone number would be in a numerical column. So, numerical columns store numbers and all numbers are the same, right? Guess again.

There are different types of numbers, and there are different types of numerical columns. Anyone with a background in statistics knows that there are four different scales that apply to numbers: nominal, ordinal, interval, and ratio. Unlike other data types, numerical types can represent all of these scales (that's why rather than talking about SQL numeric data type, we talk about types.) But how are these scales different from each other?

• Nominal values differentiate by ‶name” only. Nominal values aren't treated like numbers; you can't add or subtract them, and they have no inherent order. For example, student ID numbers and telephone numbers are nominal values. They function more like a label than a number. We can say that our phone numbers are equal (same importance, same length) but we cannot compare them or say that one is first. We cannot add them and get a meaningful phone number.
• Ordinal values provide order or rank. In ordinal values, the order between them is the significant thing. Suppose you had a scale of 1 to 3 that rated your mood. Feeling unhappy would get you a ‶1”; content gets a ‶2”, and happy has a value of ‶3”. You know that happy ranks higher than content or unhappy, but that's about it. You can't add ‶content” and ‶unhappy” and get a ‶happy”.
• Interval values show exact differences. In an interval scale, the differences between values are what is important. If you subtract 90 degrees centigrade from 100 degrees centigrade, you get a 10-degree difference. Years, dates, and most personality measures are interval measures. Interval values are numerical and are represented as numerical in the database.
• Ratio values are intervals with a defined zero value. Like an interval scale, a ratio has measurable differences between values. In a ratio scale, though, a zero value means there is nothing to be measured. For example, think about mass, length, and duration. If any of these have a zero value, there's nothing there. Ratio scales are very important in science.

Each SQL numeric data type is used to represent all of the above values, especially intervals and ratios. You can compare character values in SQL, so one could argue that character values can also represent interval data. However, that's a topic for another article.

In SQL, numbers are defined as either exact or approximate.

The exact numeric data types are SMALLINT, INTEGER, BIGINT, NUMERIC(p,s), and DECIMAL(p,s). Exact SQL numeric data type means that the value is stored as a literal representation of the number's value.

The approximate numeric data types are FLOAT(p), REAL, and DOUBLE PRECISION. These represent real numbers, but they are not represented as exact numbers in the database. Rather, they are an approximation of the real number because of the way that computer systems represent numbers. If this sounds confusing, rest assured that we'll explain it in detail later.

Let's start our consideration of SQL numerical types with the exact or numeric data types.

SQL Numeric Data Type

SQL's exact numeric data types consist of NUMERIC(p,s) and DECIMAL(p,s) subtypes. They are exact, and we define them by precision (p) and scale (s). Precision is an integer that represents the total number of digits allowed in this column. These digits are in a particular radix, or number base – i.e. binary (base-2) or decimal (base-10). They are usually defined with a decimal point. The scale, also an integer value, represents the number of decimal places to the right (if positive) or left (if negative; this is rarely used) of the decimal point.

Let's look at an example. Suppose that you defined a ‶balance” column as NUMERIC with a precision of 8 and a scale of 2.

The DDL would look like this:

CREATE TABLE account (
accountNo integer,
balance numeric(8,2)
);

The ‶balance” column can safely store the number 173226.62.

P represents the total number of all digits and s represents the two digits after the decimal.

There is a small difference between NUMERIC(p,s) and DECIMAL(p,s) SQL numeric data type. NUMERIC determines the exact precision and scale. DECIMAL specifies only the exact scale; the precision is equal or greater than what is specified by the coder. DECIMAL columns can have a larger-than-specified precision if this is more convenient or efficient for the database system.

In other words, DECIMAL gives you some leeway.

Keep in mind that financial data such as account balances must be stored as NUMERIC or DECIMAL data types.

Also, be aware that many top database management systems have vendor-specific representations of numeric types (e.g. Oracle's NUMBER data type). These implementations usually do not differentiate between NUMERIC and DECIMAL types. (In Oracle, both are the NUMBER type).

Common Numeric-Type Mistakes

When inserting data into a NUMERIC column, remember its precision limits. If you try to insert too large a number, you might get an error. For example, we want to insert the following:

INSERT INTO account(accountNo, balance) VALUES(1313,12331411.23);

This will produce an error. Why? We try again with a similar number:

INSERT INTO account(accountNo, balance) VALUES(1313,123314.1123);

The second attempt works. This is because the RDBMS rounds the inserted number by discarding of any ‶extra” digits to the right of the decimal point. In this case, it kept ‶.11” but discarded the ‶23” following. Note that If the first discarded digit is a 5 or above, the RDBMS will round up the leftmost digit.

In our example, that would mean if you inserted:

INSERT INTO account(accountNo, balance) VALUES(1313,123314.1153);

…the balance in the database would be 123314.12.

To learn more about rounding and common numerical functions, check out the SQL Basics course.

The Integer Data Types

Integer data types hold numbers that are whole, or without a decimal point. (In Latin, integer means whole.) ANSI SQL defines SMALLINT, INTEGER, and BIGINT as integer data types. The difference between these types is the size of the number that they can store.

Below, we can see Microsoft SQL's definition of various integer data types:

For these types, the default size of the column is important. Defining a smaller column size for smaller integer types (if you know the max size in advance) can help keep your tables as small as possible.

Common Integer-Type Mistakes

INTEGER columns round decimal points. To explain, let's modify our table a little:

CREATE TABLE account (
accountNo integer,
balance integer
);

If we run this statement:

INSERT INTO account(accountNo, balance) VALUES(1313,123314.3153);

… the inserted values are rounded to the first digit before the decimal point. Suppose we insert 123314.5 into the balance column:

INSERT INTO account(accountNo, balance) VALUES(1313,123314.5);

Because we changed the value behind the decimal point to a value equal to or greater than 5, we would get 123315.

If you're using integer data types in formulas, know that rounding can cause inconsistencies in formulas. If you subtract 123314.3153+123314.3153:

INSERT INTO account(accountNo, balance)    VALUES(1313,123314.3153+123314.3153);

.. the inserted value would be 123314. If you subtract the same values we added before from the balance:

SELECT BALANCE - 123314.3153 FROM ACCOUNT; 

… the result, now a decimal number, would be 123314.3147. This is a clear inconsistency. You can avoid it by defining appropriate column data types according to what operations will be done on the columns.

The Float Data Types

Float and float-related SQL numeric data type hold approximate numeric values. They consist of a significant (a signed numeric value) and an exponent (a signed integer that specifies the magnitude of the significant). These data types have a precision, or a positive integer that defines the number of significant digits (exponent of the base of the number).

This type of data representation is commonly called floating-point representation.

If we were to represent 173226.62 in this notation (with a base of 10), it would look like this.

The value of an approximate numeric value is its significant multiplied by 10 to the exponent.

To truly understand the floating point SQL numeric data type, you will have to dig into a little bit of computer science. It can be fun, but at this stage of your SQL journey I believe it is overkill. For now, just remember that there are three ANSI-standard SQL approximate types: FLOAT(p), REAL, and DOUBLE PRECISION.

The difference between FLOAT(p) and REAL is that FLOAT has a binary (not decimal) precision equal to or greater than the defined value. REAL has a predefined precision based on the database implemented. In normal working life, FLOAT is rarely used; REAL and DOUBLE PRECISION are tied to particular system implementations and developers tend to pass system implementation work to the DBAs and Sysadmins.

The difference in REAL and DOUBLE PRECISION is that REAL represents numbers in 34 bits and DOUBLE PRECISION in 64 bits.

Working with Approximate Types

It's very important to remember that this SQL numeric data type sacrifices precision for range, thus the name approximate.

In calculations, approximate types may give you weird results – like 204.000000056 where the exact result should be 204. If you are building your database for an engineering or scientific application, floating data types should be fine. There is also the difference in speed; if you are doing an exceptionally large number of complicated computations (e.g. trigonometric functions, etc.) float types should be much faster than other numerical data types. On the other hand, if you are working on a financial, banking, or other business application, using decimal representation is more appropriate.

The SQL numerical data type catalogue is not limited to the integer- and decimal-related ones. They are reflections of the need to store data in a way that's safe, predictable, and usable. As with any programming language, they remind us of the computer science aspect of databases and SQL.

To practice each SQL numeric data type and come to grips with their possibilities, usage, constraints, and common mistakes, go to LearnSQL.com and have fun!