Signed vs Unsigned Integers

The integer data type is probably one of the more common data types when working with database management systems (and with computing in general). The integer is a numeric data type that allows us to store certain kinds of numbers.

More specifically, an integer is the number zero (0), a positive natural number (e.g. 1, 2, 3, …) or a negative integer with a minus sign (e.g. −1, −2, −3, …). Integers contain no decimal or fractional part.

However, many computing environments distinguish between signed integers and unsigned integers.

Let’s take a look at the difference between signed integers and unsigned integers.

The Basic Difference

The difference between signed integers and unsigned integers can be summed up as follows:

Signed integers can represent both positive and negative numbers. For example, a signed integer can represent 0, -25 and 25.
Unsigned integers can only represent non-negative numbers (i.e. zero and positive numbers). For example, an unsigned integer can represent 0 and 25, but not -25.

The minus sign (-) in -25 is what we’re referring to when we say that this is a “signed” integer. It’s also possible to have a signed positive number (for example +25), but most programming environments tend to omit the sign on positive numbers. This is also true with numbers in general everyday use. We generally don’t include the positive sign when writing positive numbers. We usually only include the sign on negative numbers.

The sign isn’t limited to just integers. We can also include signs on floating point numbers (i.e. numbers with a decimal point).

So basically, in computer programming a numeric variable or database column is signed if it can represent both positive and negative numbers, and unsigned if it can only represent non-negative numbers (zero or positive numbers).

Signed vs Unsigned Integer Support in Programming

Some programming environments distinguish between signed and unsigned integers and others don’t. Perhaps it’s more pertinent to say that some programming environments support unsigned integers and others don’t.

For example, MySQL and MariaDB support unsigned integers but SQL Server, PostgreSQL, and many other DBMSs don’t. Also, the SQL standard doesn’t require support for unsigned integers.

Being able to choose between signed and unsigned integers allows the programmer with flexibility around the range of numbers available and storage space required.

The following table of MySQL integer types illustrates what I mean:

Type	Storage (Bytes)	Minimum Value Signed	Minimum Value Unsigned	Maximum Value Signed	Maximum Value Unsigned
`TINYINT`	1	`-128`	`0`	`127`	`255`
`SMALLINT`	2	`-32768`	`0`	`32767`	`65535`
`MEDIUMINT`	3	`-8388608`	`0`	`8388607`	`16777215`
`INT`	4	`-2147483648`	`0`	`2147483647`	`4294967295`
`BIGINT`	8	`-2⁶³`	`0`	`2⁶³-1`	`2⁶⁴-1`

Source: MySQL documentation

Take the INT type for example. We can see that both signed INT and unsigned INT use the same amount of storage (4 bytes), but their range is different:

Signed INT: -2147483648 to 2147483647
Unsigned INT: 0 to 4294967295.

So the fact that signed integers can represent both negative and positive numbers means that they lose a range of positive numbers that can only be represented with unsigned integers. This is because a signed integer has to dedicate roughly half the values to negative values, whereas the unsigned integer can dedicate its full range to positive numbers.

We can see that the storage requirement increases as we allow for larger ranges. Therefore, for optimal storage, we should try to use the most precise type whenever we design our database/applications.

So continuing with our MySQL example, when we design our database, if we know that a column is only going to contain positive numbers between 0 and 4294967295, we can use an unsigned integer. If we know it won’t go above 2147483647, then it’s less important whether it’s signed or unsigned. If we know it won’t go above 16777215, then an unsigned MEDIUMINT might be a better choice (because it requires less storage). Unless of course it won’t go above 65535 (in which a SMALLINT would be better) or 255 (in which a TINYINT would be better).

But if the column needs to hold negative numbers, then we’d need to use a signed type. The type we choose will depend on the minimum and maximum values we expect will be stored in the column.

Using Signed and Unsigned Integers

When using integers in programming environments that distinguish between signed and unsigned integers, we normally have to specify which one it is (signed or unsigned). This could be something like UINT for unsigned integers and INT for integers. Or it could be the slightly less intuitive LONG or ULONG. It could also be something like UINT8, UINT16, UINT32, or UINT64 to specify how many bits to hold. Or it could be as explicit as SIGNED INTEGER or UNSIGNED INTEGER (such as with MySQL).

However, there are some environments (such as with MariaDB) where INTEGER is short for SIGNED INTEGER, but we also have the option of explicitly stating SIGNED or UNSIGNED.

In programming environments that don’t support unsigned integers, we usually just refer to it as an INT or INTEGER. That said, the exact keywords we can use for the integer type will depend on the syntax rules of the particular programming environment, and whether or not that environment has different types for different storage requirements (for example BIGINT, SMALLINT, etc).

In MySQL for example, if we want to convert a number to an integer, we need to specify whether it’s a signed integer or unsigned integer. The INT and INTEGER keywords are optional.

MySQL example:

SELECT 
    CAST( 123.45 AS SIGNED ),
    CAST( 123.45 AS SIGNED INT ),
    CAST( 123.45 AS SIGNED INTEGER );

In MySQL we can use either SIGNED INTEGER or just SIGNED to indicate a signed integer. Same concept applies to unsigned integers – either UNSIGNED or UNSIGNED INTEGER. Actually, when we do this in MySQL, it produces a signed or unsigned BIGINT value.

But in SQL Server, which doesn’t support unsigned integers, we simply use INT or INTEGER.

SQL Server example:

SELECT 
    CAST( 123.45 AS INT ),
    CAST( 123.45 AS INTEGER );

That said, SQL Server actually has four integer types: TINYINT (1 byte), SMALLINT (2 bytes), INT (4 bytes), and BIGINT (8 bytes). So we can replace INT or INTEGER for the applicable type.

In the above examples, the values 123.45 will be truncated to just 123 when converted to integers, regardless of whether they’re signed or unsigned. This is because integers contain no decimal or fractional part.

Two’s Complement

Behind the scenes, the most common method used for representing signed and unsigned integers in computer science is known as two’s complement.

Two’s complement is a mathematical operation to reversibly convert a positive binary number into a negative binary number with equivalent (but negative) value, using the binary digit with the greatest place value to indicate whether the binary number is positive or negative (the sign).

Take Windows data types INT64 and UINT64. With INT64, the first bit (Most Significant Bit (MSB)) is the signing bit. But with UINT64, the MSB is not reserved for signing. Specifically:

An INT64 is a 64-bit signed integer (range: –9223372036854775808 through 9223372036854775807 decimal). The first bit (Most Significant Bit (MSB)) is the signing bit. Source: Microsoft
A UINT64 is a 64-bit unsigned integer (range: 0 through 18446744073709551615 decimal). Because a UINT64 is unsigned, its first bit (Most Significant Bit (MSB)) is not reserved for signing. Source: Microsoft

Converting a Signed Integer to an Absolute Value

Many programming environments have an ABS() function that allows us to get the absolute value from a given number. An absolute value is the unsigned version of the number. In other words, the absolute value describes the distance from zero that the number resides on the number line, without considering direction.

So continuing with our MySQL example, we can use the MySQL ABS() function to return the absolute value of a given signed integer:

SELECT 
    CAST( -123.45 AS SIGNED INTEGER ) AS "Signed Integer",
    ABS(CAST( -123.45 AS SIGNED INTEGER )) AS "Absolute Value";

Result:

+----------------+----------------+
| Signed Integer | Absolute Value |
+----------------+----------------+
|           -123 |            123 |
+----------------+----------------+

For other DBMSs, see: