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):
[cc lang=”sql”]
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
[/cc]

11 comments
asdf 21 Sep 2018 at 4:46 pm
Chandru 11 Jul 2015 at 4:13 am

DECLARE @Max INT= (SELECT MAX(Sequence + 1) FROM tbl)

returns
Warning: Null value is eliminated by an aggregate or other SET operation

can you please suggest in tbl have the Sequence value =2

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
Muna 29 Aug 2015 at 8:16 am

Nanee. Zugolvasf3d vagyok, de most musze1j felszf3lalni, hogy egyseeenn bűűűűűűűűűűn feltenni ilyen ke9peket, főleg ha a szerencse9tlen kis jfazer (e9n, me1rmint) az orsze1g me1sik ve9ge9ben lakik, e9s kiscsale1dja egyszerűen csak a hegyekben hajlandf3 nyaralni, me9g ve9letlenfcl se vedzkf6zelben, me9g ve9letlenfcl se a Balcsin…me9g ve9letlenfcl se “teszfcnk-kite9rőt-kisle1nyom-egy-fagyie9rt”, Mama szerint legale1bbis… egyszf3val, most azon morfondedrozok, hogy elkezdjem-e nyalogatni a monitort, vagy se. 😀

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

  • Thanks for visiting!
css.php