SQL Server Max Int Value

Posted on July 29, 2009 by Derek Dieter

3
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):
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
 Comments (RSS)
 Trackback
 Permalink
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]
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
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”
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