Adding Row Version to SQL Server table I have a table in a database with three columns: ID, Key and Value.  Everytime I update the record, I have a trigger that sends these updates to another table and creates a new record in it, who's schema is this:

RowID (new ID field for this table, identity(1,1) column)
ID (from source table)
Key (from source table)
Value (from source table)
Version (integer)


So anytime the first table record's changed, it creates a new record in the second with the table everytime.

For every record with a matching ID, I want to add a unique incremental version number.  If a record with ID of 1 is entered into this table 5 times, those records should have version numbers of 1, 2, 3, 4, and 5.  Is the way to accomplish this through a subquery for existing version numbers, or setting up a partition, or something else?

Thanks.
In such cases I am storing version numbers, and using referential integrity to make sure version numbers begin with 1 and have no gaps. Because I use constraints, I am sure my solution works in high concurrency situations. I have described this approach in this article