Case-sensitive comparison makes the repository messy

Forum for users of SQL Compare schema synchronization utility

Case-sensitive comparison makes the repository messy

Postby isme » Fri Oct 18, 2013 8:08 pm

I'm in favor of case-sensitive comparison as a principle.

SQL Compare's implementation causes some issues, though.

Enabling Case Sensitivity


Case sensitivity is enabled through the 'Use case sensitive object definition' project option.

The dialog says:

When this option is on SQL Compare will perform case sensitive text comparisons on objects. For example, object names such as ATable and atable will be considered to be different.

You should use this option only if you have databases with binary or case-sensitive sort order.

Consistency is another good reason to use this option. Inconsistency is distracting.

Comparing the schemas


In my database I have an object called 'Config.DeriveDataCentre'. In my repository I have an object called 'config.DeriveDataCentre'. The object bodies are identical.

With case sensitivity enabled, SQL Compare shows that each schema has an object that the other lacks.

To synchronize the schema, SQL Compare wants to drop 'config.DeriveDataCentre' and create 'Config.DeriveDataCentre'.

Writing the changes


SQL Compare names each file after the object definition it contains.

SQL Compare copies these actions to the clipboard:

Modify fileRedGateDatabaseInfo.xml
Modify fileFunctions\\Config.DeriveDataCentre.sql
Create file Functions\\Config.DeriveDataCentre1.sql

I asked it to sync one object, so why are there three changes?

Perhaps Windows' case-insensitivity confuses SQL Compare. Windows always sees 'Config' and 'config' as equal strings.

Instead of just modifying the existing file, it modifies it and creates a new file called Config.DeriveDataCentre1.sql.

Inspecting the output


Inspect the script folder in Explorer with the TortoiseSVN shell overlay to confirm the changes.

The red spot shows that SQL Compare has modified Config.DeriveDataCentre.sql.

The plain icon shows that SQL Compare has created the unversioned file Config.DeriveDataCentre1.sql.

Diffing the new files


Use WinMerge to compare the two files.

Config.DeriveDataCentre1.sql contains the correct definition.

Config.DeriveDataCentre.sql is empty!

Repeating the comparison


Thankfully, SQL Compare does not rely on the file name to work out what definition each file contains. Instead, it parses each file to build an object model.

It sees the definition in the unversioned file and decides that the objects in each schema are now equal.

However, if you naively commit this change, you'll cause issues for your fellow developers.

We expect the file name to match the object name. Adding '1' to the end looks sloppy.

Zero-length files are ignored by SQL Compare, and do nothing else but clutter the script folder.
Posts: 83
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh

Postby Brian Donahue » Tue Oct 22, 2013 9:30 am

Thanks for pointing this out. I did some research and apparently this behavior is intentional. I can't say if it's correct because I have very little practical experience with source control systems and I can't discuss with you whether or not this is the way it should work, but here is the comment one of the developers left the last time someone ran into this issue (SC-3645)...

The reason is source control, which is the prime scenario for scripts. If we delete a file, then the source control will just think that the file is missing, and will not delete it from source control when the changes are checked in. When the latest version are retrieved, the source control would just re add the object (as the file has been deleted), so corrupting the database this way.

If this is not true (or not true for your particular VCS, as we have to support several of them), please let me know the reasoning so I can re-open the issue.
Brian Donahue
Posts: 6590
Joined: Mon Aug 23, 2004 10:48 am

Return to SQL Compare Previous Versions

Who is online

Users browsing this forum: No registered users and 1 guest