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.>
RSQL, a simple alternative to Microsoft SQL Server