SQL Data Compare showing differences in two identical databases
Published 05 June 2013
When comparing two identical databases, SQL Data Compare may show differences. This behavior occurs for one of the following reasons:
Trim trailing spaces option is not enabled
By default, SQL Data Compare includes spaces and other white space of text fields in the comparison. Likewise, comparing a fixed-length field in one database to a variable-length field will cause differences due to spaces. The Trim trailing spaces option will allow you to ignore these differences in the comparison of text data.
Force binary collation option is enabled
By default, SQL Data Compare uses the collation of a column to decide whether or not to compare data case-sensitively. Most SQL Server databases default to a case-insensitive collation, but this can be overridden using the Force binary collation option in SQL Data Compare.
Click Edit Project to open the Project Configuration dialog box. Select the Options tab. In the Comparison Behavior section ensure the Trim trailing spaces option is selected and the Force binary collation option is not selected.