SQL Server – Why is SELECT @@IDENTITY Returning a Decimal?

ado.netsql servert-sql

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<int>(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]

Best Answer

@@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.

Related Solutions

Sql-server – SELECT/INSERT Deadlock

On the face of it, this looks like a classic lookup deadlock. The essential ingredients for this deadlock pattern are:

a SELECT query that uses a non-covering nonclustered index with a Key Lookup
an INSERT query that modifies the clustered index and then the nonclustered index

The SELECT accesses the nonclustered index first, then the clustered index. The INSERT access the clustered index first, then the nonclustered index. Accessing the same resources in a different order acquiring incompatible locks is a great way to 'achieve' a deadlock of course.

In this case, the SELECT query is:

SELECT query

...and the INSERT query is:

INSERT query

Notice the green highlighted non-clustered indexes maintenance.

We would need to see the serial version of the SELECT plan in case it is very different from the parallel version, but as Jonathan Kehayias notes in his guide to Handling Deadlocks, this particular deadlock pattern is very sensitive to timing and internal query execution implementation details. This type of deadlock often comes and goes without an obvious external reason.

Given access to the system concerned, and suitable permissions, I am certain we could eventually work out exactly why the deadlock occurs with the parallel plan but not the serial (assuming the same general shape). Potential lines of enquiry include checking for optimized nested loops and/or prefetching - both of which can internally escalate the isolation level to REPEATABLE READ for the duration of the statement. It is also possible that some feature of parallel index seek range assignment contributes to the issue. If the serial plan becomes available, I might spend some time looking into the details further, as it is potentially interesting.

The usual solution for this type of deadlocking is to make the index covering, though the number of columns in this case might make that impractical (and besides, we are not supposed to mess with such things on SharePoint, I am told). Ultimately, the recommendation for serial-only plans when using SharePoint is there for a reason (though not necessarily a good one, when it comes right down to it). If the change in cost threshold for parallelism fixes the issue for the moment, this is good. Longer term, I would probably look to separate the workloads, perhaps using Resource Governor so that SharePoint internal queries get the desired MAXDOP 1 behaviour and the other application is able to use parallelism.

The question of exchanges appearing in the deadlock trace seems a red herring to me; simply a consequence of the independent threads owning resources which technically must appear in the tree. I cannot see anything to suggest that the exchanges themselves are contributing directly to the deadlocking issue.

SQL Server Hierarchical Order – Parent-Child Tree Hierarchical ORDER BY

OK, enough brain cells are dead.

SQL Fiddle

WITH cte AS
(
  SELECT 
    [ICFilterID], 
    [ParentID],
    [FilterDesc],
    [Active],
    CAST(0 AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters]
  WHERE [ParentID] = 0
  UNION ALL
  SELECT 
    i.[ICFilterID], 
    i.[ParentID],
    i.[FilterDesc],
    i.[Active],  
    Level + CAST(i.[ICFilterID] AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters] i
  INNER JOIN cte c
    ON c.[ICFilterID] = i.[ParentID]
)

SELECT 
  [ICFilterID], 
  [ParentID],
  [FilterDesc],
  [Active]
FROM cte
ORDER BY [Level];

Best Answer

Related Solutions

Sql-server – SELECT/INSERT Deadlock

SQL Server Hierarchical Order – Parent-Child Tree Hierarchical ORDER BY

Related Question