SQLServer diff - DATETIME2 not being handled correctly

Description

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)

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

Fixed

Details

Reporter

Fix versions

Affects versions

Priority

Created June 12, 2014 at 6:20 AM
Updated June 23, 2015 at 4:23 PM
Resolved June 23, 2015 at 4:23 PM

Flag notifications