MSSQL handling timestamp according to sql standard, not sqlserver usage
Description
Environment
mssql
Activity
Andrew Hill July 2, 2015 at 1:05 AM
also "The timestamp syntax is deprecated. This feature will 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."
Andrew Hill July 2, 2015 at 1:03 AM
suggestion for current version: when creating a changelog/... for sql server, emit the token "rowVersion" for that datatype, rather than timestamp? ; this should be detectable via the metadata tables which are already in use for detecting row, and handled in a resonably backwards compatable manner.
"timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonyms. In DDL statements, use rowversion instead of timestamp"
https://msdn.microsoft.com/en-au/library/ms182776(v=sql.100).aspx (docs for sql server 2008 ; all newer versions are similar, 2005 only has "timestamp" without the alias.
Nathan Voxland June 24, 2015 at 5:42 PM
Added a system property flag of -Dliquibase.convertDataTypes=false which will preserve the TIMESTAMP type.
Nathan Voxland June 23, 2015 at 4:25 PM
Timestamp has a particular meaning in sql which is not followed by sqlserver (see https://technet.microsoft.com/en-us/library/aa260631(v=sql.80).aspx). So when diff a sqlserver database, even if we preserve "timestamp" into the changelog file, when we run update it will say "they asked for a timestamp, which means mssql datatime in sqlserver". Changing this behavior has unknown ramifications and is part of what I'd like to do with 4.0 but doesn't really fit into a 3.4 release.
Split issue from
SqlServer timestamps/rowversions are not handled correctly under 3.1.1
if you have a column of type "TIMESTAMP" (synonym: rowversion, http://msdn.microsoft.com/en-au/library/ms182776.aspx )
script table as create produces: sql server:
CREATE TABLE dbo.testTable(
TestLastUpdateValue timestamp NOT NULL ,
TestDate dateTime2(3)
)
and you do a diff between this db and a blank db you get
<changeSet author="hilland (generated)" id="1402553718353-105">
<createTable tableName="testTable">
<column name="TestLastUpdateValue" type="DATETIME">
<constraints nullable="false"/>
</column>
<column name="TestDate" type="datetime"/>
</createTable>
</changeSet>
updateSQL then promotes this to
CREATE TABLE dbo.testTable(
TestLastUpdateValue datetime NOT NULL ,
TestDate dateTime
)
note both columns changed data type.
which in almost all cases is not compatible with the original table. dateTime has limited precision, rounding errors, and can not handle years before a specific date.
timestamp is automatically populated on change, and monotonically increasing. (note, rowversion/timestamp does not store time, so data from a timestamp column can not be stored in a dateTime column)