The project options are a set of advanced features that enable you to modify the behavior of SQL Data Compare. For example, you can set a project option so that SQL Data Compare disables then re-enables triggers when you synchronize your databases.
When you create a new comparison project, you should run the comparison with the default options, then review your comparison results. However, if your database is on a SQL Server with case-sensitive sort order, you must clear the Ignore case of object names option. When you have reviewed your comparison results, you may then want to consider changing some of the options.
Project options are saved for each comparison project.
To display the options for your comparison project:
- for the current project, click
Edit Project, and click the Options tab - for a new project, click
Comparison Projects, click New, and then click the Options tab - for an existing project, click Comparison Projects, select the project, click Edit, and then click the Options tab
To save the current selection of options as your defaults, click Save As My Defaults. To restore your defaults, click My Defaults. The saved defaults will be used for all new projects.
To reset all the options to their original settings, click Red Gate Defaults. The default options for a comparison project are as follows:
- Ignore case of object names
- Ignore spaces in object names
- Include identity columns
- Include timestamp columns
- Show identical values in results
- Disable foreign keys
- Use transactions in SQL scripts
- Reseed identity columns
Ignore case of object names
Ignores the case of the object names (tables, views, users, roles, schemas, indexes, and columns) when mapping. For example, [dbo].[Widget] will be mapped to [dbo].[wIDgEt].
Note that if the databases that you are comparing are running on a SQL Server that uses case-sensitive sort order, you should ensure that this option is not selected.
Ignore spaces in object names
Ignores spaces in object names (tables, views, users, roles, schemas, indexes, and columns) when mapping. For example, [dbo].[Widget Prices] will be mapped to [dbo].[WidgetPrices].
Ignore underscores in object names
Ignores underscores in the object names (tables, views, users, roles, schemas, indexes, and columns) when mapping. For example, [dbo].[Widget_Prices] will be mapped to [dbo].[WidgetPrices].
Include views
Includes views in the comparison. Generally, views can be synchronized only if the referenced rows are from a single table, and the referenced columns are simple (for example, they must not include identity columns or computed columns).
Include identity columns
Includes identity columns in the comparison. You cannot synchronize a view if it includes an identity column.
Include timestamp columns
Includes timestamp columns in the comparison. Timestamp columns cannot be synchronized.
Trim trailing spaces
If the data in two columns differs only by the number of spaces at the end of the string, SQL Data Compare considers the data to be identical. This option does not apply to CLR or XML columns.
Trailing spaces are ignored during synchronization, if this option is selected.
Force binary collation (case-sensitive)
For all string data types, forces binary collation irrespective of column collation, resulting in a case-sensitive comparison. When this option is selected and the comparison key is a string, this may result in slower performance because the indexes are not used.
Show identical values in results
If this option is not selected, identical values will not be stored on disk nor appear in the comparison results.
Use checksum comparison
Performs a checksum prior to comparison. The data is compared only if the checksums differ. Note that if the data differs only in text or image columns, the checksums will be identical and the data may be flagged incorrectly as identical.
On SQL Server 2000 db_owner permissions are required.
You cannot select this option if the Show identical values in results project option is selected.
Compress temporary files
Compresses the temporary files that SQL Data Compare generates while performing the comparison. This reduces the possibility of running out of memory when comparing very large databases, but it can also increase processor usage and may slow down performance.
When you select this option, you will not be able to sort the results of the comparison. Clicking on a column header in the Row Differences pane does not sort the column data, due to the compression process.
Disable foreign keys
Disables then re-enables foreign keys in the synchronization SQL script. Note that in some circumstances foreign keys will be dropped and recreated rather than disabled and re-enabled.
Drop primary keys, indexes, and unique constraints
Drops then recreates primary keys, indexes, and unique constraints in the synchronization SQL script. If the primary key, index, or unique constraint is the comparison key, it cannot be dropped.
Use transactions in SQL scripts
Inserts BEGIN TRANSACTION at the beginning of the synchronization SQL script and COMMIT TRANSACTION at the end of the synchronization SQL script.
Transport CLR data types as binary
Uses the binary representation of CLR types in the synchronization SQL script. If this option is not selected SQL Data Compare uses the string representation.
Disable DML triggers
Disables then re-enables DML triggers on tables and views in the synchronization SQL script.
Disable DDL triggers
Disables then re-enables DDL triggers in the synchronization SQL script.
Reseed identity columns
Reseeds identity columns so that identity values in the database you are updating match values in the source database.
© Red Gate Software Ltd 2008. All Rights Reserved.