Incorrect schema change to be ignore in the output script

[azerios]

• SqlPackage or DacFx Version: 170.3.93
• .NET Framework (Windows-only) or .NET Core: .NET Core 10
• Environment (local platform and source/target platforms):

Steps to Reproduce:

1. Having a script inside a dacpac as

CREATE TABLE [dbo].[ANR] (
[ID] INT IDENTITY (1, 1) NOT NULL,
[CANumberID] INT NOT NULL,
[PANumberID] INT NOT NULL,
CONSTRAINT [PK_ANR] PRIMARY KEY ([ID] ASC),
CONSTRAINT [UQ_ANR_CANumberID_PANumberID] UNIQUE NONCLUSTERED ([CANumberID] ASC, [PANumberID] ASC),
CONSTRAINT [FK_ANR_AN_CANumberID] FOREIGN KEY ([CANumberID]) REFERENCES [dbo].[AN] ([Id]),
CONSTRAINT [FK_ANR_AN_PANumberID] FOREIGN KEY ([PANumberID]) REFERENCES [dbo].[AN] ([Id])
)

2. Trying to run the compare process as

var source = new SchemaCompareDatabaseEndpoint(sourceConnectionString);
var target = new SchemaCompareDatabaseEndpoint(destConnectionString);
var comparison = new SchemaComparison(source, target);
var result = comparison.Compare();

3. The output script generated is

/*
Deployment script for XXXXXXXXXX
This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
/
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
GO
:setvar DatabaseName "XXXXXXXXXX"
:setvar DefaultFilePrefix "XXXXXXXXXX"
:setvar DefaultDataPath "d:\MSSQL11.SQLSERVER\MSSQL\DATA"
:setvar DefaultLogPath "d:\MSSQL11.SQLSERVER\MSSQL\DATA"
GO
:on error exit
GO
/
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF;
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
BEGIN
PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
SET NOEXEC ON;
END
GO
USE [$(DatabaseName)];
GO
PRINT N'Dropping Foreign Key [dbo].[FK_ANR_AN_CANumberID]...';
GO
ALTER TABLE [dbo].[ANR] DROP CONSTRAINT [FK_ANR_AN_CANumberID];
GO
PRINT N'Dropping Foreign Key [dbo].[FK_ANR_AN_PANumberID]...';
GO
ALTER TABLE [dbo].[ANR] DROP CONSTRAINT [FK_ANR_AN_PANumberID];
GO
PRINT N'Starting rebuilding table [dbo].[ANR]...';
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_ANR] (
[ID] INT IDENTITY (1, 1) NOT NULL,
[CANumberID] INT NOT NULL,
[PANumberID] INT NOT NULL,
CONSTRAINT [tmp_ms_xx_constraint_UQ_ANR_CANumberID_PANumberID1] UNIQUE NONCLUSTERED ([CANumberID] ASC, [PANumberID] ASC),
CONSTRAINT [tmp_ms_xx_constraint_PK_ANR1] PRIMARY KEY CLUSTERED ([ID] ASC)
);
IF EXISTS (SELECT TOP 1 1
FROM [dbo].[ANR])
BEGIN
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_ANR] ON;
INSERT INTO [dbo].[tmp_ms_xx_ANR] ([ID], [CANumberID], [PANumberID])
SELECT [ID],
[CANumberID],
[PANumberID]
FROM [dbo].[ANR]
ORDER BY [ID] ASC;
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_ANR] OFF;
END
DROP TABLE [dbo].[ANR];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_ANRelation]', N'ANR';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_UQ_ANR_CANumberID_PANumberID1]', N'UQ_ANR_CANumberID_PANumberID', N'OBJECT';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_ANR1]', N'PK_ANR', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
PRINT N'Creating Foreign Key [dbo].[FK_ANR_AN_CANumberID]...';
GO
ALTER TABLE [dbo].[ANR] WITH NOCHECK
ADD CONSTRAINT [FK_ANR_AN_CANumberID] FOREIGN KEY ([CANumberID]) REFERENCES [dbo].[AN] ([Id]);
GO
PRINT N'Creating Foreign Key [dbo].[FK_ANR_AN_PANumberID]...';
GO
ALTER TABLE [dbo].[ANR] WITH NOCHECK
ADD CONSTRAINT [FK_ANR_AN_PANumberID] FOREIGN KEY ([PANumberID]) REFERENCES [dbo].[AN] ([Id]);
GO
USE [$(DatabaseName)];
GO
ALTER TABLE [dbo].[ANR] WITH CHECK CHECK CONSTRAINT [FK_ANR_AN_CANumberID];
ALTER TABLE [dbo].[ANR] WITH CHECK CHECK CONSTRAINT [FK_ANR_AN_PANumberID];
GO
PRINT N'Update complete.';
GO

Did this occur in prior versions? If not - which version(s) did it work in?

Why the DacFx tool is finding a schema change for this table ?

(DacFx/SqlPackage/SSMS/Azure Data Studio)

[dzsquared]

Just confirming because I think I'm missing a few steps from your process, you mention that the table script is in a dacpac - do you deploy that dacpac to the source and target endpoints before running schema compare?

[azerios]

The dacpac is deployed when the source change, regarding the target is the referential database server linked to my application.

So at each release we compare all changes, using the last backup available of the previous release and compare against the source database

If I run

-- Replace 'YourTableName' with the table you want to inspect
DECLARE @TableName SYSNAME = 'ANR';

-- Main query to get columns, PK, FK, and constraints
SELECT
c.COLUMN_NAME,
c.DATA_TYPE,
c.CHARACTER_MAXIMUM_LENGTH,
c.IS_NULLABLE,
c.COLUMN_DEFAULT,
pk.CONSTRAINT_NAME AS PrimaryKey,
fk.CONSTRAINT_NAME AS ForeignKey,
fk.REFERENCED_TABLE AS ForeignKeyReferencedTable,
fk.REFERENCED_COLUMN AS ForeignKeyReferencedColumn,
chk.CONSTRAINT_NAME AS CheckConstraint,
defc.CONSTRAINT_NAME AS DefaultConstraint
FROM INFORMATION_SCHEMA.COLUMNS c
-- Primary Keys
LEFT JOIN (
SELECT ku.TABLE_NAME, ku.COLUMN_NAME, tc.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
ON tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
) pk ON c.TABLE_NAME = pk.TABLE_NAME AND c.COLUMN_NAME = pk.COLUMN_NAME
-- Foreign Keys
LEFT JOIN (
SELECT
cu.TABLE_NAME,
cu.COLUMN_NAME,
rc.CONSTRAINT_NAME,
pk.TABLE_NAME AS REFERENCED_TABLE,
pk.COLUMN_NAME AS REFERENCED_COLUMN
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
ON rc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
INNER JOIN (
SELECT ku.TABLE_NAME, ku.COLUMN_NAME, tc.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
ON tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
) pk ON rc.UNIQUE_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
) fk ON c.TABLE_NAME = fk.TABLE_NAME AND c.COLUMN_NAME = fk.COLUMN_NAME
-- Check Constraints
LEFT JOIN (
SELECT cc.TABLE_NAME, cc.COLUMN_NAME, tc.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cc
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON cc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'CHECK'
) chk ON c.TABLE_NAME = chk.TABLE_NAME AND c.COLUMN_NAME = chk.COLUMN_NAME
-- Default Constraints
LEFT JOIN (
SELECT cc.TABLE_NAME, cc.COLUMN_NAME, tc.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cc
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON cc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'DEFAULT'
) defc ON c.TABLE_NAME = defc.TABLE_NAME AND c.COLUMN_NAME = defc.COLUMN_NAME
WHERE c.TABLE_NAME = @TableName
ORDER BY c.ORDINAL_POSITION;

Inside both database and the results are this order in the source database
ID
CANumberID
PANumberID
And this order in the target database
CANumberID
PANumberID
ID

So I confirm that the order is not ignored, and it should be ignore to consider this as difference and trigger a rebuild