Why does select SCOPE_IDENTITY() return a decimal instead of an integer?

Question

So I have a table with an identity column as the primary key, so it is an integer. So, why does SCOPE_IDENTITY() always return a decimal value instead of an int to my C# application? This is really annoying since decimal values will not implicitly convert to integers in C#, which means I now have to rewrite a bunch of stuff and have a lot of helper methods because I use SQL Server and Postgres, which Postgres does return an integer for the equivalent function..

Why does SCOPE_IDENTITY() not just return a plain integer? Are there people out there that commonly use decimal/non-identity values for primary keys?

Moonstone

Answer 1

In SQL Server, the IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p, 0), or numeric(p, 0) columns. Therefore the SCOPE_IDENTITY function has to return a data type that can encompass all of the above.

As previous answers have said, just cast it to int on the server before returning it, then ADO.NET will detect its type as you expect.

Ichinomiya

SCOPE_IDENTITY function return value is decimal(38,0) – Pepillo

Answer 2

Can't you just cast it before returning it from your query or stored proc (SPs alway return int anyway, but maybe you are using an output parameter)?

Like SELECT CAST(SCOPE_IDENTITY() AS INT) AS LAST_IDENTITY

And why it does this? Probably to be more flexible and handle larger numbers.

Tumbling

SCOPE_IDENTITY is a built in function.. so when doing like ExecuteScalar('insert...; select scope_identity()); it will return a decimal from that function rather than the expected int. – Moonstone

@Earlz, so change the SQL to ExecuteScalar('insert...; select CAST(scope_identity() AS int)'); – Tumbling

Answer 3

try using this and you'll get an integer back:

ExecuteScalar('insert...; select CONVERT(int,scope_identity())');

Houseboy

Answer 4

Scope identity return value is decimal(38,0)

CAST it, use the OUTPUT clause, or assign to an output parameter rather than SELECT SCOPE_IDENTITY() to the client

Iliac

In my case the column has the data type int but SCOPE_IDENTITY() for some reason returns System.Decimal... The OUTPUT clause returns an integer as expected, so thanks! – Delineation

Answer 5

This isn't an exact answer to the question as asked, but in the vein of the answers above, how about (in T-SQL - MS SQLServer):

select convert(bigint, SCOPE_IDENTITY())

Decidua