You are examining schema differences between two copies of a SQL Server database. But which database objects should be included in the schema comparison? Are all differences significant? Is it right, for example, to list or deploy differences in comments, indexes, or constraints? How do you deal with issues such as system-named constraints, or differences that arise because the collation setting isn’t the same in the databases being compared? How should you handle differences in encryption settings between two databases? When answering these questions, so much depends on the style, design, or type of database. It also depends on whether you’re deploying changes from development to production systems, or vice versa.
Customizing schema comparisons and deployment scripts
If you’re generating a script to capture production changes into your source control system, or development database, then there will be parts of the metadata that are strictly outside the responsibility of the development and shouldn’t even be in source control; you’ll need to exclude those objects from comparisons. For example, you’ll probably want to exclude synonyms, partition schemes and partition functions, and users to name just a few. Conversely, when those classes of objects sneak into development systems, regardless, and then get changed and included into deployment script for production, it can cause all sorts of problems. There are a host of features in SQL Server, and more are being introduced on every release. With some of these features, it is by no means clear-cut that the code is suitable for a development version of a database.
If you must sort all of this out manually, it’s a laborious, error-prone and time-consuming task. A database schema comparison tool like SQL Compare makes light work of the task. It does precisely what its name suggests: it compares SQL 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.
To help it deal with all the types of issues described above, it has, over the years, accrued a large number of options that 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.
Database schema comparison options: a reference
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:
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.
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:
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:
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:
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
Add database USE statement
AddDatabaseUseStatement alias: adus
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
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:
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
Disable DDL triggers during deployment
DisableAndReenableDdlTriggers Alias: drd
DDL Triggers get fired on DDL events such as
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:
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.
Add WITH ENCRYPTION
AddWithEncryption, Alias: we
WITH ENCRYPTION option in the
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
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
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
WITH ENCRYPTION statements on triggers, views, stored procedures, and functions. This option overrides
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.
IgnoreBindings, Alias: ib
This instructs SQL Compare to Ignore bindings on columns and user-defined types that are made with
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:
Ignores check constraints when comparing and deploying databases. If you set this, check constraints are stripped from the scripts.
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:
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
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:
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:
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:
Ignores foreign keys when comparing and deploying databases. This will lead to foreign keys being left out of
Ignore full-text indexing
IgnoreFullTextIndexing, Alias: ift
Ignores full-text catalogs and full-text indexes when comparing and deploying databases.
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
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,
DELETE from views. This option Ignores
DML triggers both when comparing and deploying databases.
Ignore LOCK properties of indexes
IgnoreLockPropertiesOfIndexes, Alias: ilpi Legacy alias:
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_PAGE_LOCKS options. These are ignored when comparing and deploying databases.
Ignore NOCHECK and WITH NOCHECK
IgnoreNocheckAndWithNocheck, Alias: inwn
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 are enabled, so they can be subsequently be enabled.
Ignore NOT FOR REPLICATION
IgnoreNotForReplication, Alias: infr
REPLICATION option on foreign keys, identities, check constraints and triggers. If you specify this option, the
REPLICATION statement won’t be displayed in the object creation script for foreign keys, identities, and check constraints. For triggers, the
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
REPLICATION statement in their definition will automatically be flagged as
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) 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.
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.
IgnoreStatistics, Alias: ist
Statistics are essential in providing information about the distribution of data to help the query optimiser determine the best strategy. If
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
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
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.
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
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 arguments, use
IgnoreNoCheckAndWithNoCheck. Foreign keys or constraints that are disabled aren’t ignored
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.
Was this article helpful?