You can use project options to modify the behavior of SQL Compare. For example, you can set SQL Compare to ignore certain objects even if they're different, or so it doesn't script certain properties for deployment (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 Use case sensitive object definition option. When you have reviewed your comparison results, you may 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.

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 Redgate 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.

Add object existence checks

When this option is selected, SQL Compare checks for the existence of objects affected by the deployment by adding IF EXISTS statements in the deployment script.

This option can be useful, for example, if you want to run the deployment script multiple times.

Use DROP and CREATE for rerunnable scripts

When this option is selected, SQL Compare attempts to make scripts rerunnable by changing ALTER into DROP and CREATE statements, and adding conditional DROP statements to CREATE statements, for the following objects:

• Views
• Stored Procedures
• Functions
• Extended Properties
• DDL Triggers
• DML Triggers

Using this option may require additional database objects to also be dropped and recreated if those other objects depend on the selected objects; these operations will be added to the script automatically if dependencies are included.

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.

Use database compatibility level

Uses a database's compatibility level instead of the SQL Server version.

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 deployment. Data will be preserved.

Do not use transactions in deployment scripts

Removes transactions from the deployment script to produce SQL code that is more readable.

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

Do not add error handling statements to deployment scripts

Removes error handling statements from the deployment script to produce SQL code that is more readable.

If you run the script and this option is not selected, the script will stop executing after encountering an error. This can be useful if you want to continue running the script when there are errors.

Add WITH ENCRYPTION

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

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

Auto-map similar columns

When this option is selected SQL Compare will automatically map columns with compatible data types and similar names in mapped tables. Deselecting this option will cause SQL Compare to only map identical columns.

Do not use ALTER ASSEMBLY to change CLR objects

If CLR objects are to be deployed, 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 deployment.

This option affects the deployment only.

Consider next filegroups in partition schemes

When this option is selected, if a partition scheme contains a next filegroup, SQL Compare considers the next filegroup in the comparison and deployment if the partition scheme is extended. The next filegroup doesn't affect how data is stored.

To ignore next filegroups, clear the check box.

Disable DDL triggers during deployment

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

Don't include a comment header in the deployment script

When this option is set, the comment header isn't included in the deployment script.

Add database USE statement

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

This option affects the deployment only.

Decrypt encrypted objects

When this option is selected, SQL Compare will decrypt encrypted database objects.

When SQL Compare saves a snapshot or scripts folder, this option is set and all encrypted objects are decrypted.

Ignore migration scripts for databases

When this option is selected, SQL Compare won't consider migration scripts when you compare a database.

This option can be useful if you've encountered errors relating to migration scripts and/or source control when comparing a database.

For more details about migration scripts, see Working with migration scripts

Database project compatible script folder output

This option forces script folder output to conform to the style used by the most recent Visual Studio database project type.

Ignore indexes

Ignores indexes when comparing and deploying databases.  In the case of memory-optimized tables or script folder targets, index differences will still be deployed if there are other differences between the tables.

Ignore performance indexes

Ignores everything that the 'Ignore indexes' option ignores except primary keys and unique constraints.

Ignore change tracking

Ignores change tracking when comparing and synchronizing databases. In the case of memory-optimized tables or script folder targets, change tracking differences will still be deployed if there are other differences between the tables.

Ignore permissions

Ignores permissions on objects when comparing and deploying databases.

Ignore DML triggers

Ignores DML triggers when comparing and deploying databases.

Ignore constraint and index names

Ignores the names of indexes, foreign keys, primary keys, and default, unique, and check constraints when comparing fields in views, tables and table-valued types. Names won't be ignored when the databases are deployed. Note that this does not ignore names in view content, for example in a table index hint.

Ignore system named constraint and index names

Ignores the names of system named indexes, foreign keys, primary keys, and default, unique, and check constraints when comparing fields in views, tables and table-valued types. Names won't be ignored when the databases are deployed. Note that this does not ignore names in view content, for example in a table index hint.

Ignore whitespace

Ignores white space (newlines, tabs, spaces, and so on) when comparing databases. White space won't be ignored when the databases are deployed.

Ignore comments

Ignores comments when comparing views, stored procedures, and so on.

Ignore full-text indexing

Ignores full-text catalogs and full-text indexes when comparing and deploying 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

Ignores statistics when comparing and deploying databases.

Ignore foreign keys

Ignores foreign keys when comparing and deploying databases.

Ignore check constraints

Ignores check constraints when comparing and deploying databases.

Ignore identity seed and increment values

Ignores identity seed and increment values when comparing and synchronizing databases. In the case of memory-optimized tables or script folder targets, identity seed and increment differences will still be deployed if there are other differences between the tables.

Ignore fill factor and index padding

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

Ignore INSTEAD OF triggers

Ignores INSTEAD OF DML triggers when comparing and deploying databases.

Ignore bindings

Ignores bindings on columns and user-defined types when comparing and deploying 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.

Ignore filegroups, partition schemes, and partition functions

Ignores filegroup clauses, partition schemes, and partition functions on tables and keys when comparing and deploying databases. Partition schemes and partition functions aren't displayed in the comparison results.

Ignore extended properties

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

Ignore SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements

Ignores these SET statements when comparing views, stored procedures, and so on. These statements won't be ignored when the databases are deployed. This will also remove the initial set statement for these settings and ANSI_PADDING.

Ignore sensitivity classifications

Ignores sensitivity classification on columns.  This will also ignore the sensitivity classification extended properties on SQL Server 2017 and older.

Ignore collations

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

Ignore certificates, symmetric keys, and asymmetric keys

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

If certificates, symmetric keys, and asymmetric keys are selected for deployment, only the permissions are deployed.

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 deploying databases. The DDL trigger order isn't affected.

Ignore event notification on queues

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

Ignore user properties

If this option isn't 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 deployment, 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 deploying databases.

Ignore LOCK properties of indexes

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

Ignore replication triggers

Ignores replication triggers when comparing and deploying 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 won't 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 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 this will apply to constraints in all objects.

Ignore identity property on columns

Ignores identity properties when comparing and synchronizing databases. In the case of memory-optimized tables or script folder targets, identity property differences will still be deployed if there are other differences between the tables.

Ignore data compression

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

Ignore database name and server name in synonyms

Ignores database names in synonyms when comparing databases.

Ignore owner authorization on schema objects

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

Ignore STATISTICS_NORECOMPUTE property on indexes

Ignores the STATISTICS_NORECOMPUTE property on indexes and primary keys.

Ignore square brackets in object names

Ignores starting and ending square brackets in object names which have been escaped using square brackets. This applies to textual objects such as stored procedures, triggers, etc.

Ignore tSQLt framework and tests

Ignores the tSQLt schema and its contents, the tSQLtCLR assembly, the SQLCop schema and its contents, and any schemas and their contents with the tSQLt.TestClass extended property set.

Ignore encryption of object text

Ignores WITH ENCRYPTION statements on triggers, views, stored procedures and functions.

This option overrides Add WITH ENCRYPTION.