Options
Case-sensitive comparison makes the repository messy
isme
Posts: 119
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:
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:
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.
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.
Iain Elder, Skyscanner
Comments
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.