Max Integer Values for SQL Server Numeric Data Types
SQL Server database design requires good knowledge of data types besides many other details. Max integer values for sql numeric data types is important if you don't want to spare more database space than required for integer or numeric data. Data type size knowledge is important for better SQL programming too.
For example, if database application requires to store integer values with maximum number equal to 100, then you can prefer to create table column using data type 1 byte tinyint instead of 2-bytes smallint, 4 bytes int or 8 bytes bigint data types in SQL Server.
SQL Server Integer Data Type Valid Ranges
Working with biggest numeric values is always easier for database developers. But when the database application is productive, then using larger space than necessary for storing data in database will decrease performance of your application. So it is best practise to use the correct data type for your data in a database table structure.
- Data Type
- tinyint
- smallint
- int
- bigint
- Min Integer Value
- 0
- -32,768
- -2,147,483,648
- -9,223,372,036,854,775,808
- Max Integer Value
- 255
- 32,767
- 2,147,483,647
- 9,223,372,036,854,775,807
- Size in Bytes
- 1 Byte
- 2 Bytes
- 4 Bytes
- 8 Bytes
SQL Server TinyInt Integer Data Type
Let's start with SQL Server tinyint integer data type.
What is the max int value for tinyint data type? Let's make an example.
declare @tinyint tinyint
set @tinyint = -1
set @tinyint = 0
set @tinyint = 255
set @tinyint = 256
If you execute sql tinyint numeric variable declaration and setting values for it, SQL Server engine will throw below arithmetic overflow error messages for values assigned out of range for tinyint data type.
Msg 220, Level 16, State 2, Line 3
Arithmetic overflow error for data type tinyint, value = -1.
Msg 220, Level 16, State 2, Line 6
Arithmetic overflow error for data type tinyint, value = 256.
In order to summarize max int value for tinyint integer data type is 255. And tinyint integer can not be negative. Minimum value for tinyint is 0.
SQL Server SmallInt Integer Data Type
Smallint integer data type is two-byte in size and can keep integer values up to 32767.
Unlike tinyint, smallint data type can store negative values too. The minimum integer value for smallint SQL Server data type is -32768
Let's now make sample numeric variable declarations and value assignment for SQL Server smallint integer data type.
declare @smallint smallint
set @smallint = -32769
set @smallint = -32768
set @smallint = 32767
set @smallint = 32768
Since the valid numeric data range for smallint data type is between -32768 and 32767, SQL engine will throw exception for value assignments using -32769 and 32768.
Msg 220, Level 16, State 1, Line 11
Arithmetic overflow error for data type smallint, value = -32769.
Msg 220, Level 16, State 1, Line 14
Arithmetic overflow error for data type smallint, value = 32768.
Min integer value for smallint is -32768 and max int value for smallint data type is 32767.
SQL Server Int Integer Data Type
Int SQL Server integer data type is most common since it has a wide range of numeric values enough to cover most requirements.
Most of database table identity columns are defined in Int SQL Server data type.
The valid min and max integer values for Int data type are -2147483648 and 2147483647
declare @int int
set @int = -2147483649
set @int = -2147483648
set @int = 2147483647
set @int = 2147483648
SQL engine will throw "Arithmetic overflow error converting expression to data type int" error message for integer values outside of valid range.
Above SQL int variable declarations will throw exception error messages for -2147483649 and 2147483648 (bigger than valid maximum integer value for int data type).
Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.
SQL Server BigInt Integer Data Type
SQL Server bigint numeric data type uses 8 byte space for each declaration or bigint value defined in SQL Server database. So please think twice to use bigint because of its high disk space requirement. You or your database administrator/developer might be using more disk capacity than required for a task where int data type will be enough.
Max integer value for bigint variable is 9223372036854775807. I'm sure this is too big for storing a few thousands of rows in a SQL Server database table. The minimum negative value that a bigint variable can keep is -9223372036854775808.
declare @bigint bigint
set @bigint = -9223372036854775809
set @bigint = -9223372036854775808
set @bigint = 9223372036854775807
set @bigint = 9223372036854775808
I'm sure it is not easy to remember all these numbers but you have already a rough idea how to use integer data types in SQL Server for the sake of performance and less physical disk usage.