Case-sensitive comparison makes the repository messy
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:
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.