The maximum value for an integer in SQL Server is:
-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:
DECLARE @int 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
Popular search terms:
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
For a 4 byte int, if you insert a million records everyday, you can keep doing it more than 5 years
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”
Thanks for these Glen!
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