• SQL Compare
• Schema Comparison Options

How to Customize Schema Comparisons using Auto Map in SQL Compare

It’s a tedious task to have to compare two versions of a SQL script, side by side, for example to find differences between the version of the script on Production, and the one on the Test system. As a developer, you must try to spot the real code differences in among the ‘noise’. The task is made even harder if the same object has different names or properties, in different databases.

SQL Compare is designed to help you automate the process of comparing and synchronizing source and target databases or script directories. One of the intelligent aspects of the tool is that it tries to perform comparisons between objects in the same way that a user might do. In the same way that users often internalize slight differences between columns, SQL Compare tries to do this as well.

In this article, we’ll look at how the table automapping feature works in SQL Compare, and how to control its behavior.

Comparing tables

Imagine that I have a table called Contacts that exists in several databases, with the intention to store the same data in each database. However, the schemas are slightly different in each case, perhaps because developers have named columns slightly differently, or because columns that exist in the table in one database don’t exist in another. This is a common problem, for example, in companies who maintain databases in multiple data centers, and where local development teams have “region-specific” differences in their database.

In my source database, the Contacts table looks as shown in Listing 1

Listing 1

However, in the target database, it looks as shown in Listing 2.

Listing 2

Fire up SQL Compare, select the source and target databases that contain these tables, and run a comparison project. SQL Compare will analyze the source and target schemas and map out any differences in the database objects.

The first thing you’ll notice, at the top, is yellow triangle alert symbol with a notification that SQL Compare has auto-mapped columns. Below this, in the object list, I see the Auto Map symbol right next to the Contacts table.

Figure 1

Check the box for the Contacts table to drill down into the details at the SQL level.

Figure 2

Here, we can see that SQL Compare has auto-mapped the Salutation column in the source to the Title column in the target, as well as the Suffix column to the Ending column.

Without the auto-map feature enabled, SQL Compare would only map tables and columns with the same name in the source and target data sources. This means it would treat Salutation and Suffix as two columns that do not exist on the target, and Title and Ending as two columns that exist only on the target. Therefore, the script SQL Compare would generate to synchronize the source and target would drop both the Title and Ending columns in the target database, and create the Salutation and Suffix columns (as well as the fullname calculated column).

Unless we supplied a migration script to preserve data, we’d obviously lose any existing data in the Title and Ending columns. Auto-map is designed to help avoid this. It allows SQL Compare to auto-map columns that have different names but are identical in other respects (same data types, same collation setting, and so on). In this example, it means that it will, instead, rename Salutation to Title, and rename Suffix to Ending, preserving the data in the renamed columns on the target.

However, is this the behavior you really want? Perhaps, on discussion with other developers, you discover that while Suffix and Ending really are the same column with different names, a salutation might be different from a title, and so the Salutation column has a different purpose than the Title column. Now the team has a design decision to make. In this case, they decide that Salutation best reflects the nature of the data being stored, and so will drop the Title column, and add Salutation, in the target database. This means they need to un-map those two columns. Fortunately, it’s very easy to control the behavior of auto-map, as well as various other SQL Compare options.

Controlling table mapping

The SQL Compare comparison project contains includes many settings that I can control, including the auto-mapping. To view and alter these setting, simply click Edit project at the top.

Figure 3

This gives me a dialog with my data sources, but I have three other tabs at the top, containing various settings I might want to alter for SQL Compare:

• Table mapping – control how SQL Compare auto-maps tables
• Owner mapping – control how SQL Compare maps object owners in source and target
• Options – control various aspects of SQL Compare behavior when running the comparisons and generating the deployment script.

The Options are especially useful in reducing ‘noise’ in the comparison, since it allows us to choose to ignore certain differences that might not be significant for us, such as differences in collation settings, or comments, or whitespace, or in the names of system-named constraints and indexes. However, here we’ll just focus on the Table mapping tab.

If I click that, I get a list of the mappings between the various objects in the database. I can see in the image that my Contacts table was ‘partially mapped’.

Figure 4

A Partial mapping indicates that not all columns in source and target could be mapped. Click on the Partial link for Contacts, and you’ll see that it is only the fullname column in the source that cannot be mapped to any column in the target. All my other columns are mapped, either because they are identically-named, or because they are similar columns that SQL Compare chose to auto-map.

Figure 5

As discussed earlier, I’m happy with the mapping of Suffix to Ending, but not with Salutation to Title, so I’m going to turn off that mapping. I highlight the Salutation – Title mapping and click Unmap. On doing so, these columns move into the “not mapped” portion of the dialog.

Figure 6

Close this dialog and then click Compare now to re-run the comparison, with the mapping change. Figure 7 shows the result.

Figure 7

Now, Salutation is treated as a new column that needs to be created on the target and Title as a column that needs to be dropped. We can see this even more clearly on the Summary view.

Figure 8

Salutation and fullname are green, indicating columns that must be created on the target; Title is red, indicting it is a column that must be dropped on the target; Suffix and Ending are yellow, indicating a column that will be altered, in this case renamed.

If we hit the Deploy button, SQL Compare will generate a deployment script, shown in Figure 9, which when executed on the target, will deploy all required schema changes, resulting in any changed object being updated, every new object being added, and the deletion of any object that is deleted in the source.

Figure 9

You will see a warning that the Title column will be dropped, so you’ll need a migration script to cover the change if you need to preserve existing data. If, instead, you wanted to keep both the Title and Salutation columns, you’d simply need to add the former to your source database.

Customized Comparisons with SQL Compare

SQL Compare includes many options to make comparing objects easier on the developer. Auto mapping is one of these, and if often saves a little time and frustration. However, there are times that you many need to customize your mapping, and this gives you a technique for doing so.

If you have other ideas of requests, please let the Compare development team know on UserVoice.

Tools in this post

SQL Compare

Compare and synchronize SQL Server database schemas

Find out more

SQL Toolbelt

The industry-standard tools for SQL Server development and deployment.

Find out more