Description: Using xSQL Schema Compare and xSQL Data Compare to keep the tables of two databases in sync is a no-brainer, and, almost always, the default synchronization options will do the trick. However, there are a few special cases in which knowing how to correctly manipulate the comparison and synchronization options is a must to complete the synchronization process correctly. One of this cases is the synchronization of NOT NULL Columns.

Let’s say there are two tables in two different databases that need to be synchronized. Both of these tables have data and one of them has one or more columns with a NOT NULL constraint and no default value. In this case, synchronization of these table’s schemas using the default options is not possible.

Reason: This is because when you generate the synchronization script xSQL Schema Compare will create the following statement:

Notice the NOT NULL without a default value specification in the ALTER TABLE statement. Logically this is correct because one of the tables has the ‘TestColumnNotNull’ with a NOT NULL constraint and no default value, so it will try to create the same column on the other table. But, because these tables both have data, adding a column in one of them would mean that the values for this column would be NULL. Since the column has a NOT NULL constraint SQL Server will not allow the addition of this column.

Workaround: The solution to this is to force xSQL Schema Compare to script the new columns as NULL by checking the ‘Script new columns as NULL’ in the comparison options dialog (picture below):

This time, when the synchronization script is generated it will not add the NOT NULL constraint, and SQL Server will allow the schema synchronization. After this, the column on the target database can be manually updated with valid data, or the data synchronization with xSQL Data Compare can be performed, which will fill the newly added column with values. To add the NOT NULL constraint, all that needs to be done is to uncheck the ‘Script new columns as NULL’ and perform the schema comparison and synchronization. This time SQL Server will have no problem with adding a NOT NULL constraint in the new column because it does not have any NULL values.

And there you go, the tables are synchronized. CodeProject