SQL Server Max Int Value
-
Posted on July 29, 2009 by Derek Dieter
-
13
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]
- Comments (RSS)
- Trackback
- Permalink
alsdkjfe
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
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]
Good idea
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. 😀
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 😉
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 ;).
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
