SQL Compare 14

Setting project options

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.

Some of the options apply only to the comparison, and don't affect the deployment. Similarly, some options apply only to the deployment.

Hover the mouse pointer over the option for a more detailed description.

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:

Behavior options

  • Auto-map similar columns
  • Decrypt encrypted objects (2008 databases)
  • Enable SQL Monitor integration (find out more)

Ignore options

  • Ignore database and server names in synonyms
  • Ignore filegroups, partition schemes, and partition functions
  • Ignore fill factor and index padding
  • Ignore user properties
  • Ignore white space
  • Ignore WITH element order

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.


SSDT-compatible options - Preview release only

When compare identifies that a scripts folder is a Microsoft Database Project, which is only supported as a Preview release in SQL Compare, the following standard options will also be used automatically.

  • Ignore permissions
  • Ignore SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements

  • Ignore WITH NOCHECK
  • Ignore system named constraint and index names

Add database USE statement

Command line option: AddDatabaseUseStatement

Alias: adus

Adds a USE statement at the top of the SQL deployment script.

Add DROP and CREATE for rerunnable scripts

Command line option: DropAndCreateForReRunnableScripts

Alias: dac

Legacy aliases: DropAndCreateInsteadOfAlter, dacia

When this option is specified, SQL Compare replaces ALTER statements in the deployment script with DROP and CREATE statements for:

  • Views
  • Stored Procedures
  • Functions
  • Extended Properties
  • DDL Triggers
  • DML Triggers
If you specify this option, you must also select the Add Object Existence Checks option, or the deployment script will fail. This is done for you in the UI.

Add NO POPULATION to fulltext indexes

Command line option: AddNoPopulation

Alias: anp

Adds the 'NO POPULATION' clause to all new fulltext indexes, so that you can control when the first population occurs, rather than letting them populate at deployment time.

If you use ADD NO POPULATION then Change Tracking will be turned off on the index, as the option is not compatible with change tracking.

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

Add object existence checks

Command line option: ObjectExistenceChecks

Alias: oec

Legacy alias: ObjectExistenceChecks

Checks for the existence of objects affected by the deployment by adding IF EXISTS statements in the deployment script.

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

Enabling this option will cause SQL Compare to generate deployment scripts that use sp_executesql for object types where this is required to be able to perform the check.


Add ONLINE = ON when creating indexes

Command line option: OnlineIndexBuild

Alias: oib

Adds the ONLINE = ON option when creating relational indexes.

Note that some indexes cannot be created with ONLINE = ON within a transaction, so you may need to also choose the "Don't use transactions in deployment scripts" option.

Add WITH ENCRYPTION

Command line option: AddWithEncryption

Alias: we

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 is not saved in the snapshot.

Auto-map similar columns

The sense of this option is reversed on the command line so if it is set in a project then to get the same behavior on the command line it should not be set.

Command line option: NoAutoColumnMapping

Alias: nacm

By default, SQL Compare will automatically map columns with compatible data types and similar names in mapped tables. Setting this option in a project will cause SQL Compare to only map identical columns.

Consider next filegroups in partition schemes

Command line option: ConsiderNextFilegroupInPartitionSchemes

Alias: cfgps

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.

Database Project compatible script folder output

Not on the command line.

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

Decrypt encrypted objects

Command line option: DecryptEncryptedObjects

Alias: deo

Legacy aliases: DecryptPost2kEncryptedObjects, dp2k

When this option is specified, SQL Compare decrypts text objects in databases created using the WITH ENCRYPTION option.

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

  • When comparing large databases with few encrypted objects, selecting this option may result in slower performance.
  • When this option isn't selected, encrypted text objects are shown as different and can't be deployed.
  • Decrypting encrypted objects requires that the user has sysadmin permissions
  • This option does not work with Azure SQL databases

Deploy all dependencies

Command line option: IncludeDependencies

Alias: incd

Includes dependent objects when comparing and deploying databases. For example, if a view depends on a table then the table will be deployed when deploying the view.

This option does not appear in the project options in the UI but is instead displayed on the dependencies step of the deployment wizard where a subset of the differences are selected for deployment and unselected dependencies were identified.

Disable DDL triggers during deployment

Command line option: DisableAndReenableDdlTriggers

Alias: drd

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.

Do not add error handling statements to deployment scripts

Command line option: NoErrorHandling

Alias: neh

Removes error handling from the deployment scripts to produce more readable SQL.

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.

If you deploy using SQL Compare, the deployment is always stopped when an error is encountered.

This option can only be selected if the Don't use transactions in deployment scripts option is selected.

Do not include comment header in the deployment script

Command line option: DoNotOutputCommentHeader

Alias: nc

When this option is specified, comments and comment headers are not included in the output deployment script.

Do not use ALTER ASSEMBLY to change CLR objects

Command line option: DontAlterAssembly

Alias: daa

Legacy aliases: UseClrUdtToStringForClrMigration, uclr

If CLR objects included in the deployment, 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 more information, see Understanding the deployment.

This option affects the deployment only.

Do not use transactions in deployment scripts

Command line option: NoTransactions

Alias: nt

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

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

If the deployment includes memory-optimized objects (memory-optimized tables, natively compiled stored procedures or memory-optimized table types), you must select this option to create a valid deployment script.

For more information, see Deploying memory-optimized objects.

Enable SQL Monitor integration

The sense of this option is reversed on the command line so if it is set in a project then to get the same behavior on the command line it should not be set.

Command line option: NoDeploymentLogging

Alias: ndl

Appends a logging statement at the end of the SQL Compare generated deployment script to log the deployment details to the SQL Server Log in order for SQL Monitor to detect the deployment and mark it on the timeline for performance data (find out more).

Force column order

Command line option: ForceColumnOrder

Alias: f

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.

Throw on file parse failed

Command line option: ThrowOnFileParseFailed

 Alias: tofpf

Throws an exception when parsing a scripts folder fails.

This option is only available on the command line. In the UI the script parser error dialog will be displayed when this error occurs.

Use case-sensitive object definition

Command line option: UseCaseSensitiveObjectDefinition

Alias: cs

Legacy alias: CaseSensitiveObjectDefinition

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.

Be careful when you change this option. For example, if you create a schema snapshot with this option selected and you then compare the snapshot with another database without this option set, SQL Compare may produce unexpected errors.

Use database compatibility level

Command line option: UseCompatibilityLevel

Alias: ucl

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

Ignore

Ignore authorization on schema objects

Command line option: IgnoreSchemaObjectAuthorization

Alias: isoa

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

Ignore bindings

Command line option: IgnoreBindings

Alias: ib

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 certificates, symmetric keys, and asymmetric keys

Command line option: IgnoreCertificatesAndCryptoKeys

Alias: icc

SQL Server severely restricts access to certificates, symmetric keys, and asymmetric keys. This means 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 change tracking

CommandLineOption: IgnoreChangeTracking

Alias: ict

Ignores change tracking when comparing and synchronizing databases.

Ignore check constraints

Command line option: IgnoreCheckConstraints

Alias: ich

Legacy alias: IgnoreChecks

Ignores check constraints when comparing and deploying databases.

Ignore collations

Command line option: IgnoreCollations

Alias: ic

Ignores collation orders on character datatype columns when comparing and deploying databases.

Ignore comments

Command line option: IgnoreComments

Alias: icm

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

Comments will still appear in the deployment scripts.

Ignore constraint and index names

Command line option: IgnoreConstraintAndIndexNames

Alias: icn

Legacy alias: IgnoreConstraintNames

Ignores the names of indexes, foreign keys, primary keys, and default, unique, and check constraints when comparing views, tables and table-valued types. If an index or constraint differs in ways other than its name then its name won't be ignored when it is deployed. Note that this does not ignore names in the definition of a view, for example in a table index hint. In the case of a scripts folder target, SQL Compare will deploy objects using system-named constraints even if the source or target uses constraint names.

Ignore data compression

Command line option: IgnoreDataCompression

Alias: idc

Ignores page and row compression for tables and indexes. When Ignore filegroups is selected, compression is automatically ignored for partitioned tables. In the case of a scripts folder target, SQL Compare will deploy objects without data compression even if the source or target uses data compression.

Ignore database and server name in synonyms

Command line option: IgnoreDatabaseAndServerNameInSynonyms

Alias: idsn

Legacy aliases: IgnoreDatabaseAndServerName, IgnoreDatabaseName, idn

Ignores the database and server name in synonyms when comparing databases.

Ignore DML trigger order

Command line option: IgnoreTriggerOrder

Alias: ito

DML triggers can have an order specified, such as FIRST INSERT, LAST UPDATE, and so on. Specify this option to ignore the trigger order for DML triggers when comparing and deploying databases. The DDL trigger order isn't affected.

Ignore DML triggers

Command line option: IgnoreTriggers

Alias: it

Ignores DML triggers when comparing and deploying databases.

Ignore dynamic data masking

Command line option: IgnoreDynamicDataMasking

Alias: iddm

Ignores MASKED clauses on table columns. Whilst masking-only differences will not be deployed, if your target column was masked and has any change deployed to it, for memory-optimized tables and scripts folders this will cause the field to lose its masking function regardless of whether it was also masked in the source database.

Ignore event notification on queues

Command line option: IgnoreEventNotificationsOnQueues

Alias: iqen

Legacy alias: IgnoreQueueEventNotifications

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

Ignore extended properties

Command line option: IgnoreExtendedProperties

Alias: ie

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

This will not ignore the sensitivity classification extended properties on SQL Server 2017 and older.

Ignore filegroups, partition schemes and partition functions

Command line option: IgnoreFileGroupsPartitionSchemesAndPartitionFunctions

Alias: ifg

Legacy alias: IgnoreFileGroups

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 fill factor and index padding

Command line option: IgnoreFillFactor

Alias: if

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

Ignore foreign keys

Command line option: IgnoreForeignKeys

Alias: ifk

Legacy aliases: IgnoreKeys, ik

Ignores foreign keys when comparing and deploying databases.

Ignore full-text indexing

Command line option: IgnoreFullTextIndexing

Alias: ift

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

Ignore identity property on columns

Command line option: IgnoreIdentityPropertiesOnColumns

Alias: iip

Legacy alias: IgnoreIdentityProperties

Ignores the identity property on columns when comparing databases. The identity property won't be ignored when databases are deployed.

Ignore identity seed and increment values

Command line option: IgnoreIdentitySeedAndIncrementValues

Alias: isi

Legacy alias: IgnoreIdentitySeedAndIncrement

For identity properties, ignores only the identity seed and increment values when comparing databases. They won't be ignored when the databases are deployed.

Ignore indexes

Command line options: IgnoreIndexes

Alias: ii

Ignores indexes, unique constraints, and primary keys 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 INSTEAD OF triggers

Command line option: IgnoreInsteadOfTriggers

Alias: iit

Ignores INSTEAD OF DML triggers when comparing and deploying databases.

Ignore LOCK properties of indexes

Command line option: IgnoreLockPropertiesOfIndexes

Alias: ilpi

Legacy aliases: IgnoreIndexLockProperties, iilp

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

Ignore migration scripts for databases

Command line option: IgnoreMigrationScripts

Alias: ims

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

Ignore NOCHECK and WITH NOCHECK

Command line option: IgnoreNocheckAndWithNocheck

Alias: inwn

Ignores the NOCHECK and WITH NOCHECK arguments on foreign keys and check constraints. When this option is specified, SQL Compare always applies constraints, even when NOCHECK and WITH NOCHECK are enabled.

Ignore NOT FOR REPLICATION

Command line option: IgnoreNotForReplication

Alias: infr

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

If you specify this option, the NOT FOR REPLICATION statement won't be displayed in the object creation script for foreign keys, identities, and check constraints.

For 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 specify the IgnoreWhiteSpace 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 IgnoreWithNocheck option to identify these objects as being the same.

Ignore nullability of columns

Command line option: IgnoreNullability

Alias: in

Ignore performance indexes

Command line option: IgnorePerformanceIndexes

Alias: ipi

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

Ignore permissions

Command line option: IgnorePermissions

Alias: ip

Ignores permissions on objects when comparing and deploying databases.

Ignore replication triggers

Command line option: IgnoreReplicationTriggers

Alias: irpt

Ignores replication triggers when comparing and deploying databases.

Ignore SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements

Command line option: IgnoreQuotedIdentifiersAndAnsiNullSettings

Alias: iq

Ignores these common 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 them and the deprecated SET ANSI_PADDING from the initial SET statement.

Ignore sensitivity classifications

Command line option: IgnoreSensitivityClassification

Alias: isc

Ignores sensitivity classification on columns. 

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

Ignore statistics incremental

Command line option: IgnoreStatisticsIncremental

Alias: isinc

Ignores the Statistics_Incremental property on indexes when comparing and synchronizing databases.

Ignore square brackets in object names

Command line option: IgnoreSquareBrackets

Alias: isb

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 statistics

Command line option: IgnoreStatistics

Alias: ist

Ignores statistics when comparing and deploying databases. In the case of a scripts folder target, SQL Compare will deploy objects without statistics even if the source or target uses statistics.

Ignore STATISTICS_NORECOMPUTE property on indexes

Command line option: IgnoreStatisticsNorecompute

Alias: isn

Ignores the STATISTICS_NORECOMPUTE property on indexes and primary keys.

Ignore system named constraint and index names

Command line option: IgnoreSystemNamedConstraintAndIndexNames

Alias: iscn

Legacy alias: IgnoreSystemNamedConstraintNames

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 tSQLt framework and tests

Command line option: IgnoretSQLt

Alias: itst

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 user properties

Command line option: IgnoreUserProperties

Alias: iup

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

If you don't specify this option, 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.

Ignore users' permissions and role memberships

Command line option: IgnoreUsersPermissionsAndRoleMemberships

Alias: iu

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 whitespace

Command line option: IgnoreWhiteSpace

Alias: iw

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

Ignore WITH element order

Command line option: IgnoreWithElementOrder

Alias: iweo

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), specify this option to ignore the order of the WITH elements when comparing and deploying databases.

Ignore WITH ENCRYPTION

Command line option: IgnoreWithEncryption

Alias: iwe

Ignores WITH ENCRYPTION statements on triggers, views, stored procedures and functions. This option overrides Add WITH ENCRYPTION.

Ignore WITH NOCHECK

Command line option: IgnoreWithNocheck

Alias: iwn

Ignores the WITH NOCHECK argument on foreign keys and check constraints. When this option is specified, disabled constraints aren't ignored, so this option is useful if you want to find out if a constraint is disabled.

If you want to ignore both NOCHECK and WITH NOCHECK arguments, use IgnoreNoCheckAndWithNoCheck.

Foreign keys or constraints that are disabled aren't ignored.

Contents


Didn't find what you were looking for?