Schema Compare Improvements

Schema Compare is an incredibly useful tool, providing a visual head over SSDT’s model differencing and update engine.  It can be used to compare any combination of database, project or dacpac, and allows selective update of the target schema (via an update script in the case of a dacpac).   We’ve made some significant changes to the tool for the RTW release, improving its look and feel, particularly to make it easier to digest and process comparison results.  This post describes many of Schema Compare’s key features – some of which surfaced in CTP4 – with a screen shot at the bottom that highlights several of them.

First, the visual comparison ‘language’ of the results grid:

Differences-Only by Default: By default the grid contains differences only (with empty folders removed) – if there is only one difference you will see just one item.  And the grid always contains all the actions resulting from the comparison – while you can hide an action temporarily within a contracted group it is always present in the grid and will apply to the update or script unless you exclude it by unchecking the action.

Equal Objects filter:
A toolbar button adds equal objects to the grid.   Enabling this is useful if you want to review, for example, unchanged columns alongside the changed columns in a table. 

Unsupported Actions filter: You can also choose to see unsupported actions – these result from differences for which there is no supported action that can be taken on the target.  These typically result from differences in server objects or built-in types between schema versions.

Action Icons: Actions (Add, Change, and Delete) are visualized using icons, making it easier to absorb a set of changes at a glance. The checkbox alongside an icon indicates if the action will be included in the update or generated script.  If there is no icon the item will not be included in an update or script.

Grayed Items: Items that do not contribute to the update are grayed – excluded actions, unsupported actions and equal objects are all grayed.  Folders are grayed when all their contents are grayed making it easy to see when a group of differences have all been excluded without you needing to drill in.

Grouping: By default, items are grouped by action so you can quickly assess what changes will be made on update.  You can also group the results by object type or by schema. You can expand or collapse a group to temporarily hide detail, and you can exclude all or include all objects in a group.

Refactor Highlighting: Schema Compare processes the refactor log if present when targeting a database.  Refactoring is indicated in the grid as a change action with the source name bolded to highlight the new schema and/or name.  Refactoring will cause objects to be renamed in the database.  Refactoring sometimes also shows up as a second order effect on other objects that SQL Server will modify when applying the rename.  These will not be marked as actions in the grid as you cannot exclude them, but you will see the changed script if you select the affected object. 

Probably the biggest set of changes affects the script difference pane.  While the grid provides a great overview, to see all changes to an object in the grid you have to fully expand it, which, can quickly clutter the view if you’re reviewing many objects.  To address this we’ve focused more attention on the script differencing experience – after all, you are writing and editing object scripts to begin with.  Changes include:

Expanded Object Scripts:  The script difference pane now shows the combined scripts for an object and its hierarchical children.  This gives a complete picture of all the changes affecting an object in one easy-to-scan place.  To complement this, the Next and Previous buttons step between top-level objects only.  Together, these two changes can dramatically simplify scanning through the results of a comparison. 

Enhanced Script Differencing: The script difference algorithm now treats child objects as discrete entities, more effectively highlighting those that have been added, deleted or changed.  The color scheme is now more subtle and better reinforces the direction of changes.  And remember that you can expand the script pane or swap it to the top – so you can easily optimize the layout to better focus on reviewing scripts.

The screen shot below highlights many of these improvements.

Hopefully we’re headed in the right direction – as always we’d love to hear your feedback.

Enjoy!