SQL Server practicalities: the "rowversion" and "timestamp" data types in SQL Server Management Studio

In SQL Server databases, keeping track of data changes is often paramount. For example, if you ever need to replicate or synchronize data, knowing which rows have changed and which haven't been is key to success.

Now, the traditional method of handling this is to have fields like "CreatedAt", "ModifiedAt" and "DeletedAt", and store date values (clock time) in those fields. But, this requires manual work and/or code to keep these values up-to-date.

SQL Server supports a handy data type specifically for this: called "rowversion". This type defines an eight-byte binary value, usually as VARBINARY(8), which in turn can be read as (or cast into) a "bigint" data type.

Now, traditionally in SQL Server, to use this data type, you had used the type name "timestamp". The problem with this name is that it is the same name used in the SQL ISO Standard for something else than in SQL Server. As a result, the "timestamp" type has been deprecated since SQL Server version 2008, and the "rowversion" data type should be used instead.

This leads to a slight problem in SQL Server Management Studio (SSMS): if you try to use the "rowversion" data type when designing new tables, you will be greeted with the error message "Invalid data type".

The essential point: in practice – even with the latest SSMS versions – you cannot use the "rowversion" data type yet. Instead, you must still use "timestamp", even if it is marked as deprecated.

Hopefully, SSMS will start to support the new syntax. In the mean time, use the "ALTER TABLE.. ADD" SQL command to add tables with proper "rowversion" column type.

Hope this helps!