microsoft / microsoft/DacFx

Permanent Difference in Custom Table Type Computed Column

Open
#432 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug persistent-schema-differences
Dominant language
C#
Stars
460
Forks
29
Avg merge
4d 9h
Merged PRs (30d)
7

Description

  • SqlPackage or DacFx Version: 162.2.111.2
  • .NET Framework (Windows-only) or .NET Core: Both
  • Environment (local platform and source/target platforms): Microsoft SQL Server 2019 (RTM-CU12) (KB5004524) - 15.0.4153.1 (X64) Jul 19 2021 15:37:34 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 22631: ) (Hypervisor)

Steps to Reproduce:

  1. Create an empty sqlproj project. Can be old or new sqlproj type
  2. Add a custom table type with a computed column like so:
CREATE TYPE MyCustomTableType AS TABLE (
    NormalColumn            int,
    ComputedColumn  	AS NormalColumn
);
  1. Build and Publish the dacpac to a database
  2. Publish the database again to the same database
  3. Expected: No SQL commands are issued
  4. Actual: Table type is dropped and recreated
Dropping User-Defined Table Type [dbo].[MyCustomTableType]...
Creating User-Defined Table Type [dbo].[MyCustomTableType]...

Example project is attached here:
MyTestDatabase.zip

Doing a schema comparison in Azure Data Studio doesn't reveal any difference. However, doing a schema comparison in visual studio between the project and the database reveals that the isPersistedNullable properties on the computed columns are different.
image

Did this occur in prior versions? If not - which version(s) did it work in?
This issue was happening back in version 16.1.6374.0
(DacFx/SqlPackage/SSMS/Azure Data Studio)

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the attached MyTestDatabase.zip and reproduce the build and two successive publishes against SQL Server 2019. Compare the computed column's isPersistedNullable property in Visual Studio, and consider the issue resolved when the second publish emits no drop-and-recreate commands for the custom table type.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.