TSQL Auto Increment on Update

Question

SQL Server 2008+

I have a table with an auto-increment column which I would like to have increment not only on insert but also update. This column is not the primary key, but there is also a primary key which is a GUID created automatically via newid().

As far as I can tell, there are two ways to do this.

1.) Delete the existing row and insert a new row with indentical values (plus any updates).

or

2.) Update the existing row and use the following to get the "next" identity value:

IDENT_CURRENT('myTable') + IDENT_INCR('myTable')

In either case, I'm forced to allow identity inserts. (With option 1, because the primary key for the table needs to remain the same, and with option 2 because I'm updating the auto-increment column with a specific value.) I'm not sure what the locking/performance consequences of this are.

Any thoughts on this? Is there a better approach? The goal here is to maintain an always increasing set of integer values in the column whenever a row is inserted or updated.

While you can "get" the next Identity value for a specific table, it normally doesn't help you except for testing that value. In particular you cannot update the identity value of an existing row to what should be "next". You can manage to serialize a primary key using application logic, though I'd only do it if consecutive values are absolutely required because of the performance hit. I'll add something to my answer if that's of interest. — hardmath
– hardmath, Commented Jun 28, 2011 at 19:44

hardmath · Accepted Answer · 2011-06-28 19:36:31Z

I think a column of type rowversion (formerly known as "timestamp") might be your simplest choice, although at 8 bytes these can amount to fairly large integers. The "timestamp" syntax is deprecated in favor of rowversion (since ISO SQL has a timestamp datatype).

If you stay with the Identity column approach, you would probably want to put your logic into an UPDATE trigger, which would effectively replace the UPDATE with the INSERT and DELETE combination you've described.

Note that Identity column values are not guaranteed to be sequential, only increasing.

I had researched this, but I wasn't sure it was guaranteed to be unique within a table and always incrementing. (Don't care about sequential, as long as it always increments.) From your description, it's not only unique within a table but also within an entire database. Correct?
That's a good question, uniqueness within the database. I'd have to rely on Microsoft's documentation that rowversion "[is] a data type that exposes automatically generated, unique binary numbers within a database... The rowversion data type is just an incrementing number."

Charles Graham · Accepted Answer · 2011-06-28 19:35:59Z

Does it need to be an integer column? A timestamp column will provide you the functionality you are looking for out of the box.

Nicholas Carey · Accepted Answer · 2011-06-28 19:37:13Z

Columns with an identity property can't be updated. Once the column with an identity property on it has been assigned a value, either automatically, or with identity_insert on, it is an invariant value. Further the identity property may not be disabled or removed via alter column.

I believe what you want to look at is a SQL Server TIMESTAMP (now called rowversion in SQL Server 2008). It is fundamentally an auto-incrementing binary value. Each database has a unique rowversion counter. Each row insert/update in a table with a timestamp/rowversion column results in the counter being ticked up and the new value assigned to the inserted/modified row.

Collectives™ on Stack Overflow

TSQL Auto Increment on Update

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related