Red Gate forums :: View topic - Case-sensitive comparison makes the repository messy
Return to RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

Case-sensitive comparison makes the repository messy

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message

Joined: 12 Jun 2012
Posts: 81
Location: Edinburgh

PostPosted: Fri Oct 18, 2013 8:08 pm    Post subject: Case-sensitive comparison makes the repository messy Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue

Joined: 23 Aug 2004
Posts: 6677

PostPosted: Tue Oct 22, 2013 9:30 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB © 2001, 2005 phpBB Group