Script comparison and synchronization options in ApexSQL Source Control

Applies to

ApexSQL Source Control

Summary

The purpose of this article is to explain the SQL compare and synchronization options ApexSQL Source Control provides for comparing objects.

Description

ApexSQL Source Control provides the ability to extensively configure what will be seen as a change made against an object when compare its database and source control state.

Checking these options will ignore the made change and as a result, the changed object will not be shown in the Action center tab.

These options can be initially set when a database is being linked to source control, in the Source control setup window, under the Script options tab:

Additionally, this set of options can be found in the Options window, where they can be changed at any time:

The SQL compare and synchronization options are divided into categories regarding the parts of the script they affect:

• Attributes for script-based objects
• General attributes
• Objects
• Table attributes
• Synchronization options
• Optional output elements
• Owners in synchronization script

Attributes for script-based objects

Developers may work on their scripts in various versions of SSMS or different platforms, like Azure, and commit changes to a central repository.

When taking into consideration the different scripting styles of each developer or comments they may add to their scripts for their purposes, then source control could detect a great number of differences between local objects and the ones on the repository. These “false positives” contribute nothing to the overall definition or functionality of those objects. To ignore these, ApexSQL Source control provides options to ignore:

• Case – Ignore case differences in the scripts
• Comments – Ignore all comments
• White space – Ignore all white space characters, including line feed, carriage return, space, and tab
• WITH ENCRYPTION – Ignore difference in WITH ENCRYPTION attribute

General attributes

Here the user can exclude various attributes of objects from SQL compare in the Action center tab, before committing them to a repository:

• ANSI option – Ignore ANSI database-level settings that may have an impact on the behavior of compared objects. These include ANSI_NULLS and QUOTEDJDENTIFIER
• Authorization – Ignore authorization on schema-qualified objects during comparison
• Bound rules/defaults – Column-level and user-defined datatype constraints and default value rules not considered during comparison
• Case in names – Case differences in objects, columns, parameters, and index names will not be considered during the comparison
• Extended properties – Differences in extended properties not considered during comparison
• Permissions – Ignore user-level permissions (Select, Insert, Update) for compared objects

Quick tip: Note the difference between “Case” and “Case in names”. The Case refers to clauses while the Case in names refers to the actual names of database objects.

Objects

With these options, certain objects can be excluded from the SQL compare in total:

• Replication triggers – Ignore triggers created by SQL Server for replication purposes
• Server and database names in synonyms – Ignore server name and database name parts (if present) of name that a synonym refers to
• System objects – Ignore all system objects
• Trigger order – Ignore DML trigger order, such as FIRST DELETE or LAST INSERT. DDL trigger order is not affected

When check to be ignored, these objects will not be shown in the Action center tab.

Table attributes

There is a long list of attributes that users can choose to ignore when the SQL compare in the Action center tab is in question:

• ASC/DESC for index columns – Ignore sort order of index columns
• Check/default constraints – Ignore check/default constraints – Column-level data check constraints and default values not considered in comparisons
• Change tracking – Ignore change tracking option value while comparing tables and indexes
• Collations – Ignore server-level collation differences between tables. This applies only to structure and not to collation-caused differences in actual underlying table data
• Column order – Specific column order between tables is ignored. Other column differences still considered, but their ordinal position not factored in
• Constraint names – Ignore constraint names. Constraint name differences not considered in comparisons
• Dataspaces – Server-level filegroups (SQL 2000) and dataspaces (SQL 2005, SQL 2008) referenced in table structures not considered in comparisons
• Data compression – Ignore Data compression option value while comparing tables and indexes. Note that if ‘Ignore dataspaces’ option is selected, data compression is ignored automatically
• FILEFACTOR attribute – Ignore FILLFACTOR property of indexes, primary keys and unique constraints
• Foreign key actions for ON DELETE/ON UPDATE – Ignore ON DELETE and ON UPDATE actions defined on foreign keys
• Foreign keys – All facets of a foreign key: name, composition and properties ignored when comparing tables
• Fulltext indexes – Fulltext indexes present on tables will be ignored in comparison
• Identity attribute of columns – Ignore identity property on columns. The option used only when comparing tables. Properties will not be ignored in the script preview window and in the synchronization script
• Identity seed and increment – Ignore identity seed and increment values for identity property of columns when comparing tables. Properties will not be ignored in the script preview window and in the synchronization script
• Indexes – Indexes and index properties will not be considered in comparisons
• Index names – Names of table indexes will be ignored in comparisons (other index properties will be considered, unless selected otherwise)
• Lock escalation – Ignore lock_escalation property of tables
• Next filegroups in partition schemas – Ignore next filegroups in partition schemes during comparison
• NOT FOR REPLICATION attribute of constraints, identities, foreign keys and triggers – Ignore NOT FOR REPLICATION argument on constraints, identities, foreign keys
• PAGE LOCK and ROW LOCK properties of indexes – Ignore PAGE LOCK and ROW LOCK attributes of indexes
• Primary keys – Primary key constraints and primary key properties (name, composition) present on tables will not be considered in comparisons
• Secondary index attributes – Ignore PADJNDEX, IGNORE_DUP_KEY, STATISTICS_NORECOMPUTE properties of indexes
• Statistics – Statistical analysis settings for tables not considered in comparisons
• Unique constraints – Differences in column-level UNIQUE constraints will not be considered. All other constraint differences considered unless specified otherwise
• User defined data types – Differences in facets of UDTs (name, composition, properties) will not be considered. Does not affect overall column comparison and checks for the column name, order, and other properties
• WITH NOCHECK property of constraints – Ignore WITH NOCHECK attribute on foreign key and check constraints. Disabled foreign keys or check constraints not ignored
• ANSI padding – Ignore ANSI padding ON or OFF

Synchronization options

These options affect the creation of the synchronization script in the Get changes from the repository window, shown when in the Action center tab for the listed changes the acton is set to be ‘’Commit to database’’. The following options are available:

• Add WITH ENCRYPTION – The WITH ENCRYPTION clause is added to SQL statements in a synchronization script
• Check for object existence with IF EXIST/IF NOT EXIST – The IF EXISTS statement is added to the synchronization script to verify the object exists before the DROP, CREATE or ALTER statements
• Disable DDL triggers – DDL triggers that may cause problems during synchronization are disabled in a destination data source. The option is available only for SQL Server 2005 and up
• Include transaction handling in synchronization script – Error handling statements with Rollback/Commit commands depending on the success or failure of the synchronization are added to the synchronization script
• Include dependent objects – Dependent database objects will be included in the synchronization script, even if they were unchecked for the synchronization process
• Script USE for database – The USE statement is added to a synchronization script
• Use DROP/CREATE statements instead of ALTER for different procedures, functions, triggers, and views – The DROP/CREATE statements are used in a synchronization script when a database object (procedure, function, trigger, or view) is to be created in a destination data source. The ALTER statement is used otherwise
• Use DROP/CREATE statements instead of ALTER for different tables – The DROP/CREATE statements are used in a synchronization script when a table is to be created in a destination data source. The ALTER statement is used otherwise

Optional output elements

With these options, it is possible to add certain informative fields to the synchronization script.

Users may choose to add automatically generated comments and print statements. Apart from that, a header may be added, that can include the author, date and time of creation, and legal information of the script. Available options are:

• Header – Add header at beginning of created synchronization script. Under the header the Author, Date and time, and Legal options are present
• Comments – If this option is selected, comments statements will be added to the synchronization script
• Print statements – Select to add PRINT statements to the synchronization script

After all the above-listed options are checked, the synchronization script will look like this:

Owners in synchronization script

These options dictate whether the owners of objects are included in the synchronization script or not. This can be particularly helpful when comparing identical objects which are under different schemas in the local database and the repository. The user can force include owners, or choose to include them only when they are different from the database owner or the current user.

Included owners in the synchronization script

Excluded owners in the synchronization script

Tooltips

From the ApexSQL Source Control 2021 version, tooltips for all Ignore comparison options are clickable and every tooltip contains an example for the chosen option:

FAQs

Q: Can the SQL compare and synchronization options be set differently for every linked database?

A: Yes, when more than one database is linked to source control, the user can choose which database’s SQL compare and synchronization options they want to edit in the Options window:

Q: Do these options affect other users?

A: No, script options only affect the user’s machine, so each developer in a team can set them up to define what will be defined as a difference in the SQL compare process between the linked database and the source control.

Q: Do SQL compare and synchronization options have to be set every time?

A: No, script options are saved locally for each database, for as long as it is linked, regardless of the connected users. When a database is unlinked, script options will have to be set up again, upon the new linking process.

Q: Do I have to set SQL compare and synchronization options for every database separately?

A: No. Set SQL compare and synchronization options for one linked database and click the Save as my defaults button. For every other linked database, choose it from the drop-down list, click the My defaults button and click the OK button, and the saved SQL compare and synchronization options will be applied to the chosen database as well.

Q: How I can be sure if the checked options are the ones I need?

A: Every option in the Script options tab has a clickable tooltip, which will provide an example of the listed option.