The Identity Field part II - a Gotcha
This is a follow up to the post titled "Getting the ID field after an Insert". I recently saw someone struggle with this because of an easily made syntax error.
Consider this code:
Follow up post on doing this in MS Access.
SET NOCOUNT ON
INSERT INTO users(username, email)
VALUES
('#usersname#','#email#' )
SELECT @@Identity AS newId FROM users
SET NOCOUNT OFF
</cfquery>
Do you see the "error"? It's the phrase "FROM users". It's pretty easy to think you might need that "from users". After all, you are trying to retrieve the Identity field that was recently created by the system in the users table. The thing to remember is that @@Identity does not exist in the users table. It is a scope variable that exists inside this transaction.
It's not exactly wrong
Here's the other thing to keep in mind, this code is syntactically correct. The SQL server will not complain that this is in error. Why? Because you can select a constant as if it were from a table. For example, you can do the following:
FROM users
Name username Mark sjones Mark mJohnson Mark tSlovinsky ...and so on....Now consider the code above. If the value of @@Identity IS 30 and the table has 30 rows you will get back thirty rows of the number 30.
More Bad News
The thing about this error is that you may never realize it's there. Your code may work perfectly well with the syntax above. One of the uses of doing this type of identity retrieval is to insert something in a related table along with the new ID. So you write code like this:
SET NOCOUNT ON
INSERT INTO users(username, email)
VALUES
('#usersname#','#email#' )
SELECT @@Identity AS newId FROM users
SET NOCOUNT OFF
</cfquery>
<cfquery name="putGroup" datasource="#dsn#">
INSERT INTO groupUsers (groupId, userId)
VALUES
(#putUser.newId#,#groupId# )
</cfquery>
If you make this little error, your code may work exactly as expected during development. It may even work very well during load testing unless you create custom scripts capable of entering hundreds of records. The problem is that if your table (users in the this case) grows too large your code will bog down and eventually fail. It will not scale. Every insert will return a large array of numbers the size of the table. 30 or 40 thousand rows and you will start noticing the decline. It will be very hard to debug as well. Nothing in the logs will help. Nothing on the database will help. You will have to notice the record count in the debug - something you may overlook on an insert query. So watch out!
4 Comments
-
It's time to start using SCOPE_IDENTITY() instead of @@IDENTITY!!! @@IDENTITY doesn't always return the expected result!
http://weblogs.sqlteam.com/travisl/archive/2003/10...
http://msdn.microsoft.com/vbasic/using/understandi...
http://blogs.technet.com/mat_stephen/archive/2005/... -
@@identity does return the expected result IF the result you expect is the one outlined in the documentation.
-------- from BOL-------------
After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.
--------------------
The note on MSDN says exactly the same thing, as does the carters and Matt Stephens. In my original post I included 2 paragraphs on the pitfalls of @@identity - including the caveates on these other blogs.
Clearly @@identity is only to be used when there is a clear path in your statement - no triggers and no subsequent inserts in the same batch followed by references to what you expect is the first @@identity value.
The problem with this feature is that most people do not fully grasp how it works - which is why I wrote the blog in the first place :) -
I can't remember ever wanting the behavior that @@IDENTITY provides in a cf app. I almost always use SCOPE_IDENTITY() or, rarely, IDENT_CURRENT('tablename'). It's much safer. -
MIke and Roland - I give I give.... I will rewrite this to refer soley to scope_identity() .... happy?
:)
Sites to Watch
Aggregators
RSS
