Practicing more on the database - Rowversion / Timestamp data type

The Transact-SQL timestamp data type is not what it looks. It's not some sort of datetime data type, it's a rowversion. Each database has a counter that is incremented for each insert or update operation on a table that has a rowversion / timestamp column. Rowversion / timestamp is generally used as a mechanism for version-stamping table rows. The rowversion is unique for one entire database.

What about rowversion or timestamp?
Timestamp is a somewhat confusing name to use. Because in SQL-2003 the timestamp data type is exactly what we know as the datetime data type in T-SQL. So I prefer to use the synonym rowversion.

What about journaling these values?
In the past, I talked about triggers for journaling your data operations. In the specific example of journaling, you have to handle a rowversion column different. Because of the characteristics of a rowversion column you cannot use this datatype to journal the values deleted and inserted into a table. However the nonnullable rowversion column is semantically equivalent to a binary(8) column (a nullable rowversion is equivalent to a varbinary(8)), so you can choose to copy the inserted and deleted data from a rowversion column into a binary(8) column.

Versioning an entire entity
As mentioned a rowversion column can be used for versioning a row in a table. But you can look further. It can also be used for versioning an entire entity. The only thing you need to check is the version column in any of the entity's rows. If one is changed act like the entire entity is changed.

I hope you have learned something about this SQL Server feature that was unknown to me for a long time. At least I've learned something new on SQL Server.

Gravatar