Why is `SELECT @@IDENTITY` returning a decimal? I'm using Dapper to execute the following query against a SQL Server 2008 R2 Express instance from a ASP.NET MVC 3 (.NET 4.0) application.

INSERT INTO Customers (
         Type, Name, Address, ContactName, 
         ContactNumber, ContactEmail, Supplier)
VALUES (
         @Type, @Name, @Address, @ContactName, 
         @ContactNumber, @ContactEmail, @Supplier)

SELECT @@IDENTITY


The call to connection.Query&lt;int&gt;(sql, ...) is throwing an Invalid Cast Exception. I've debugged it and it's at the point where Dapper calls GetValue on the returned SqlDataReader.

The return type of GetValue is Object, inspecting it in the debugger show's it's a boxed decimal.

If I change the select to SELECT CAST(@@IDENTITY as int), the return of GetValue is a boxed int and the exception isn't thrown.

The Id column is definitely of type int; Why would SELECT @@IDENTITY return a decimal? 

Some additional information:


The database is brand new.
The Customers table is the only object I've added to it. There are no other (user) tables, views, triggers or stored procedures in the database.
There are 10 rows in the database, there Id's are 1,2,3,4,5,6,7,8,9,10 (i.e. the column isn't beyond the limits of an int).


My table definition is

CREATE TABLE [dbo].[Customers](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Type] [int] NOT NULL,
    [Name] [nvarchar](255) NOT NULL,
    [Address] [nvarchar](1000) NOT NULL,
    [ContactName] [nvarchar](255) NOT NULL,
    [ContactNumber] [nvarchar](50) NOT NULL,
    [ContactEmail] [nvarchar](255) NOT NULL,
    [Supplier] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (
    PAD_INDEX  = OFF, 
    STATISTICS_NORECOMPUTE  = OFF, 
    IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS  = ON, 
    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
@@identity returns a numeric(38,0).  You'll need to cast it to get it to an int.
SELECT CAST(@@identity AS INT)

Also, try using scope_identity instead.  If you have any triggers on the Customers table, you may end up getting the last identity from another table.

Finally, since you're using dapper, you'll want to wrap all of that inside a stored procedure so that you are guaranteed to execute the insert and then the select on the identity in the same batch.
Theoretically, it should work most of the time to execute both of those on their own.  But issues could arise if you have to go to the database twice.  (E.g. how does this work with connection pooling?  What about dropped connections? etc.)  If you just throw it all in a stored procedure, you won't have to worry about that extra effort down the road.