Options

Treat empty strings as NULL results in comparison errors

wtaylorwtaylor Posts: 2
Hi,

I recently enabled the option "Treat empty strings as NULL". The results showed what seemed to be false differences.

For example, when comparing a column which contains a Y or an N value, many rows which have a Y on both sides of the comparison were highlighted as being different. When I double-click on an example of this, the pop-up Viewer window indicates that they are a match (there is no crossed-out red equal sign). Neither value contains trailing whitespace. The problem is pervasive and occurs on other column types where the values can independently be verified as identical.

This example was conducted in a SQL Server 2012 database. Both of the compared tables are in the same database instance. The column is defined as "Varchar(2), null" on both sides.

When I disable "Treat empty strings as NULL" the comparison behaves as expected.

Any help is appreciated!

Comments

  • Options
    I created a sample database and cannot reproduce the issue, so I'm at a loss to explain what you are seeing.
    Table in both databases:
    CREATE TABLE [dbo].[Table_1](
    	[ID] [int] NOT NULL,
    	[data] [varchar](2) NULL,
     CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    

    Data:
    [use database1]
    INSERT INTO table_1 (id,data) VALUES(1, 'Y ')
    INSERT INTO table_1 (id,data) VALUES(2, 'Y')
    INSERT INTO table_1 (id,data) VALUES(3, 'Y')
    INSERT INTO table_1 (id,data) VALUES(4, 'Y ')
    USE [database2]
    INSERT INTO table_1 (id,data) VALUES(1, 'Y ')
    INSERT INTO table_1 (id,data) VALUES(2, 'Y')
    INSERT INTO table_1 (id,data) VALUES(3, 'Y ')
    INSERT INTO table_1 (id,data) VALUES(4, 'Y')
    

    All I can think of is maybe the collations are different between the databases, but then it should always show the tables as different...

    I don't think we could work this out unless we has all of your data compare settings and maybe even a backup of the database.
  • Options
    Hi

    I'm using the latest version - 10.4.8.62. I'm also facing the same issue when trying to compare the tables with 'Treat Empty Strings as NULL' option enabled.

    I created tables similar to the ones that you have created and I'm getting incorrect results (high-lighting equal values as differences).

    Can you please help.
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Again, we'll probably need to see the databases to reproduce and fix the problem.
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Treat empty strings as NULL appears to be broken and we have logged a bug SDC-1651.

    The problem seems to be in the results display. When you actually use SQL Data Compare to create the script, it does not try to update any of these columns it has erroneously identified as being different.
  • Options
    chetmuschetmus Posts: 1 Bronze 1
    We are currently using version 10.7.0.23 and still seeing the issue. Is there a scheduled release date for the fix to this issue ?
  • Options
    jmeyerjmeyer Posts: 70 Bronze 2
    I can confirm this behavior under 10.7.0.23 as well
  • Options
    String a = null, it will only save a string type in the stack pointer, but the pointer is not just to any string heap.
    String a = string. The Empty, in addition to save a string type in the stack pointer, the pointer points to the string data of pile, the Empty string, the string and a = "";Is the same.


    _____________________________
    http://www.mashgear.com
Sign In or Register to comment.