CHAR Comparison - Trailing Space Bug?

Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
edited September 13, 2005 11:28AM in SQL Data Compare Previous Versions
Hi Jeff,

Yes, Data Compare is sensitive to leading and trailing spaces in varchar columns. The char datatype shouldn't be affected, since the data is always the same length because SQL Server pads it for you to make sure that the data is always the same size.

If you are looking for data with leading or trailing spaces, the LEN function is a bad choice because it strips pad characters off automatically. SQL's DATALENGTH function is a much better choice for finding variable length data because it includes the spaces in its' calculation.

Comments

  • Hi Jeff,

    No, the column order shouldn't matter. I wonder if you possibly have a different collation order on this column between these two databases?
  • Hello,

    This seems very strange. Could you post up the creation script for the tables in question so that we can reproduce this anomoly.
    Chris Sampson
    Support Engineer
    Red Gate Software
  • Hi!

    I have the same problems so I'm very interested in knowing if the new version corrects the problem.

    Thanks.
    ==========================
    Patrick Barry
    Administrateur sénior de bases de données
    Senior Database Administrator
    UAP Inc.
    http://www.uapinc.com
    ==========================
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Ed,

    Are you sure that the data type is varchar in both databases, and you're not comparing a char to a varchar column? Also keep in mind that the Data Comparison is always case-sensitive regardless of the server collation.
  • Hi!

    Has there been any advance on that matter? We rely a lot on SQL Data Compare to make sure everything is ok before we go to production and I must say that this "bug" (or at least it's what it seems to be) creates a lot of mistrust in SQL Data Compare from our QA people. And I would really like to tell them that it's being fixed in the next release...

    Patrick
    ==========================
    Patrick Barry
    Administrateur sénior de bases de données
    Senior Database Administrator
    UAP Inc.
    http://www.uapinc.com
    ==========================
  • Hi Patrick,

    There is a solution of sorts on the way. We've added an option to 'trim trailing spaces'. If you want the patched version, please send an email to support (AT) red-gate.com and I will send the updated files that will give you the new option.
  • Hi Ed and Brian,

    thanks for the info. As for your offer Brian, I can wait for the release if it's not too far in the future. I just needed to know what was happening with this problem. Brian, I would really appreciate it if you could give me a time frame for the next release.

    P.S. I have 2 theories on why we're having this problem:

    1) SQL Server might be compressing information internally when it needs to change records from one page to another. That way, we get spaces in one database and not in the other one (or after doing an update).

    2) Depending on the MDAC version installed, the SQL Server OLEDB driver might have a bug in it (or in the ADO.NET) and might be padding blanks when it should not. For instance, it the driver uses the "ANSI_PADDING" setting at the server level (or database level or connection level) instead of the one at the table level, we would be getting different results from different servers (or databases).

    Unfortunately, I haven't had the time to test all these scenarios to confirm anything.

    Patrick
    ==========================
    Patrick Barry
    Administrateur sénior de bases de données
    Senior Database Administrator
    UAP Inc.
    http://www.uapinc.com
    ==========================
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Patrick,

    That's most interesting, thanks! I've seen from running SQL Profiler against a Data Compare session that we do set the ANSI_PADDING ON for the connection.
  • We are having this same problem and it has become a show stopper. I'd really appreciate any update on this bug if you are able to provide additional information.
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    TrimTrailing spaces is already commercially available in the current version of Data Compare. Have you tried using that option?
  • Hi!

    Just to let you know that version 3.2.1.14 corrected my problems.

    Thanks!

    Patrick
    ==========================
    Patrick Barry
    Administrateur sénior de bases de données
    Senior Database Administrator
    UAP Inc.
    http://www.uapinc.com
    ==========================
This discussion has been closed.