The SQL Server identity crisis

by Luca Baldini on 05/08/2013

Last week we received a support request flagging a possible problem regarding the counter fields. The text of the question was the following:

I insert a record in a table that has a counter field; in the Insert instruction I used the Instant Developer functionality that gives me the value of the counter field after insertion. But it doesn’t seem to work correctly.

We’ve received similar requests in the past, and the cause of the problem was the same as this one: the presence of a trigger which, after saving the new row, runs an Insert instruction on another table that also has a counter field. To retrieve the value in question after an Insert instruction, applications developed with Instant Developer use the @@IDENTITY property, but in this particular case the result obtained is incorrect. This “problem” is familiar to users of SQL Server, so much so in fact it has a specific name: Identity Crisis.

Some of you are already aware of the problem and in the past have asked us why we don’t use the SCOPE_IDENTITY() function to solve it. Unfortunately, as Microsoft indicates, in certain cases the @@IDENTITY and SCOPE_IDENTITY() functions can return incorrect values. The bug has been closed but the correction is only present in SQL Server 2012:

[…] that this bug is fixed in SQL Server 2012, but not in older versions, and that they will probably not provide a patch for older versions of SQL Server because of the risk of regression bugs.

If you use counters and triggers of this type, you’ve only got one choice: reread the value of the last row inserted, using other fields to identify it. It’s certainly an inconvenient solution, but it’s the only one that can give you a guarantee.

However, if you need to develop a new application, I’d like to give you some different advice. Indeed, identity crisis is not the only problem linked to the use of counters. For example, bidirectional synchronization of data generated by offline mobile applications becomes problematic, because the values of the counter fields created in the devices could conflict with each other.

So how can we solve this problem once and for all? If you develop with Instant Developer, I recommend you use the DocID: a GUID compressed into 20 characters used to uniquely identify each entity, definitively resolving the identity crisis problem. If you’d like to learn more, you can read the Instant Developer User’s Guide at pages 39 and 229-230. In addition, if you use the DocID you don’t even need to reread the value of the primary key, which is already available in the client part of your application.

Identity crisis? Never again!

References:

Leave a Comment

Previous post:

Next post: