How to Calculate MAXINT

Introduction

MAXINT or INT_MAX is the highest number that can be represented by a given integer data type. In SQL Server this number for the INT data type is 2,147,483,647. The highest number you can store using the BIGINT data type is 9,223,372,036,854,775,807.

The question I would like to look at today is the following: How can I calculate MAXINT without utilizing another datatype.

Signed Numbers

A signed integer allows to store positive and negative numbers, while an unsigned integer can only represent positive numbers.
All signed integer data types use a single bit to indicate if the number is positive or negative. In a 32 bit integer like SQL Servers INT data type 31 bits are left to encode the actual number. That means we can store 2^31 different numbers. With zero being the smallest non negative number, the largest number for the INT data type is 2^31-1 = 2,147,483,647.

Calculations

The trouble with this formula is, that when trying to directly calculate this number, you will have an intermediate result that is out of the range of the data type.

This SELECT statement will cause an arithmetic overflow error:

Msg 232, Level 16, State 3, Line 1
Arithmetic overflow error for type int, value = 2147483648.000000.

One of the ways to deal with that is to use a data type that can store larger numbers like the float datatype:

That works for the INT datatype. However, as there is no numerical datatype in SQL Server that uses more than 64 bits to represent a value, any attempt to do this for BIGINT will result in an inaccurate estimation of the real value:

This results in 9,223,372,036,854,775,799 which is 8 under the accurate value of 9,223,372,036,854,775,807.

This raises the question I mentioned earlier: Is there a way to calculate MAXINT without using a different data type?

Binary Representations

From here on I will use MAXINT to represent the highest number for the SQL Server INT datatype and MAXBIGINT to do the same for the SQL Server BIGINT data type.

If you display MAXINT as binary number it looks like this

1111111111111111111111111111111

Each digit in a binary number represents a specific power of two. The right most digit in above number stands for 2^0 and the left most for 2^31. To calculate the decimal value for a binary number you just add the "power of two"-values for each digit multiplied with the value of that digit. So binary 1011 would be 1*2^3 + 0*2^2 = 1*2^1 + 1*2^0 = 8+0+2=1 = 11. That means, to get to MAXINT, you can just add all 31 powers of two from 2^0 to 2^30:

The code uses Itzik Ben-Gan's GetNums function to produce a list of all numbers from 1 to 31. That allows us to calculate the "power of 2"-values from 2^0 to 2^30 and add them all together using the SUM aggregate function.

The above solution works for MAXINT. It also works for MAXBIGINT if you first CAST the literal 2 to BIGINT. However, this method of calculation seems overly complicated.

Two's Complement

So far we have only looked at positive numbers. We said already that negative numbers are represented by using a single bit as an indicator. This is done with the Two's Complement encoding. I will not go into more detail about this encoding here, but it being used means that we can represent as many different negative as non negative numbers. As zero is not a negative number the possible values for negative numbers range from -(2^0) to -(2^31) for INT and -(2^0) to -(2^63) for BIGINT. So while we can't represent 2^63, we can represent -(2^63) with the BIGINT data type.

To calculate -(2^63) we can use the fact that an uneven power of a negative number is again negative. That means that -(2^63) = (-2)^63. To get to MAXBIGINT we now just need to subtract this value from -1:

-1 - (-2)^63 = -1 - (-(2^63)) = -1 + 2^63

In T-SQL the calculation looks like this for MAXINT:

For MAXBIGINT we again need to first cast the literal 2 to BIGINT to tell SQL Server that this is the data type we would like to work in:

In above calculations it is important to keep the order of the steps. Writing -POWER(-2,31)-1, while mathematically equivalent will cause SQL Server to first calculate 2^31 which will instantly cause the dreaded Arithmetic Overflow error.

Conclusion

While there is no way to directly calculate MAXBIGINT in SQL Server due to data type limitations, exploiting the fact that the range for integer data types for numbers smaller that zero is one bigger than the range for numbers greater than zero allows us to calculate this value within the ranges of the BIGINT data type.