We are using the IsVersion property on the ColumnAttribute on a property in a LINQ to SQL class for optimistic concurrency checks. What should the column definition be in the database?
Currently we are using
version_number int NOT NULL IDENTITY (1, 1)
Do we need Identity? Can we get LINQ to SQL to update the version number for us? The only issue with Identity is that every row has a different number. We'd like to see the number increment by 1 when the row is updated.
You should use a Timestamp data type in your database tables. This will translate to a System.Data.Linq.Binary type in your entities.
With the Timestamp data type, SQL Server will automatically create and increment this value for you.
The "version" of a row should be updated every time the row gets modified - the unique ID of a row definitely does not qualify for that! (this typically is set once and never changes).
What you're looking for is a TIMESTAMP or ROWVERSION in SQL Server - it used to be called TIMESTAMP, but since it's really just a 8-byte binary "counter" and has nothing to do with time and/or date (other than the fact it's monotonically increasing over time), the SQL Server team will be calling it ROWVERSION from now on.
This is a data type that gets updated by SQL Server internally - you cannot set or insert a value into such a field yourself. It's guaranteed to change every time the row changes, so you can use it to detect something has changed in your data.
See the MSDN docs on rowversion or read the Understanding TIMESTAMP (ROWVERSION) in SQL Server article on ASP Alliance.
Alternatively you can use a real timestamp if the data does not change too often (I do that a lot in financial applications, but I can guarantee different timestamps), or a simple counting column.