CHAR Comparison - Trailing Space Bug?
Brian Donahue
Posts: 6,590 Bronze 1
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.
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.
This discussion has been closed.
Comments
No, the column order shouldn't matter. I wonder if you possibly have a different collation order on this column between these two databases?
This seems very strange. Could you post up the creation script for the tables in question so that we can reproduce this anomoly.
Support Engineer
Red Gate Software
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
==========================
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.
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
==========================
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.
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
==========================
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.
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
==========================