In this article, I described how to calculate the maximum range of various integer data types in SQL Server. TINYINT, SMALLINT, INT and BIGINT are all number data types. The difference between these data types are in the minimum and maximum values. So let's have a look at a practical example of how to calculate the maximum range of the integer data type in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

Calculating the maximum range of various integer data types.

Bigint Data Type

The Bigint data type represents an integer value. It can be stored in 8 bytes.

Formula

2^(n-1) is the formula of the maximum value of a Bigint data type.

In the preceding formula N is the size of the data type. The ^ operator calculates the power of the value.

Now determine the value of N in Bit:

  1. Select (max_length * 8) as 'Bit(s)' from sys.types Where name = 'BIGInt'

Bigint-datatype-bit-size-in-SQL-Server.jpg

Determine the maximum range of Bigint

The formula is:

2^(n-1) here N=64

  1. Select Power(cast(2 as varchar),(64) -1) as 'Bigint max range' from sys.types Where name = 'BIGInt'

Bigint-datatype-max-size-in-SQL-Server.jpg

The range of a Bigint data type is -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

INT Data Type

Int represents an integer value that can be stored in 4 bytes. INT is the short form of integer.

Formula

2^(n-1) is the formula to find the maximum of an INT data type.

In the preceding formula N is the size of data type. The ^ operator calculates the power of the value.

Now determine the value of N in Bit:

  1. Select (max_length * 8) as 'Bit(s)' from sys.types Where name = 'Int'

Int-datatype-bit-size-in-SQL-Server.jpg

Determine the maximum range of int

The formula is:

2^(n-1) here N=32

  1. Select Power(cast(2 as varchar),(32) -1) as 'int max range' from sys.types Where name = 'Int'

int-datatype-max-size-in-SQL-Server.jpg

The range of an int data type is -2,147,483,648 to 2,147,483,647.

Smallint Data Type

Smallint represents an integer value that can be stored in 2 bytes.

Formula

2^(n-1) is the formula to find the maximum of a Smallint data type.

In the preceding formula N is the size of the data type. The ^ operator calculates the power of the value.

Now determine the value of N in Bit:

  1. Select (max_length * 8) as 'Bit(s)' from sys.types Where name = 'Smallint'

Smallint-datatype-bit-size-in-SQL-Server.jpg

Determine the maximum range of Smallint

The formula is:

2^(n-1) here N=64

  1. Select Power(cast(2 as varchar),(16) -1) as 'Smallint max range' from sys.types Where name = 'SMALLInt'

Smallint-datatype-max-size-in-SQL-Server.jpg

The range of a Smallint data type is -32768 to 32767.

Tinyint Data Type

Tinyint represents an integer value that can be stored in 1 byte.

The range of a Tinyint data type is 0 to 255.