Exploring the SQL Compare Options

Phil Factor dissects and disentangles the various SQL Compare options.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

SQL Compare does precisely what its name suggests: it compares SQL Server databases. It will take a representation of a database, either a real database, a backup, a Redgate Snapshot, a scripts folder, or a source control reference and compare it with another such representation of a database. When it has done it, it will create a script that would alter the schema of the target to be identical to the source. If you want, it will update a database or scripts folder, directly.

It sounds easy, but the problem comes with the details. It is very unlikely that your idea of a comparison will be the same as another user’s idea. So much depends on the style, design, or type of database. There is, for a start, the problem of deciding whether a difference is significant.

There are all sorts of details that will cause people to stroke their chins, wondering whether it is right to list or deploy a change if a comment, index, or constraint changes. How should you handle differences in encryption settings between two databases? What parts of the metadata are strictly outside the responsibility of the development and shouldn’t even be in source control? What if you want the script for doing development work rather than deployment? There are a host of features in SQL Server ,and more are being introduced on every release, and with some of these features, it is by no means clear-cut that the code is suitable for a development version of a database.

The result is that, over the years, a large number of options have been added to alter the way that the comparison and scripting is done. Many of these determine whether certain difference between tables, such as between constraints, nullability or comments, represent a legitimate change that should be deployed to the target.

Setting the Options

In the SQL Compare UI, you set these options in the project Options menu, or in one case on the dependencies step of the deployment wizard where a subset of the differences are selected for deployment and unselected dependencies were identified.

You can also pass the options as parameters to the SQL Compare Command-line.

SQL Change Automation (SCA) projects also use these options and you can specify SQL Compare options in calling SQL Chang Automation PowerShell cmdlets.

Both SQL Compare and SCA use default options decided by Redgate. SQL Compare will, out of the box, ignore such things as white space, database user properties and database and server names in synonyms. SQL Compare, however, allows you to save your own preferred default options. On top of that level of customization, you can alter the way that SQL Compare command-line, and SCA does its comparisons and generates its scripts every time it runs, by passing to it a collection of options.

Comparison Options

Use database compatibility level

UseCompatibilityLevel Alias: ucl

SQL Compare’s comparisons vary according to the version of SQL Server. By default, it works out the correct comparison by reading the version of SQL Server, but individual ‘legacy’ databases may be hosted on a recent version of SQL Server but set at a lower compatibility level. Uses a database’s compatibility level instead of the SQL Server version.

Auto-map similar columns

NoAutoColumnMapping Alias: nacm

This command-line option does the reverse. By default, SQL Compare copes with slight changes by making an educated assumption when matching columns that are similar but not identical. It places columns with compatible data types and similar names in mapped tables so that the user doesn’t have to. Setting this option in a project will cause SQL Compare to only map identical columns, so the user must map these columns manually from the Table mapping tab

Throw on file parse failed

ThrowOnFileParseFailed Alias: tofpf

Throws an exception when the process of 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.

Force column order

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.

Use case-sensitive object definition

UseCaseSensitiveObjectDefinition Alias: cs, Legacy alias: CaseSensitiveObjectDefinition

When a database is set to have a case-sensitive collation, it is possible, though inadvisable, for objects, columns, indexes and so on to be distinguished by character case differences as well as character differences. Although against the SQL Standards, it is possible to have keywords ATable and atable for different objects. By setting this option, comparisons of object, column or index names and the contents of routines become case sensitive. 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.

Ignore certificates, symmetric keys, and asymmetric keys

IgnoreCertificatesAndCryptoKeys, Alias: icc

SQL Server severely restricts access to certificates, symmetric keys, and asymmetric keys so even if it was a good idea to compare them, SQL Compare is severely limited in what it can do. For this reason, if certificates, symmetric keys, and asymmetric keys are selected for deployment, only the permissions are deployed. The rest must be done separately.

Ignore comments

IgnoreComments Alias: icm

This option will leave out comments from a comparison of the code within modules (procedures, functions, views and so on) so if the code is the same but the comments different, the module isn’t part of the deployment. All Comments will still appear in the deployment scripts for all deployed objects.

Ignore migration scripts for databases

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.

Ignore identity property on columns

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

IgnoreIdentitySeedAndIncrementValues Alias: isi Legacy alias: IgnoreIdentitySeedAndIncrement

It is possible to specify the start number (seed) and increment value in IDENTITY columns. This option will ignore them in comparisons, but they won’t be ignored when the databases are deployed.

Ignore system named constraint and index names

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 SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements

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.

Script options

Add database USE statement

AddDatabaseUseStatement alias: odus

This would prevent you executing the deployment if the database is a different database, by adding a USE statement specifying the name of the database as the first action of the script. This ensures that the connection executes all the batches of the script in that specified database. This must be off if you wish to generate a script that will synchronize several databases under different names at the same version.

Add object existence checks

ObjectExistenceChecks Alias: oec

This adds code for each DDL statement that ensures that DDL ALTER or DROP statements are only executed on an object if it exists, and that CREATE statements are only executed if an object doesn’t exist. These aren’t logically necessary of the target is unchanged from the result of the comparison but may become necessary if the script is used more widely or is used more than once.

Database Project compatible script folder output

Cannot be specified on the command line.

The Redgate standard for storing object-level scripts is simpler and more forgiving than that of a Visual Studio database project. However, when using SQL Compare with a Visual Studio Database project. This option will tell SQL Compare to create or maintain a script folder that conforms to the style used by the most recent Visual Studio database project type.

Deploy all dependencies

IncludeDependencies Alias: incd

Because it is possible with SQL Compare to select just part of a database for a deployment script, it would be perfectly possible without this option to generate a script that cannot be executed successfully. If, for example, a view depends on three tables, then it cannot be deployed unless these three tables are deployed with the view. With this option, the objects that are referenced by the objects you select are also included in the script whether you selected them or not.

Do not add error handling statements to deployment scripts

NoErrorHandling Alias: neh

The error handling in SQL Compare scripts is important for preventing the database being in an indeterminate state, at the end of a deployment. If you deploy using SQL Compare, the deployment is always stopped when an error is encountered, and the transaction is rolled back. However, when you are testing a deployment and an error happens, it can take longer to be certain what caused the error. If you select this option, and remove the use of transactions, then there is no error handling and the script will relapse to the default error-behaviour and may or may not stop executing after encountering an error. Because error handling and the use of transactions are part of the same error-handling approach, 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

DoNotOutputCommentHeader Alias: nc

The Scripts generated by SQL Compare have initial block comments that specify the source and target database, the date that the script was generated, and the version of SQL Compare. When this option is specified, comments and comment headers are not included in the output deployment script.

Do not use transactions in deployment scripts

NoTransactions Alias: nt

Removes transactions from the deployment SQL script. This option is closely related to the option to remove error handling for debugging purposes, but it can be useful in edge cases with database builds that involve memory-optimized objects (memory-optimized tables, natively compiled stored procedures or memory-optimized table types). If this option isn’t specified and the deployment script fails, the script is rolled back to the start of the failed transaction, thereby leaving the target database in its former state. If this option is specified, the script isn’t rolled back, and the database is left in an indeterminate state.

Use DROP and CREATE instead of ALTER

DropAndCreateForReRunnableScripts, Alias: dac

This only applies to Views, Stored Procedures, Functions, Extended Properties, DDL Triggers and DML Triggers.

The script first checks to see if an object of that name already exists and if it does exist, drops it. Then it is re-created Any specific permissions to the object will be lost, and have to be added. If you select this option, the ‘Add object existence checks’ option is also selected, as the script would fail otherwise.

Ignore constraint and index names

IgnoreConstraintAndIndexNames Alias: icn Legacy alias: IgnoreConstraintNames

This option will prevent SQL Compare considering a difference in 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, however, be ignored when the databases are deployed. This does not ignore names in view content, for example in a table index hint. If the target is a scripts folder, SQL Compare will deploy tables using system-named constraints even if the source or target uses names

Deployment Options

Disable DDL triggers during deployment

DisableAndReenableDdlTriggers Alias: drd

DDL Triggers get fired on DDL events such as CREATE, DROP, ALTER and are used for tracking and auditing DDL changes in production systems. This can cause unnecessary alerts and other problems when you run the deployment. The nature of these problems vary according to the actions that are coded in the triggers. If you select this option, then all DDL triggers are disabled at the start of the script and enabled at the end. This allows deployment to proceed without triggers.

Do not use ALTER ASSEMBLY to change CLR objects

DontAlterAssembly Alias: daa Legacy alias: UseClrUdtToStringForClrMigration, uclr

If CLR objects are included in the deployment, the assembly will be rebuilt instead of altered either if a parent assembly needs to be re-created of if the assembly metadata between the assembly in the source and target is different. If SQL Compare cannot compare the metadata, it leaves a log entry stating why. It will also decide to rebuild if either or both assemblies contain no files or are less than 96 bytes in length. This option avoids the option of using ALTER ASSEMBLY by forcing two rebuilds of the table with conversion to and from strings to update the CLR objects. This option affects the deployment only.

Enable SQL Monitor integration

NoDeploymentLogging, Alias: ndl

This allows SQL Monitor to detect a deployment and mark it on the SQL Monitor timeline. It allows any changes due to the deployment to be spotted quickly. When a deployment is successful, the script appends a logging statement to add the deployment details to the SQL Server Log. This, in turn, allows SQL Monitor to detect the deployment and mark it on the timeline for performance data.

Encryption options

Add WITH ENCRYPTION

AddWithEncryption, Alias: oec

Adds a WITH ENCRYPTION option in the CREATE or ALTER script for all routines (stored procedures, functions, views, and triggers) that are included in the deployment. This causes them all to be encrypted. When SQL Compare creates a Redgate snapshot, this option is ignored, and WITH ENCRYPTION is not saved in the snapshot. This encryption method only prevents casual attempts at inspection, so also consider Role-based security, applying only the required privileges to users.

Decrypt encrypted objects

DecryptEncryptedObjects, Alias: deo

By default, SQL Compare must assume that all encrypted objects of the same type and name are different. However. when this option is specified, and you use a login with sysadmin permissions, SQL Compare decrypts all the encrypted modules (functions, procedures, views and so on) in databases that use the WITH ENCRYPTION option. When SQL Compare saves either a Redgate snapshot or a scripts folder, this option is set by default, so that all encrypted objects are decrypted. Selecting this option can occasionally cause slower performance when comparing large databases that have just a few encrypted objects. However, by un-setting this option, SQL Compare must assume that all encrypted text objects are different, but because it cannot generate the DDL code, they can’t be deployed. This option does not work with Azure SQL databases

Ignore WITH ENCRYPTION

IgnoreWithEncryption, Alias: iwe

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

Exclusion/Inclusion options

Consider next filegroups in partition schemes

ConsiderNextFilegroupInPartitionSchemes, Alias: cfgps

A partition scheme maps the partitions of a partitioned table or index to filegroups. The number and domain of the partitions of a partitioned table or index are determined in a partition function. A table in two deployment environments can be partitioned on the same partition schema, but the underlying partition functions can be specifying different values. 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.

Ignore authorization on schema objects

IgnoreSchemaObjectAuthorization, Alias: isoa

This is used to prevent authorization changes of any schema objects from being seen as a change and so affecting what is stored in source control and used in deployments. Otherwise, the authorisations in source control will overwrite the authorization of a deployment environment, such as Test or UAT. When this is set, SQL Compare ignores authorization clauses on schema-qualified objects when comparing and deploying databases, but not the schema itself.

Ignore bindings

IgnoreBindings, Alias: ib

This instructs SQL Compare to Ignore bindings on columns and user-defined types that are made with sp_bindrule and sp_bindefault clauses when comparing and deploying databases. Rules and Sybase-style defaults are still used though they are outside the SQL Standard.

Ignore change tracking

IgnoreChangeTracking, Alias: ict

Ignores change tracking when comparing and synchronizing databases. If you are using change tracking on a production database you need to set this option to prevent having to manually exclude tables and schemas from comparisons, and to have change-tracking included in synchronization scripts

Ignore check constraints

IgnoreCheckConstraints, Alias: ich Legacy alias: IgnoreChecks

Ignores check constraints when comparing and deploying databases. If you set this, check constraints are stripped from the scripts.

Ignore collations

IgnoreCollations, Alias: ic

Collations provide sorting rules, case sensitivity, and accent sensitivity of strings of characters. They dictate the code page and corresponding characters that can be represented. This option tells SQL Compare to ignore collation orders on character datatype columns when comparing and deploying databases. Collation information is not included in the scripts, so collation will default to the collation of the target.

Ignore data compression

IgnoreDataCompression, Alias: idc

Ignores the page and row compression attributes for tables and indexes. When the IgnoreFilegroups option is also 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

IgnoreDatabaseAndServerNameInSynonyms, Alias: idsn Legacy aliases: IgnoreDatabaseAndServerName, IgnoreDatabaseName, idn

Ignores the database and server name in synonyms when comparing databases, so that each deployment environment can configure their actual object reference without causing a comparison to flag a difference or overwrite a setting during a synchronization.

Ignore DML trigger order

IgnoreTriggerOrder, Alias: ito

A DML trigger is a special type of stored procedure associated with a table or view that is automatically executed when a data manipulation language (DML) event happens that affects the parent. If there is more than one trigger associated with a particular DML operation. We can specify that a trigger is executed either first or last using procedure sp_settriggerorder. There can be only one first or last trigger for each statement on a table. Specify this option to ignore the trigger order for DML triggers when comparing and deploying databases so that a change in order does not cause a difference. The DDL trigger order isn’t affected.

Ignore DML triggers

IgnoreTriggers, Alias: it

Ignores DML triggers when comparing and deploying databases. Sometimes, production databases have special DML triggers for audit purposes that need to be kept separate from the development database and source control.

Ignore dynamic data masking

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

IgnoreEventNotificationsOnQueues, Alias: iqen Legacy alias: IgnoreQueueEventNotifications

Ignores the event notification on queues when comparing and deploying databases. This is a concern where even notification is used for auditing.

Ignore extended properties

IgnoreExtendedProperties, Alias: ie

This option ignores extended properties on objects and databases when comparing and deploying databases. This will mean that the extended properties, including comments and documentation, won’t be compared and will not be deployed to the target. However, the sensitivity classification extended properties on SQL Server 2017 and older will be deployed.

Ignore filegroups, partition schemes and partition functions

IgnoreFileGroupsPartitionSchemesAndPartitionFunctions, Alias: ifg Legacy alias: IgnoreFileGroups

If you choose this option, filegroup clauses, partition schemes, and partition functions on tables and keys are ignored when comparing and deploying databases. Partition schemes and partition functions aren’t displayed in the comparison results.

Ignore fill factor and index padding

IgnoreFillFactor, Alias: if

Ignores the settings for the fill-factor and index padding in indexes, when comparing and deploying databases. This will lead to the default fill-factor, usually 100, being used when creating or rebuilding indexes.

Ignore foreign keys

IgnoreForeignKeys, Alias: ifk Legacy alias: IgnoreKeys, ik

Ignores foreign keys when comparing and deploying databases. This will lead to foreign keys being left out of CREATE scripts

Ignore full-text indexing

IgnoreFullTextIndexing, Alias: ift

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

Ignore indexes

IgnoreIndexes, Alias: ii

When this is set, indexes, unique constraints, and primary keys are ignored when comparing and deploying databases. In the case of memory-optimized tables or when the target is a script folder, index differences will still be deployed if there are other differences between the tables.

Ignore INSTEAD OF triggers

IgnoreInsteadOfTriggers, Alias: iit

INSTEAD OF triggers on tables or views cause their associated DML operation to be skipped, and they just execute the code within the trigger. They are used most often to allow applications to insert into, UPDATE or DELETE from views. This option Ignores INSTEAD OF DML triggers both when comparing and deploying databases.

Ignore LOCK properties of indexes

IgnoreLockPropertiesOfIndexes, Alias: ilpi Legacy alias: IgnoreIndexLockProperties, iilp

When you rebuild an Index in SQL Server, you can specify that SQL Server should acquire Row and Page Level Locks when accessing data for reading and writing. This is done with the ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS options. These are ignored when comparing and deploying databases.

Ignore NOCHECK and WITH NOCHECK

IgnoreNocheckAndWithNocheck, Alias: inwn

Ignores the NOCHECK and WITH NOCHECK arguments on foreign keys and check constraints. This affects whether they are disabled or not, and whether the data is checked when they are enabled. When this option is specified, SQL Compare will always apply constraints, even when NOCHECK and WITH NOCHECK are enabled, so they can be subsequently be enabled.

Ignore NOT FOR REPLICATION

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

IgnoreNullability, Alias: in

Ignores the nullability of a datatype (i.e. NULL /NOT NULL) when comparing columns in tables.

Ignore performance indexes

IgnorePerformanceIndexes, Alias: ipi

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

Ignore permissions

IgnorePermissions, Alias: ip)

Ignores permissions on objects when comparing and deploying databases.

Ignore replication triggers

IgnoreReplicationTriggers, Alias: irpt)

Ignores replication triggers when comparing and deploying databases.

Ignore sensitivity classifications

IgnoreSensitivityClassification, Alias: is

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

Ignore square brackets in object names

IgnoreSquareBrackets, Alias: isb

If object names have been escaped with square brackets, this option ignores them in comparing names.

Ignore statistics

IgnoreStatistics, Alias: ist

Statistics are essential in providing information about the distribution of data to help the query optimiser determine the best strategy. If AUTO_CREATE_STATISTICS is ON, the Query Optimizer can create statistics on individual columns in the query predicate when necessary to improve cardinality estimates for the query plan. in a few cases, you need to create additional statistics with CREATE STATISTICS or modify the query design to improve query performance. If this option is set, these extra statistics are ignored 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

IgnoreStatisticsNorecompute, Alias: isn

Ignores the STATISTICS_NORECOMPUTE property on indexes and primary keys. Turning auto-update statistics off might be a good idea in exceptional circumstances. if your current statistics are a good representation of the data, and you know that resampling would most likely omit some essential parts of your statistics.

Ignore tSQLt framework and tests

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

IgnoreUserProperties, Alias: iup

If you specify this option, users’ properties are ignored, and only the username 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>

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

IgnoreWhiteSpace, Alias: iw

Ignores whitespace differences (newlines, tabs, spaces, and so on) when comparing the content of the definitions, SQL code, in routines (procedures, views functions triggers etc).

Ignore WITH element order

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. The order of these isn’t significant so it is unlikely that you’d want to alter a procedure trigger or function if the order is different.

Ignore WITH NOCHECK

IgnoreWithNocheck, Alias: iwn

Ignores the WITH NOCHECK argument on foreign keys and check constraints. This NOCHECK option sets whether the constraint or key is checked when it is enabled. If no check is made, then the constraint is flagged as untrusted so cannot be used by the query optimiser. If you want to ignore both NOCHECK and WITH NOCHECK arguments, use IgnoreNoCheckAndWithNoCheck. Foreign keys or constraints that are disabled aren’t ignored

Summary

SQL Compare was first created many years ago, and its evolution has been largely dictated by the requests and requirements of its users. To be useful to as many developers and DBAs as possible, it has developed a rich undergrowth of options. Some of them deal with the way that scripts are written, many of them determine what properties of SQL Server objects within a database represent a difference, and a large body of them determine what SQL Server objects are ignored entirely and left out of synchronization scripts. Some are extraordinarily useful, many or handy for occasional use, and a few have brought comfort to a handful of users struggling with an unusual problem.

The Redgate tools that use these options directly, SQL Compare and SCA, allow the user a great deal of flexibility in the ways they can use the tools, and so it pays to become familiar with them.