The project options enable you to modify the behavior of SQL Compare. For example, you can set a project option so that SQL Compare ignores certain objects even if they are different, or so that it does not script certain properties for synchronization (such as the collation order on columns).

When you create a new 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 select the Treat items as case sensitive option. When you have reviewed your comparison results, you may then want to consider changing some of the options.

The options you set are saved for each project, and are modified on the Project Configuration dialog box.
To display the Project Configuration dialog box, click  (Edit Project), or select Project Options from the Tools menu.

The options you can set are described below. Note that some of the options apply only to the comparison; they do not affect the synchronization. Similarly, some options apply only to the synchronization.

Default options

To make the current selection of options your defaults, click Save As My Defaults. These saved defaults will be used for all new projects. To restore your defaults after making changes, click My Defaults.

To reset all the options to their original settings, click Red Gate Defaults. The default options for a project are as follows:

• Decrypt encrypted objects on 2005 and 2008 databases
• Ignore white space
• Ignore fill factor and index padding
• Ignore filegroups, partition schemes, and partition functions
• Ignore user properties
• Ignore WITH element order
• Ignore database and server names in synonyms

To search for an option, type search text in the Find box. The list is filtered to display only those options that contain the search text.

Use case sensitive object definition

For databases with case-sensitive collation, enables objects with case-sensitive names to be compared and deployed. For example, considers object names such as ATable and atable as different and performs case-sensitive comparisons on stored procedures, and so on.

You should use this option only if you have databases with binary or case-sensitive sort order.

Note that you should take care when you change this option. For example, if you create a database snapshot with this option selected and you then compare the snapshot with another database with the option cleared, SQL Compare may produce unexpected errors.

Force column order

If additional columns are inserted into the middle of a table, this option forces a rebuild of the table so the column order is correct following synchronization. Data will be preserved.

Do not use transactions in synchronization SQL scripts

Removes transactions from synchronization scripts to produce SQL code that is more readable.

If this option is not selected and the synchronization script fails, the script is rolled back to the start of the failed transaction. If this option is selected, the script is not rolled back. This can be useful for detection of errors within a script.

Add WITH ENCRYPTION

Adds WITH ENCRYPTION when stored procedures, functions, views, and triggers are included in the synchronization.

Note that if you use ADD ENCRYPTION on a SQL Server 2005 database, SQL Compare will not subsequently be able to display, compare, or synchronize the encrypted objects.

When SQL Compare creates a snapshot, this option is ignored, and WITH ENCRYPTION is not saved in the snapshot.

Do not use ALTER ASSEMBLY to change CLR objects

This option is used only for SQL Server 2005 and SQL Server 2008 databases.

If CLR objects are to be synchronized, this option forces two rebuilds of the table with conversion to and from strings to update the CLR objects, instead of using ALTER ASSEMBLY. For a detailed explanation, see Understanding the synchronization.

This option affects the synchronization only.

Consider next filegroups in partition schemes

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

When this option is selected, if a partition scheme contains a next filegroup, SQL Compare considers the next filegroup in the comparison and synchronization if the partition scheme is extended. The next filegroup does not affect the way in which data is stored.

To ignore next filegroups, clear the check box.

Disable DDL triggers during synchronization

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

DDL triggers can cause problems when you run the synchronization. Select this option to disable any enabled DDL triggers before synchronizing the databases, and re-enable those triggers following synchronization.

Add database USE statement

Adds a database USE statement to the top of the synchronization script.

This option affects the synchronization only.

Don't include a comment header in the synchronization script

When this option is set, the comment header is not included in the synchronization script.

Decrypt encrypted objects in 2008 and 2005 databases

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

When this option is selected, SQL Compare decrypts text objects in SQL Server 2008 and SQL Server 2005 databases which were created using the WITH ENCRYPTION option. Note that when comparing large databases, selecting this option can result in slower performance.

When this option is not selected, text objects in SQL Server 2008 and SQL Server 2005 databases are shown as different, and cannot be deployed.

Ignore indexes

Ignores indexes when comparing and synchronizing databases.

Ignore permissions

Ignores permissions on objects when comparing and synchronizing databases.

Ignore DML triggers

Ignores DML triggers when comparing and synchronizing databases.

Ignore constraint and index names

Ignores the names of indexes, foreign keys, primary keys, and default, unique, and check constraints when comparing databases. Note that the names will not be ignored when the databases are deployed.

Ignore white space

Ignores white space (newlines, tabs, spaces, and so on) when comparing databases. Note that white space will not be ignored when the databases are deployed.

Ignore comments

Ignores comments when comparing views, stored procedures, and so on. Note that comments will not be ignored when the objects are deployed.

Ignore full-text indexing

Ignores full-text catalogs and full-text indexes when comparing and synchronizing databases.

Ignore users' permissions and role memberships

When role-based security is used, object permissions are assigned to roles, not users. If this option is selected, SQL Compare compares and deploys object permissions only for roles, and members of roles that are roles. Users' permissions and role memberships are ignored.

Ignore STATISTICS_NORECOMPUTE property on indexes

Ignores the STATISTICS_NORECOMPUTE property on indexes and primary keys.

Ignore statistics

Ignores statistics when comparing and synchronizing databases.

Ignore foreign keys

Ignores foreign keys when comparing and synchronizing databases.

Ignore check constraints

Ignores check constraints when comparing and synchronizing databases.

Ignore identity seed and increment values

For identity properties, ignores only the identity seed and increment values when comparing databases. Note that they will not be ignored when the databases are deployed.

Ignore fill factor and index padding

Ignores the fill factor and index padding in indexes and primary keys when comparing and synchronizing databases.

Ignore INSTEAD OF triggers

Ignores INSTEAD OF DML triggers when comparing and synchronizing databases.

Ignore bindings

Ignores bindings on columns and user-defined types when comparing and synchronizing databases. For example, sp_bindrule and sp_bindefault clauses will be ignored.

Ignore WITH NOCHECK

Ignores the WITH NOCHECK argument on foreign keys and check constraints.

Note that foreign keys or constraints that are disabled, are not ignored.

Ignore filegroups, partition schemes, and partition functions

Ignores filegroup clauses, partition schemes, and partition functions on tables and keys when comparing and synchronizing databases. Partition schemes and partition functions are not displayed in the comparison results.

Ignore extended properties

Ignores extended properties on objects and databases when comparing and synchronizing databases.

Ignore SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements

Ignores these SET statements when comparing views, stored procedures, and so on. Note that these statements will not be ignored when the databases are deployed.

Ignore collations

Ignores collations on character data type columns when comparing and synchronizing databases.

Ignore certificates, symmetric keys, and asymmetric keys

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

SQL Server severely restricts access to certificates, symmetric keys, and asymmetric keys. Consequently, SQL Compare cannot compare all of the properties for a symmetric key.

If certificates, symmetric keys, and asymmetric keys are selected for synchronization, only the permissions are synchronised.

Ignore DML trigger order

DML triggers can have an order specified, such as FIRST INSERT, LAST UPDATE, and so on. Select this option to ignore the trigger order for DML triggers when comparing and synchronizing databases. Note that the DDL trigger order is not affected.

Ignore event notification on queues

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

Ignores the event notification on queues when comparing and synchronizing databases.

Ignore user properties

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

If this option is not selected, SQL Compare compares user properties, such as the type of user (SQL, Windows, certificate-based, asymmetric key based) and any schema. If a user is selected for synchronization, SQL Compare deploys the properties where possible.

If you select this option, users' properties are ignored, and only the user name is compared and deployed.

Ignore WITH element order

If a stored procedure, user-defined function, DDL trigger, DML trigger, or view contains multiple WITH elements (such as encryption, schema binding, and so on), select this option to ignore the order of the WITH elements when comparing and synchronizing databases.

Ignore LOCK properties of indexes

This option is used only for SQL Server 2008 and SQL Server 2005 databases.

Ignores index PAGE LOCK and ROW LOCK properties when comparing and synchronizing databases.

Ignore replication triggers

Ignores replication triggers when comparing and synchronizing databases.

Ignore NOT FOR REPLICATION

Ignores the NOT FOR REPLICATION option on foreign keys, identities, check constraints and triggers.

If you select this option, the NOT FOR REPLICATION statement will not be displayed in the object creation script for foreign keys, identities, and check constraints.

In the case of triggers, the NOT FOR REPLICATION statement will be displayed in the object creation script, but will be ignored for the purposes of the comparison. When comparing triggers, you should also select the Ignore white space option, but note that this option will also be applied to all objects in the comparison.

Check constraints and foreign keys that contain the NOT FOR REPLICATION statement in their definition will automatically be flagged as WITH NOCHECK. Use the Ignore WITH NOCHECK option to identify these objects as being the same; but note that this will apply to constraints in all objects.

Ignore identity property on columns

Ignores the identity property on columns when comparing databases. Note that the identity property will not be ignored when databases are deployed.

Ignore data compression

This option is used only for SQL Server 2008 databases.

Ignores page and row compression for tables and indexes. Note that when Ignore filegroups is selected, compression is automatically ignored for partitioned tables.

Ignore database name in synonyms

Ignores database names in synonyms when comparing databases.

Ignore owner authorization on schema objects

This option is used only for SQL Server 2008 and 2005 databases.

Ignores authorization clauses on schema-qualified objects when comparing and synchronizing databases.