none
Schema Compare falsly reports differences in computed columns RRS feed

  • Question

  • Using SSMS 2014 create a blank database with a single table with a computed column as below

    CREATE TABLE [dbo].[Table_1] (
        [Col1] INT NOT NULL,
        [Col2] INT NOT NULL,
        [Col3] AS  ([Col1]+[Col2]) PERSISTED,
        CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ([Col1] ASC)
    );
    GO

    In Visual Studio 2012 create a blank Database project. Run Schema Compare from within Visual Studio with the database as the source and the database project as the target.

    After the target is updated, Schema Compare still detects the computed columns as different but does not highlight any differences.

    This issue doesn't seem to arise if VS is the source and SQL Server the target.

    Edit: I've since discovered that Schema Compare shows the source database sets IsPersistedNullable = true on the computed column, but IsPersistedNullable = null in the target project. However IsPersistedNullable is set to true in the properties of the computed column in the VS project.

    IsPersistedNullable can be set to True or False (but not null) in VS but does not appear to be available as a property in SSMS. How can this be fixed so that the schema can be properly synchronised?

    • Edited by FSL AU Saturday, July 26, 2014 7:10 AM
    Saturday, July 26, 2014 5:12 AM

Answers

All replies

  • This looks like defect in the model comparison logic. In this case they should compare equal. Can you please file a connect bug on this so we can better track the issue. Please find the instructions to file a connect bug here.

    Thanks

    Lonny

    Monday, September 29, 2014 5:57 AM
  • I can confirm this bug using the latest VS2014 Update 4 downloaded yesterday.  My production DB and project consistently differ on IsPersistedNullable columns in 5 different tables.

    Updating the local project to match the DB does not fix this issue.

    Tuesday, November 18, 2014 1:47 PM
  • A connect bug was raised for this issue over a year ago and closed. Scheme Compare has become effectively unusable on a database with many computed columns, as it has becomes so hard to tell what has actually changed.

    Thursday, December 4, 2014 5:32 PM
  • As suggested in [https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1e490f6e-384d-48a4-b2b4-738bfa67484b/difference-in-computed-columns?forum=ssdt]

    OK, I looked at the code, and we have a bug when dealing with the NULLability of persisted columns. The only way to make it consistent right now is to define the persisted column as NOT NULL, once deployed the difference will not show. If the persisted column is NULL you will run in to a problem comparing a DACPAC or a BD with the project.


    -GertD @ www.sqlproj.com

    That worked for me on SQL Server 2012 Enterprise and SSDT + BI on VS2012 Pro

    Cheerio,

    lb:þ


    Thursday, February 5, 2015 4:54 PM