CODEWITHSUNDEEP
sql-servert-sqllong-integerliteralsbigint
Jason
Jason

Reputation: 621

How to specify a BIGINT literal in T-SQL?

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.

Upvotes: 21

Views: 18577

Answers (4)

Tim Lehner
Tim Lehner

Reputation: 15251

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;

Upvotes: 14

Cetin Basoz
Cetin Basoz

Reputation: 23807

select cast(1 as bigint)

IOW you simply cast your value. What would be the purpose?

Upvotes: 10

Andrey Klochkov
Andrey Klochkov

Reputation: 168

You can use decimal or hex literal like:

declare @variable bigint
set @variable = 0x7FFFFFFFFFFFFFFF
select @variable
set @variable = 9223372036854775807
select @variable

Upvotes: 0

Gordon Bell
Gordon Bell

Reputation: 13633

declare @var as bigint
set @var = 12345

Upvotes: 3

Related Questions