Changing the length of an VARCHAR or NVARCHAR column that has explicit COLLATE defined that matches the database default COLLATION will trigger a data loss check in the script output #453
Description
- SqlPackage or DacFx Version:
<Sdk Name="Microsoft.Build.Sql" Version="0.1.15-preview" /> - .NET Framework (Windows-only) or .NET Core:
MSBuild version 17.3.4+a400405ba for .NET - Environment (local platform and source/target platforms): Local SQL 2022 running in docker for testing. This is the same behavior we are seeing on our Windows 2019 servers with SQL Server 2022 Enterprise Edition.
Steps to Reproduce:
Changing the length of an VARCHAR or NVARCHAR column that has explicit COLLATE defined that matches the database default COLLATION will trigger a data loss check in the script output.
Expected Behavior
A VARCHAR or NVARCHAR expansion should be a metadata only operation and should not trigger a data loss warning.
Database Project file (CollationTest.sqlproj)
Details
<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
<Sdk Name="Microsoft.Build.Sql" Version="0.1.15-preview" />
<PropertyGroup>
<Name>CollationTest</Name>
<DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
<ModelCollation>1033, CI</ModelCollation>
</PropertyGroup>
<ItemGroup>
<Build Remove="out\**" />
</ItemGroup>
</Project>
Table Definition (TestTable.sql)
Details
CREATE TABLE [dbo].[TestTable]
(
[TestTable_ID] [bigint] IDENTITY (1,1) NOT NULL ,
-- Test collation that is the same as the DB default
[SameCollation] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS ,
[SameCollationN] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS ,
-- Test collation that is different than the DB default
[DiffCollation] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CS_AS ,
[DiffCollationN] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CS_AS ,
CONSTRAINT [TestTable_PK] PRIMARY KEY CLUSTERED ([TestTable_ID] ASC) ON [PRIMARY]
)
GO
Publish Profile (publish.xml)
Details
<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="15.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<PropertyGroup>
<ProfileVersionNumber>1</ProfileVersionNumber>
<ScriptDatabaseOptions>false</ScriptDatabaseOptions>
<DoNotDropCredentials>true</DoNotDropCredentials>
<DoNotDropDatabaseRoles>true</DoNotDropDatabaseRoles>
<DoNotDropExtendedProperties>true</DoNotDropExtendedProperties>
<DoNotDropFilegroups>true</DoNotDropFilegroups>
<DoNotDropFiles>true</DoNotDropFiles>
<DoNotDropRoleMembership>true</DoNotDropRoleMembership>
<DoNotDropUsers>true</DoNotDropUsers>
<DropExtendedPropertiesNotInSource>false</DropExtendedPropertiesNotInSource>
<DropObjectsNotInSource>true</DropObjectsNotInSource>
<DoNotAlterChangeDataCaptureObjects>false</DoNotAlterChangeDataCaptureObjects>
<DoNotAlterReplicatedObjects>false</DoNotAlterReplicatedObjects>
<ExcludeObjectTypes>
Credentials;
DatabaseRoles;
ExtendedProperties;
RoleMembership;
Users;
Filegroups;
Files;
</ExcludeObjectTypes>
<IgnoreAuthorizer>true</IgnoreAuthorizer>
<IgnoreColumnOrder>true</IgnoreColumnOrder>
<IgnoreComments>true</IgnoreComments>
<IgnoreExtendedProperties>true</IgnoreExtendedProperties>
<IgnoreIdentitySeed>true</IgnoreIdentitySeed>
<IgnoreIncrement>true</IgnoreIncrement>
<IgnoreIndexOptions>true</IgnoreIndexOptions>
<IgnorePartitionSchemes>true</IgnorePartitionSchemes>
<IgnorePermissions>true</IgnorePermissions>
<IgnoreRoleMembership>true</IgnoreRoleMembership>
<IgnoreTableOptions>true</IgnoreTableOptions>
<IgnoreWithNocheckOnCheckConstraints>true</IgnoreWithNocheckOnCheckConstraints>
<IgnoreWithNocheckOnForeignKeys>true</IgnoreWithNocheckOnForeignKeys>
</PropertyGroup>
</Project>
Build command
dotnet build
Sqlpackage command
sqlpackage /Action:Script /DeployScriptPath:.\out\CollationTest.dacpac.script.sql /DeployReportPath:.\out\CollationTest.dacpac.report.xml /Profile:.\publish.xml /SourceFile:.\bin\Release\CollationTest.dacpac /TargetServerName:localhost /TargetDatabaseName:CollationTest /TargetTrustServerCertificate:true /p:VerifyDeployment=false /p:BlockOnPossibleDataLoss=true /TargetUser:sqluser /TargetPassword:sqlpass
Change the VARCHAR and NVARCHAR lengths from 64 to 65 to trigger a schema modification
Details
CREATE TABLE [dbo].[TestTable]
(
[TestTable_ID] [bigint] IDENTITY (1,1) NOT NULL ,
-- Test collation that is the same as the DB default
[SameCollation] [varchar] (65) COLLATE SQL_Latin1_General_CP1_CI_AS ,
[SameCollationN] [nvarchar](65) COLLATE SQL_Latin1_General_CP1_CI_AS ,
-- Test collation that is different than the DB default
[DiffCollation] [varchar] (65) COLLATE SQL_Latin1_General_CP1_CS_AS ,
[DiffCollationN] [nvarchar](65) COLLATE SQL_Latin1_General_CP1_CS_AS ,
CONSTRAINT [TestTable_PK] PRIMARY KEY CLUSTERED ([TestTable_ID] ASC) ON [PRIMARY]
)
GO
Rerun the build and sqlpackage commands as above and the SQL script output will add a data loss detection message and check into the script for the table. It only adds the message for the 2 columns that have collation matching the database default. It does not have that warning for the 2 columns that have a different collation.
Also to note there is no table rebuild in the SQL script output. It just does a simple ALTER COLUMN as I would expect. So the warning is incorrect.
Details
/*
The type for column SameCollation in table [dbo].[TestTable] is currently VARCHAR (64) NULL but is being changed to VARCHAR (65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL. Data loss could occur and deployment may fail if the column contains data that is incompatible with type VARCHAR (65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL.
The type for column SameCollationN in table [dbo].[TestTable] is currently NVARCHAR (64) NULL but is being changed to NVARCHAR (65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL. Data loss could occur and deployment may fail if the column contains data that is incompatible with type NVARCHAR (65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL.
*/
IF EXISTS (select top 1 1 from [dbo].[TestTable])
RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT
GO
PRINT N'Altering Table [dbo].[TestTable]...';
GO
ALTER TABLE [dbo].[TestTable] ALTER COLUMN [DiffCollation] VARCHAR (65) COLLATE SQL_Latin1_General_CP1_CS_AS NULL;
ALTER TABLE [dbo].[TestTable] ALTER COLUMN [DiffCollationN] NVARCHAR (65) COLLATE SQL_Latin1_General_CP1_CS_AS NULL;
ALTER TABLE [dbo].[TestTable] ALTER COLUMN [SameCollation] VARCHAR (65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL;
ALTER TABLE [dbo].[TestTable] ALTER COLUMN [SameCollationN] NVARCHAR (65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL;
Our workaround is to just remove the explicit COLLATE statements from all of the tables where it matches the database default. We understand the explicit COLLATE definition for columns that match the default collation is unnecessary, but this feels like maybe a bug to me.
Did this occur in prior versions? If not - which version(s) did it work in?
(DacFx/SqlPackage/SSMS/Azure Data Studio)