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.

Solution 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;

Solution 2

select cast(1 as bigint)

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

Solution 3

declare @var as bigint
set @var = 12345

Comments

[Jason]

I can think of a dozen use cases. But, here's a simple one: SELECT POWER(256,5) will fail unless I explicitly CONVERT (or CAST per your example) 256 to a bigint as in SELECT POWER(CONVERT(bigint, 256), 5).

[Cetin Basoz]

I see, T-SQL parser is not smart enough. One way would be to do as: select power(256.0,5);

[Chris]

Not really, this makes a varbinary: SELECT (0x761CC659+0x11E1A300)

[Preza8]

Casting will make the column nullable, which might be bad in some cases.

[Cetin Basoz]

@Preza8, like what and what would be your suggestion instead?

[Preza8]

@CetinBasoz Like when it is supposed to become a primary key. As far as I am aware, there is not satisfactory answer to this solution. The workaround I went with was altering the column later. But that is also only really possible if there is no data in the table, so...

[Cetin Basoz]

@Preza8, then you need to check for uniqueness in the first place. Being null doesn't prevent being a primary key. If your primary key column wouldn't accept nulls then you could simply add a criteria as "... IS NOT NULL".

[Preza8]

That was not my point. The data do not containt any null values but the type system and the available functions do not make it possible in that case to set the type of such column to not null. I got error 8111. Maybe it is because of CLUSTERED? I don't know and I already chose a different way.