SQL Server Max Int Value

The maximum values for an integer in SQL Server are:

-2147483648 through 2147483647

And the byte size is 4 bytes.

Other maximum values:

  • BigInt: -9223372036854775808 through 9223372036854775807 (8 bytes)
  • SmallInt: -32768 through 32767 (2 bytes)
  • TinyInt: 0 through 255 (1 byte)

Here is the proof (thanks to BJ):

DECLARE @MAX INT, @MIN INT

SELECT @MAX = 127, @MIN = 1

WHILE @MIN = 1 BEGIN
BEGIN TRY
    SELECT @MAX = @MAX * 2 + 1
END TRY
BEGIN CATCH
    BEGIN TRY
        SET @MIN = -1 - @MAX
    END TRY
    BEGIN CATCH

        SET @MIN = 0

    END CATCH
END CATCH
END

SELECT @MIN , @MAX

8 comments
BJ 25 Mar 2012 at 8:42 pm

Bearing in mind that *int variables are binary, we can dramatically reduce the number of iterations through the loop by treating them as such. The following code will return both minimum and maximum values for all integer types, signed or unsigned, with the minimum number of iterations. Just change the type used to declare @max and @min in the first line to bigint, int, smallint, or tinyint:

declare @max int, @min int

select @max = 127, @min = 1

while @min = 1 begin
begin try
select @max = @max * 2 + 1
end try
begin catch
begin try
set @min = -1 – @max
end try
begin catch
set @min = 0
end catch
end catch
end

select @min [minimum value], @max [maximum value]

Derek Dieter 01 Jan 2013 at 3:14 pm
Subodh 28 Feb 2012 at 4:14 am

Your example takes lot of time for execution because it has to add 1000 everytime that will result in 2147483000 + 647 iterations which consume lot of time. I have reduced the iteration by using looping

DECLARE @int int
DECLARE @div bigint

SET @int = 0
SET @div=10000000000
WHILE 1 = 1
BEGIN
BEGIN TRY
SET @int = @int + @div
END TRY
BEGIN CATCH
IF @div=1
BREAK
SET @div=@div/10
CONTINUE
END CATCH
END
SELECT @int

Raj 19 Jul 2011 at 1:39 pm

For a 4 byte int, if you insert a million records everyday, you can keep doing it more than 5 years ;)

Epis 14 Dec 2012 at 4:21 pm

Actually, we exceeded int’s max on primary keys of two tables in one of our projects production DB in less than 18 months, so it’s not as unreal as you think ;).

Glen 08 Feb 2011 at 8:58 pm

Extrapolating your exactness on this in negative values – lol

DECLARE @int int
SET @int = 0
WHILE 1 = 1
BEGIN
BEGIN TRY
SET @int = @int – 1000
END TRY
BEGIN CATCH
BEGIN TRY
SET @int = @int – 1
END TRY
BEGIN CATCH
SELECT @int
BREAK;
END CATCH
END CATCH
END

result: -2147483648

“never argue with fools, they beat you with experience every time”

Derek Dieter 17 Feb 2011 at 1:50 pm

Thanks for these Glen!

Glen 08 Feb 2011 at 8:55 pm

Sorry your example needs a slight mod to work with SQL 2005;

DECLARE @int int
SET @int = 0
WHILE 1 = 1
BEGIN
BEGIN TRY
SET @int = @int + 1000
END TRY
BEGIN CATCH
BEGIN TRY
SET @int = @int + 1
END TRY
BEGIN CATCH
SELECT @int
BREAK;
END CATCH
END CATCH
END

result: 2147483647

Featured Articles

 Site Author