SQLServer diff - DATETIME2 not being handled correctly
Description
Environment
windows / sql server
Activity
Show:
Nathan Voxland June 23, 2015 at 4:23 PM
Fixed datetime2 portion of the original issue in 3.4.0
Nathan Voxland June 23, 2015 at 4:23 PM
Split TIMESTAMP issue out into https://liquibase.jira.com/browse/CORE-2401#icft=CORE-2401
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)