Sql-server – n underling difference between TIMESTAMP and ROWVERSION when creating columns with each

sql serverssdttimestamp

I'm having problems with SSDT detecting a change to a "TIMESTAMP" column where both the target column and model.xml column definition are of type "TIMESTAMP". When this happens, the following alter statement is generated which fails trying to alter a TIMESTAMP column:

ALTER TABLE [dbo].[MyTable] ALTER COLUMN [MyColumn] TIMESTAMP NOT NULL;

My guess is that target column was created as ROWVERSION rather than TIMESTAMP and this is tripping up SSDT as when I update the model.xml column def to "ROWVERSION" a difference is NOT detected and the ALTER statement is not generated.

I know that these two statements are functionally equivalent:

alter table [MyTable] add [NewColumn] TIMESTAMP not null
alter table [MyTable] add [NewColumn] ROWVERSION not null

and that ROWVERSION is a alias for TIMESTAMP..

However, when enquiring on the column type, both alter statements result in the DATA_TYPE being reported as [timestamp]:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Table_Name = 'MyTable'

Is there an underlying "hidden" difference between columns created as ROWVERSION vs TIMESTAMP? – one that is being picked up by the Diff engine in SSDT?

Thanks in advance for any help!

Best Answer

I can't reproduce this on SQL Server 2017 or 2019 with Visual Studio 2019, so it would be interesting to know:

  • what version of sqlpackage.exe are you using?
  • what version of Visual Studio and SSDT are you using?
  • what version of SQL Server are you targeting?
  • what is the actual table definition in SSDT?
  • what options are you specifying when publishing?

Despite not being able to repro the issue, I do have some suggestions.

Whenever SSDT is doing some repetitive or redundant like this, I try to make sure that my definition in the model (SSDT project) matches the target, as sometimes things get transformed prior to being stored in SQL Server's metadata.

As you noted, the metadata is out of sync in this case. The column is stored as timestamp regardless of whether you specify rowversion or timestamp in the CREATE TABLE statement.

However, when enquiring on the column type, both alter statements result in the DATA_TYPE being reported as [timestamp]:

This behavior is related to these types being data type synonyms. Synonyms don't leave any trace that one was used in a DDL statement:

However, after the object is created, the synonyms have no visibility. When the object is created, the object is assigned the base data type that is associated with the synonym. There's no record that the synonym was specified in the statement that created the object.

What you're reporting is backwards from what I would expect, but it's possible that SSDT is trying to enforce this deprecation warning from the rowversion docs page:

The timestamp syntax is deprecated. This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

I'd suggest you update the SSDT project to use rowversion to avoid this problem.

Related Question