Adding Row Version to SQL Server table
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