I avoided going into this subject until when I will be deploying the new SQL Server database, I'm not understanding why the timestamp data type is required, can someone educate me on this one. All the tables I have do not have this field inserted yet I left it as the last item. Could it be it has something to do with record locking?
Solved Timestamps data type why are they required in SQL Server (1 Viewer)
- Thread starter nector
- Start date
This makes it easier to check during the update process whether the data set has been changed by other users in the meantime.
Take a look at the process in the profiler.
Update with timestamp: PK + timestamp are checked
Without timestamp: PK and all unchanged data fields are checked. Problem: As soon as "imprecise" data types like float occur, this check can fail.
Timestamp does not have to be in every table.
For tables that are only updatetd per SQL statement or only needed for selects, the TS is not necessary.
But as soon as data is changed via Access forms (with ODBC linked table data source) and float (I think also datetime) data fields are included, the TS helps.
Take a look at the process in the profiler.
Update with timestamp: PK + timestamp are checked
Without timestamp: PK and all unchanged data fields are checked. Problem: As soon as "imprecise" data types like float occur, this check can fail.
Timestamp does not have to be in every table.
For tables that are only updatetd per SQL statement or only needed for selects, the TS is not necessary.
But as soon as data is changed via Access forms (with ODBC linked table data source) and float (I think also datetime) data fields are included, the TS helps.
GPGeorge
George Hepworth
- Local time
- Yesterday, 20:18
- Joined
- Nov 25, 2004
- Messages
- 1,981
The TimeStamp, or RowVersion, field is not a time. I think the term "RowVersion" is preferred because it better reflects that, but TimeStamp is deeply ingrained in its history and not easily updated.
It's a value that is updated whenever a record is changed so that the checking Josef describes can work.
It is not only useful when a table has an imprecise data type, it is also important when tables include bit fields.
It's a value that is updated whenever a record is changed so that the checking Josef describes can work.
It is not only useful when a table has an imprecise data type, it is also important when tables include bit fields.
- Local time
- Yesterday, 23:18
- Joined
- Feb 19, 2002
- Messages
- 43,466
Having the RowVersion also makes updates much more efficient. The database engine does not have to evaluate every column in the table, it only has to look at the RowVersion for the PK to know if some other update has been made between when the row was fetched and when the user tried to update it. So you retrieve the first 10 columns plus the RV for PK 2828 to populate the form. Meanwhile someone updates the 12th field which wasn't retrieved by this form, That changes the value of the RV. When you save your changes to the first 10 columns, Access compares the current RV with the one retrieved originally. If they are the same, it is safe to update the data. If they are different. Someone else changed something while you were viewing PK 2828 and so it is not safe to update the data.
