DacFx shows permanent diff on table constraints and/or computed columns if some specific expressions used

[IVNSTN]

trafficstars

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

DacFx treats some expressions in a strange manner: it shows permanent diff on constraints and computed columns if such expression or function used. This diff cannot be fixed with redeployment or whatever unless column/constraint definition is rewritten without such expression/function usage.

Troublesome functions and expressions	Alternative which fixes diff
LEFT, RIGHT	SUBSTRING
DAY, MONTH, YEAR	DATEPART(<DAY|MONTH|YEAR>, ...)
CAST	CONVERT
IIF	CASE WHEN ... THEN ... END
<col> IN (<value1>,.. <valueN>)	<col> = <value1> OR ... <col> = <valueN>
<col> BETWEEN <value1> AND <value2>	<col> >= <value1> AND <col> <= <value2>

Steps to Reproduce:

1. Create new sqlproj, add table scripts from below to this project
2. Deploy this project to new database
3. Perform Schema Compare or generate deploy script via Publish menu item in VS or sqlpackage.exe
4. See that just created computed columns and constraints from dbo.trouble_table "have discrepancy" and are being dropped and recreated in publish script
5. Apply this publish script
6. Repeat item 3, see the same discrepancy: same constraints and computed columns from dbo.trouble_table are "different" again
7. Repeat 5, get back to 3 and see the very same diff
8. Whereas dbo.fine_table is fine, no diff shown

Table scripts:

CREATE TABLE dbo.fine_table
(
    fine_table_id    INT      NOT NULL PRIMARY KEY
    , some_bool_char CHAR(1)  NULL
    , dt             DATETIME NULL
    , fine_col_01    AS DATEPART(YEAR, dt)
    , fine_col_02    AS CONVERT(TIME, dt)
    , fine_col_03    AS CASE WHEN some_bool_char = 'Y' OR some_bool_char = 'N' THEN 1 ELSE 0 END
    , fine_col_04    AS CASE WHEN some_bool_char = 'Y' THEN 1 ELSE 0 END
    , fine_col_05    AS CASE WHEN fine_table_id >= 1 AND fine_table_id <= 100 THEN 300 ELSE 0 END
    , fine_col_06    AS SUBSTRING(some_bool_char, 1, 1)
    , CONSTRAINT fine_check_01 CHECK (DATEPART(YEAR, dt) >= '2000')
    , CONSTRAINT fine_check_02 CHECK (CONVERT(TIME, dt) <> '00:00')
    , CONSTRAINT fine_check_03 CHECK (some_bool_char = 'Y' OR some_bool_char = 'N')
    , CONSTRAINT fine_check_04 CHECK (CASE WHEN some_bool_char = 'Y' THEN 1 ELSE 0 END = 1)
    , CONSTRAINT fine_check_05 CHECK (fine_table_id >= 1 AND fine_table_id <= 100)
    , CONSTRAINT fine_check_06 CHECK (SUBSTRING(some_bool_char, 1, 1) <> '')
);
GO

CREATE TABLE dbo.trouble_table
(
    trouble_table_id INT      NOT NULL PRIMARY KEY
    , some_bool_char CHAR(1)  NULL
    , dt             DATETIME NULL
    , trouble_col_01 AS YEAR(dt)                                                         -- YEAR used
    , trouble_col_02 AS CAST(dt AS TIME)                                                 -- CAST used 
    , trouble_col_03 AS CASE WHEN some_bool_char IN ('Y', 'N') THEN 1 ELSE 0 END         -- IN used
    , trouble_col_04 AS IIF(some_bool_char = 'Y', 1, 0)                                  -- IIF used
    , trouble_col_05 AS CASE WHEN trouble_table_id BETWEEN 1 AND 100 THEN 300 ELSE 0 END -- BETWEEN used
    , trouble_col_06 AS LEFT(some_bool_char, 1)                                          -- LEFT used
    , CONSTRAINT trouble_check_01 CHECK (YEAR(dt) >= '2000')                             -- YEAR used
    , CONSTRAINT trouble_check_02 CHECK (CAST(dt AS TIME) <> '00:00')                    -- CAST used
    , CONSTRAINT trouble_check_03 CHECK (some_bool_char IN ('Y', 'N'))                   -- IN used
    , CONSTRAINT trouble_check_04 CHECK (IIF(some_bool_char = 'Y', 1, 0) = 1)            -- IIF used
    , CONSTRAINT trouble_check_05 CHECK (trouble_table_id BETWEEN 1 AND 100)             -- BETWEEN used
    , CONSTRAINT trouble_check_06 CHECK (LEFT(some_bool_char, 1) <> '')                  -- LEFT used
);
GO

Complex expressions including mentioned expressions/functions lead to the same permanent discrepancy.

Schema compare shows diff right after the first table creation and keeps showing no matter how many times you redeploy the project: Note, on the right side (DB instance) it shows not the code defined in the project but something "reverse engineered" from what is stored in DB metadata. Some functions/expressions look like expanded into simpler expressions, some look replaced as if they were shortcuts. If you go to DB and run sp_help then you can see the same things:

However there are no such replacements in publish scripts (no matter - the initial table creation or a publish script supposed to eliminate the discrepancy after Schema Compare). So I guess it's not a real bug but some misunderstanding between DacFx script generation and how SqlServer actually stores constraint and computed column definitions. Still, this is very uncomfortable and time consuming to see unexpected diff between project and prod all the time. One needs to spend some time to realize that diff is a fake.

Publish script generated after schema compare

/*
Deployment script for database1

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 "database1"
:setvar DefaultFilePrefix "database1"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\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 Check Constraint [dbo].[trouble_check_01]...';


GO
ALTER TABLE [dbo].[trouble_table] DROP CONSTRAINT [trouble_check_01];


GO
PRINT N'Dropping Check Constraint [dbo].[trouble_check_02]...';


GO
ALTER TABLE [dbo].[trouble_table] DROP CONSTRAINT [trouble_check_02];


GO
PRINT N'Dropping Check Constraint [dbo].[trouble_check_03]...';


GO
ALTER TABLE [dbo].[trouble_table] DROP CONSTRAINT [trouble_check_03];


GO
PRINT N'Dropping Check Constraint [dbo].[trouble_check_04]...';


GO
ALTER TABLE [dbo].[trouble_table] DROP CONSTRAINT [trouble_check_04];


GO
PRINT N'Dropping Check Constraint [dbo].[trouble_check_05]...';


GO
ALTER TABLE [dbo].[trouble_table] DROP CONSTRAINT [trouble_check_05];


GO
PRINT N'Dropping Check Constraint [dbo].[trouble_check_06]...';


GO
ALTER TABLE [dbo].[trouble_table] DROP CONSTRAINT [trouble_check_06];


GO
PRINT N'Altering Table [dbo].[trouble_table]...';


GO
ALTER TABLE [dbo].[trouble_table] DROP COLUMN [trouble_col_06], COLUMN [trouble_col_05], COLUMN [trouble_col_04], COLUMN [trouble_col_03], COLUMN [trouble_col_02], COLUMN [trouble_col_01];


GO
ALTER TABLE [dbo].[trouble_table]
    ADD [trouble_col_01] AS YEAR(dt),
        [trouble_col_02] AS CAST (dt AS TIME),
        [trouble_col_03] AS CASE WHEN some_bool_char IN ('Y', 'N') THEN 1 ELSE 0 END,
        [trouble_col_04] AS IIF (some_bool_char = 'Y', 1, 0),
        [trouble_col_05] AS CASE WHEN trouble_table_id BETWEEN 1 AND 100 THEN 300 ELSE 0 END,
        [trouble_col_06] AS LEFT(some_bool_char, 1);


GO
PRINT N'Creating Check Constraint [dbo].[trouble_check_01]...';


GO
ALTER TABLE [dbo].[trouble_table] WITH NOCHECK
    ADD CONSTRAINT [trouble_check_01] CHECK (YEAR(dt) >= '2000');


GO
PRINT N'Creating Check Constraint [dbo].[trouble_check_02]...';


GO
ALTER TABLE [dbo].[trouble_table] WITH NOCHECK
    ADD CONSTRAINT [trouble_check_02] CHECK (CAST(dt AS TIME) <> '00:00');


GO
PRINT N'Creating Check Constraint [dbo].[trouble_check_03]...';


GO
ALTER TABLE [dbo].[trouble_table] WITH NOCHECK
    ADD CONSTRAINT [trouble_check_03] CHECK (some_bool_char IN ('Y', 'N'));


GO
PRINT N'Creating Check Constraint [dbo].[trouble_check_04]...';


GO
ALTER TABLE [dbo].[trouble_table] WITH NOCHECK
    ADD CONSTRAINT [trouble_check_04] CHECK (IIF(some_bool_char = 'Y', 1, 0) = 1);


GO
PRINT N'Creating Check Constraint [dbo].[trouble_check_05]...';


GO
ALTER TABLE [dbo].[trouble_table] WITH NOCHECK
    ADD CONSTRAINT [trouble_check_05] CHECK (trouble_table_id BETWEEN 1 AND 100);


GO
PRINT N'Creating Check Constraint [dbo].[trouble_check_06]...';


GO
ALTER TABLE [dbo].[trouble_table] WITH NOCHECK
    ADD CONSTRAINT [trouble_check_06] CHECK (LEFT(some_bool_char, 1) <> '');


GO
PRINT N'Checking existing data against newly created constraints';


GO
USE [$(DatabaseName)];


GO
ALTER TABLE [dbo].[trouble_table] WITH CHECK CHECK CONSTRAINT [trouble_check_01];

ALTER TABLE [dbo].[trouble_table] WITH CHECK CHECK CONSTRAINT [trouble_check_02];

ALTER TABLE [dbo].[trouble_table] WITH CHECK CHECK CONSTRAINT [trouble_check_03];

ALTER TABLE [dbo].[trouble_table] WITH CHECK CHECK CONSTRAINT [trouble_check_04];

ALTER TABLE [dbo].[trouble_table] WITH CHECK CHECK CONSTRAINT [trouble_check_05];

ALTER TABLE [dbo].[trouble_table] WITH CHECK CHECK CONSTRAINT [trouble_check_06];


GO
PRINT N'Update complete.';

GO

Did this occur in prior versions? If not - which version(s) did it work in? It worked the same in prior versions.

As a "workaround" we have special rules in our custom linter which prevent a developer from using mentioned expressions and functions in constraints and computed columns.

(DacFx/SqlPackage)

[IVNSTN]

See also #385

[IVNSTN]

Note, this problem is also reproducible on user-defined table types.

[daiplusplus]

This also happens with:

• the INCLUDE() and WHERE clauses in CREATE INDEX statements.
• INDEX declarations within a CREATE TABLE statmenet.
• DEFAULT constraints.
• ...basically anywhere you have a T-SQL expression that gets serialized by SQL Server without preserving its original script text - so I'm sure there's probably a few more cases I'm not considering.