Author: Chuck Heinzelman
Reviewers: Kevin Cox, Kun Cheng, Michael Thomassy
In a recent customer engagement, I was presented with a problem that I have seen in the past and am surprised that I don’t see more often.
Take the following table as an example:
CREATE TABLE dbo.ResultSetTest
ID integer IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
TestData varchar(255) NOT NULL
This is a simple table with an Identity column – I’m sure most of us have a table like this (with more columns, of course) in the systems that we work with.
The customer had code to insert a new row into the table and return to the calling application the value assigned to the ID column. The code was similar to the following:
INSERT INTO dbo.ResultSetTest
SELECT @@Identity AS ID;
Given this T-SQL batch, how many result sets would you expect to receive? If you answered 1 I would not be surprised – as this is what my customer was expecting. In fact – depending on the driver and execution method used (we were using the Microsoft JDBC Driver for SQL Server) – this T-SQL batch can generate 2 result sets (which is what my customer was seeing). The first result set (from the INSERT statement) was empty, and the second result set (from the SELECT statement) contained the value of the ID column.
So, how do you handle a situation like this? I can think of several ways:
More information about the OUTPUT clause can be found in SQL Server Books Online at http://msdn.microsoft.com/en-us/library/ms177564.aspx.
When writing T-SQL statements and batches, you need to be aware of the clients that your data consumers will be using to retrieve the data. Different drivers can act differently, and you need to be prepared when you get calls saying “The statement you wrote is not doing what it should be doing!”
What was the connection string properties of the connection that generated this behaviour?
Of course, you shouldn't use "@@IDENTITY" at all; use "SCOPE_IDENTITY()" or the OUTPUT clause instead.
Chuck, I didnt know about that behavior, that was worth a post. I like the output option as well. I also prefer scope_identity() over @@Identity, just to make sure that some future trigger doesn't cause me to get unexpected values back.
Thank you for the comment.
I would not say that you shouldn't use @@IDENTITY at all...but it is important to know your environment and understand that it could return a different result than you may expect.
Thanks for the comment.
I've seen the behavior in the past, but until now I've never had a real reason to track down what was happening or why. Depending on the provider, SET NOCOUNT ON might solve the problem (no results from the first statement), but that should be tested to make sure that it does not cause other problems.
Another way to handle the situation is to suppress the first record set from the insert by adding SET NOCOUNT ON. Then, before SELECT for the new identifier, SET NOCOUNT OFF. The second setting is not needed because the SELECT will return the records to the caller anyway, even without the NOCOUNT setting turned "OFF".
StackOverflow has (my) question abut SET NOCOUNT ON: stackoverflow.com/.../27535
The last update was to show a similar issue with JPA and stored procedures: stackoverflow.com/.../27535