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

sql-server primary-key scope-identity
Earlz picture Earlz · Apr 8, 2010 · Viewed 27.6k times · Source

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?

Answer

Christian Hayter picture Christian Hayter · Apr 8, 2010

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.

Related questions

Add primary key to existing table

I have an existing table called Persion. In this table I have 5 columns: persionId Pname PMid Pdescription Pamt When I created this table, I set PersionId and Pname as the primary key. I now want to include one more column …

Read More
SQL Server add auto increment primary key to existing table

As the title, I have an existing table which is already populated with 150000 records. I have added an Id column (which is currently null). I'm assuming I can run a query to fill this column with incremental numbers, and then …

Read More
What are the best practices for using a GUID as a primary key, specifically regarding performance?

I have an application that uses GUID as the Primary Key in almost all tables and I have read that there are issues about performance when using GUID as Primary Key. Honestly, I haven't seen any problem, but I'm about …

Read More