How to specify a BIGINT literal in T-SQL?

Question

Aside from wrapping my literal in a CONVERT function, is there a way to specify that I want e.g. 12345 represented as a BIGINT and not an INT? In C#, I could specify 12345L, but I'm unaware of equivalent functionality in T-SQL.

Answer 1

You have to explicitly declare or cast to a bigint.

While there are prefixes and symbols for some other datatypes (binary, float, money, etc.), I don't think there is a way to do this in T-SQL for bigint that doesn't involve either explicitly declaring the bigint or casting/converting to it.

In fact, at least for a select...into operation, SQL Server will use a numeric (decimal) datatype once your integer literals go beyond what can be stored in an int.

select 2000000000 as col into test;
select * from information_schema.columns where table_name = 'test';
-- DATA_TYPE: int
drop table test;

select 3000000000 as col into test;
select * from information_schema.columns where table_name = 'test';
-- DATA_TYPE: numeric
drop table test;

select cast(3000000000 as bigint) as col into test;
select * from information_schema.columns where table_name = 'test';
-- DATA_TYPE: bigint
drop table test;

declare @col bigint = 3000000000;
select @col as col into test;
select * from information_schema.columns where table_name = 'test';
-- DATA_TYPE: bigint
drop table test;