Why is the "rowversion" data type replaced with "timestamp" in the comparison result?
Shaggy
Posts: 16 Bronze 1
Would also like to know why the binding to the partition scheme isn't maintained the SQL compare result.
Update >> SQL Compare appears to ignore partition functions & schemes.
Source Control << This is what is actually in Git.
Source Control from SQL Compare's perspective:
Target database from SQL Compare's perspective:
Update >> SQL Compare appears to ignore partition functions & schemes.
Source Control << This is what is actually in Git.
CREATE TABLE [AppData].[ShermanLog]
(
[ShermanLogId] [int] NOT NULL IDENTITY(-2147483648, 1),
[PartitionDate] [date] NOT NULL,
[CreatedOn] [datetime2] (3) NOT NULL CONSTRAINT [DF_ShermanLog_CreatedOn] DEFAULT (sysdatetime()),
[CreatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_ShermanLog_CreatedBy] DEFAULT (suser_sname()),
[UpdatedOn] [datetime2] (3) NOT NULL CONSTRAINT [DF_ShermanLog_UpdatedOn] DEFAULT (sysdatetime()),
[UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_ShermanLog_UpdatedBy] DEFAULT (suser_sname()),
[LoanId] [int] NOT NULL,
[ShermanLogTypeId] [int] NOT NULL,
[RequestXml] [xml] NULL,
[ResponseXml] [xml] NULL,
[IsSuccessful] [bit] NOT NULL,
[PartnerId] [smallint] NOT NULL,
[DataVersion] [rowversion] NOT NULL
) ON [ShermanLog_PS] ([PartitionDate])
Source Control from SQL Compare's perspective:
CREATE TABLE [AppData].[ShermanLog]
(
[ShermanLogId] [int] NOT NULL IDENTITY(-2147483648, 1),
[PartitionDate] [date] NOT NULL,
[CreatedOn] [datetime2] (3) NOT NULL CONSTRAINT [DF_ShermanLog_CreatedOn] DEFAULT (sysdatetime()),
[CreatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_ShermanLog_CreatedBy] DEFAULT (suser_sname()),
[UpdatedOn] [datetime2] (3) NOT NULL CONSTRAINT [DF_ShermanLog_UpdatedOn] DEFAULT (sysdatetime()),
[UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_ShermanLog_UpdatedBy] DEFAULT (suser_sname()),
[LoanId] [int] NOT NULL,
[ShermanLogTypeId] [int] NOT NULL,
[RequestXml] [xml] NULL,
[ResponseXml] [xml] NULL,
[IsSuccessful] [bit] NOT NULL,
[PartnerId] [smallint] NOT NULL,
[DataVersion] [timestamp] NOT NULL
)
Target database from SQL Compare's perspective:
CREATE TABLE [AppData].[ShermanLog]
(
[ShermanLogId] [int] NOT NULL IDENTITY(-2147483648, 1),
[PartitionDate] [date] NOT NULL,
[CreatedOn] [datetime2] (3) NOT NULL CONSTRAINT [DF_ShermanLog_CreatedOn] DEFAULT (sysdatetime()),
[CreatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_ShermanLog_CreatedBy] DEFAULT (suser_sname()),
[UpdatedOn] [datetime2] (3) NOT NULL CONSTRAINT [DF_ShermanLog_UpdatedOn] DEFAULT (sysdatetime()),
[UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_ShermanLog_UpdatedBy] DEFAULT (suser_sname()),
[LoanId] [int] NOT NULL,
[ShermanLogTypeId] [int] NOT NULL,
[RequestXml] [xml] NULL,
[ResponseXml] [xml] NULL,
[IsSuccessful] [bit] NOT NULL,
[PartnerId] [smallint] NOT NULL,
[DataVersion] [timestamp] NOT NULL
)
Tagged:
Best Answers
-
Alex B
Posts: 1,157 Diamond 4
Hi @Shaggy,
Are you filtering out Partition Function objects or could the object be filtered out by name? I've just created a partition function in my SQL2017 instance, using the latest SQL Source Control and it is showing up in the Commit tab for me to commit to source control.
Kind regards,
Alex -
Julia.Hayward
Posts: 16 Bronze 4
Just to clarify a little, it's SQL Server itself that automatically converts all mentions of [rowversion] to [timestamp] at the point where it parses DDL queries - if you create a table with a [rowversion] column and then ask SQL to script that table out, you'll see it has done the conversion automatically. And this query:SELECT * FROM sys.types WHERE name = N'rowversion'<span>;</span>
returns no rows. It's clearly an unsatisfactory situation that the automatic conversion is done *to* the deprecated one, and won't be resolved until a SQL is released where the conversion is reversed.
Tech Lead, SQL Toolbelt
Redgate Software -
Shaggy
Posts: 16 Bronze 1
Alex B >> Partition functions & schemes are not filtered out in my SQL Compare session. The functions & schemes do make it to source control. However, SQL Compare ignores differences between source control and a target database. As previously mentioned, I added a function to the database that didn't exist in the target. SQL Compare didn't detect the difference.
Julia >> Thanks for identifying the root cause. -
Alex B
Posts: 1,157 Diamond 4
Hi @Shaggy,
Apologies for my confusion - this is likely due to the default SQL Compare comparison Option to "Ignore filegroups, partition schemes and partition functions". I've just checked and the latest version of SQL Compare correctly compares these objects when this ignore option is disabled.
Kind regards,
Alex
Answers
For the [rowversion] vs [timestamp] this is because [timestamp] is a datatype synonym for [rowversion] - see this page for more information. So the two things are equivalent.
Having said that, the page indicates that [timestamp] is deprecated so I have raised SC-10217 regarding this since we should probably start scripting [rowversion] out as itself and possibly scripting [timestamp] out [rowversion] too (though making that decision for a user may not be appropriate).
For the first part of your statement, can you elaborate a bit on the following and give an example:
Update >> SQL Compare appears to ignore partition functions & schemes.
Alex
Have you visited our Help Center?
Thanks for the feedback regarding rowversion & timestamp. We are in the process of migration to SQL 2016. We plan to convert all timestamp columns to the rowversion type since MS deprecated timestamp.
Regarding partitioning, I conducted a test by introducing a partition function in the target database that didn't exist in source control. SOC didn't detect the drift from SOC.