What is the preferred SQL server column definition used for the LINQ to SQL Version property?

Question

We use the IsVersion property in ColumnAttribute for the property in the LINQ to SQL class for optimistic concurrency checks. What should be the definition of a column in a database?

We are currently using

 version_number int NOT NULL IDENTITY (1, 1) 

Do we need identification? Can we get LINQ to SQL to update the version number for us? The only problem with Identity is that each line has a different number. We want the number to increase by 1 when updating the row.

Answer 1

The "version" of the line should be updated every time the line changes - the unique identifier of the line is definitely not suitable for this! (this is usually set once and never changes).

What you are looking for is TIMESTAMP or ROWVERSION in SQL Server - it was called TIMESTAMP , but since it is really just an 8-byte binary “counter” and has nothing to do with time and / or date (except that it is monotonically increasing from over time), the SQL Server team will call it ROWVERSION from now on.

This is the data type that SQL Server internally updates — you cannot set or paste a value in such a field yourself. It is guaranteed to change every time a row changes, so you can use it to detect that something has changed in your data.

See MSDN docs in rowversion or read Understanding TIMESTAMP (ROWVERSION) in SQL Server about the ASP Alliance.

Answer 2

Database tables should use the Timestamp data type. This will result in the use of the System.Data.Linq.Binary type in your entities.

With the data type Timestamp, SQL Server will automatically create and increase this value for you.

Answer 3

Alternatively, you can use a real timestamp if the data does not change too often (I do this in financial applications, but I can guarantee different timestamps) or a simple counting column.