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:
- Select (max_length * 8) as 'Bit(s)' from sys.types Where name = 'BIGInt'
Determine the maximum range of Bigint
The formula is:
2^(n-1) here N=64
- Select Power(cast(2 as varchar),(64) -1) as 'Bigint max range' from sys.types Where name = 'BIGInt'
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:
- Select (max_length * 8) as 'Bit(s)' from sys.types Where name = 'Int'
Determine the maximum range of int
The formula is:
2^(n-1) here N=32
- Select Power(cast(2 as varchar),(32) -1) as 'int max range' from sys.types Where name = 'Int'
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:
- Select (max_length * 8) as 'Bit(s)' from sys.types Where name = 'Smallint'
Determine the maximum range of Smallint
The formula is:
2^(n-1) here N=64
- Select Power(cast(2 as varchar),(16) -1) as 'Smallint max range' from sys.types Where name = 'SMALLInt'
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.
Jiyaul MustaphaPosted Aug 2, 2019, 12:08 AM
In this articleData type Range Storage bigint -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) 8 Bytes int -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) 4 Bytes smallint -2^15 (-32,768) to 2^15-1 (32,767) 2 Bytes tinyint 0 to 255 1 Byte
wael shamiriPosted Jul 22, 2018, 9:08 AM
Nic..................................
Subhasis BilasPosted Mar 8, 2018, 6:35 AM
Perfect one... Keep it up
Upendra Pratap ShahiPosted Feb 4, 2016, 6:06 AM
nice one...