BIGINT (TSQL Datatype)
Is a 8-bytes signed integer.
Syntax
BIGINT
Range
-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
Remarks
Use this type if you work with integers that exceed the int
range.
If a table has columns defined as bigint
, they don’t use more storage than tinyint
, smallint
or int
when written to disk, because the data is compressed.
Literal Bigint
There is no special syntax for writing a literal bigint
.
You just use a literal int
, and it will be automatically converted to bigint
when needed.
If the literal number exceeds the range of bigint
or if it contains a decimal point, it will be parsed as a literal numeric
.
PRINT TYPEOF(1000000000000000000) -- prints BIGINT
PRINT TYPEOF(1000000000000000000000) -- prints NUMERIC(22, 0)
DECLARE @a BIGINT, @b BIGINT, @c BIGINT, @d BIGINT;
SET @a = 123456; -- literal 'int'
SET @b = 1000000000000000000; -- too large to be a literal 'int', so it is a literal `bigint`
PRINT @a;
PRINT @b;
The result is:
BIGINT
NUMERIC(22, 0)
123456
1000000000000000000
Examples
DECLARE @a BIGINT = 12345;
DECLARE @b BIGINT = 647;
PRINT @a + @b + 5; -- 5 is a literal 'int'
-- 'bigint' + 'numeric' --> 'numeric'. 'numeric'/'int' --> 'numeric'
PRINT (@a + @b + 1000000000000000000. ) / 123;
-- 'bigint' + 'bigint' --> 'bigint'. 'bigint'/'int' --> 'bigint'
PRINT (@a + @b + 1000000000000000000) / 123;
The result is:
12997
8130081300813113.756097560976 <--- result is 'numeric'
8130081300813113 <--- result is 'bigint'
Examples (overflow)
DECLARE @a BIGINT = 1234;
PRINT @a * 1000000000000000000.; <--- result is 'numeric', no overflow
PRINT @a * 1000000000000000000; <--- result is 'bigint', overflow occurs
The result is:
1234000000000000000000
<Arithmetic error. BIGINT overflow.>