CODEWITHSUNDEEP
sqlsql-server-2008identityauto-increment
RMD
RMD

Reputation: 3253

TSQL Auto Increment on Update

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.

Upvotes: 3

Views: 3142

Answers (3)

Nicholas Carey
Nicholas Carey

Reputation: 74385

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.

Upvotes: 2

hardmath
hardmath

Reputation: 8823

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.

Upvotes: 4

Charles Graham
Charles Graham

Reputation: 1157

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

Upvotes: 2

Related Questions