ColdFusion Muse

The Identity Field part II - a Gotcha

Mark Kruger August 18, 2005 12:26 PM MS SQL Server, Coldfusion Tips and Techniques Comments (4)

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.

<cfquery name="putUser" datasource="#dsn#">
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:

SELECT 'Mark' AS name, username
   FROM    users
If there are 20 records in the table you will get back 20 records. The usersnames will be from the table, but the value of the "name" column will be the same for every row. They will all say Mark.
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:

<cfquery name="putUser" datasource="#dsn#">
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>
This code will also work. Why? Because when you output a queryname.column name without being inside of a cfoutput or cfloop specifying the query, Coldfusion converts it into #queryname['col'][1]#. It takes the first row of data and uses it. So your insert into the group table will work fine because all the rows have the same number in them.

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!

  • Share:

4 Comments

  • Roland Collins's Gravatar
    Posted By
    Roland Collins | 8/18/05 2:10 PM
    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/...
  • Mark's Gravatar
    Posted By
    Mark | 8/18/05 2:35 PM
    @@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 :)
  • Mike Rankin's Gravatar
    Posted By
    Mike Rankin | 8/19/05 12:01 AM
    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.
  • Mark's Gravatar
    Posted By
    Mark | 8/19/05 9:04 AM
    MIke and Roland - I give I give.... I will rewrite this to refer soley to scope_identity() .... happy?

    :)