Schema Comparison for SQL Server version 4 - New and Updated Features

This page contains information about the new features, enhancements and fixes included in the version 4 of the Schema Comparison for SQL Server. To view the release notes for other versions, click here.
‍

• User Interface
• Supported SQL Server Versions
• Supported Database Objects
• New Comparison Options
• New Scripting Options
• New Execution Options
• Changes to Existing Options

User Interface

• The user-interface has been upgraded with a completely new look and simplified workflow.
• The scripting functionality that existed in the old version, separate from the comparison, has been dropped. It did not add much value to the primary function of the tool and generated confusion.
• Schema comparison sessions are saved in the workspace. This allows the users to rerun common comparison scenarios with just one click.
• All comparison operations have been implemented asynchronously, which keeps the interface very responsive even when lengthy operations are being performed on the background.
• Script execution supports cancellation and implements a progress window that displays the percentage completed.
• The comparison results grid has been improved significantly. It includes different types of grouping, filtering by status or object type, ability to drill down on details of tables and views tailored to the object's structure, search functionality and more.
• The component that displays script differences has been improved. The script that synchronizes a particular object now includes all the required dependencies.
• The structure of synchronization script has been simplified. The previous versions wrapped each object fragment in a transaction, which often made the script very hard to follow. Version 4 includes one single transaction that starts at the beginning of the script and commits at the end.
• Script warnings have been improved and expanded. They are more descriptive and reported in groups organized by type of warning. There are warnings, for example, related to potential data loss (when the column length changes), dropped columns, unsupported objects and more.
• Logging has been improved as well. There are now detailed logs generated by API errors, UI errors and script execution.

Supported SQL Server Versions

Supported Database Objects

• CLR Procedures: partially supported by the previous version
• CLR Triggers: partially supported by the previous version
• CLR Types: partially supported by the previous version
• Full-Text Indexes on Views: not supported by the previous version
• User-Defined Table Types: not supported by the previous version
• Sequences: not supported by the previous version
• Full-Text Stop List: not supported by the previous version
• Search Property List: not supported by the previous version
• Extended Properties: partially supported in the previous version
• Permissions: includes the new permissions introduced in SQL Server 2012

New Comparison Options

• Include Dependant Objects: Indicates whether dependencies should be included in the synchronization script.
• Ignore Object Name In Definition: When selected, forces Schema Compare to parse the SQL definition of views, functions, procedures, triggers and not flag objects as different when the name in definition is different.
• Standardize Name In Definition: When selected, changes the object name in definition to the standard format: [schema.[name].
• Compare Table Change Data Capture: Indicates whether Table change data capture should be compared.
• Compare Table Lock Escalation: Indicates whether Table Lock Escalate property should be compared.
• Compare Index Row Locks: Indicates whether the index ROW_LOCKS property should be compared.
• Compare Index Page Locks: Indicates whether the Index PAGE_LOCKS property should be compared.
• Compare Index Column Order: Indicates whether the ASC or DESC property of index's columns should be compared.
• Compare "Not For Replication": Indicates whether the "Not For Replication" property should be compared. It effects identity columns, check constraints and foreign keys.
• Ignore Default Constraint Name: Replaces the previous option: Compare Default Constraint Name.
• Ignore Check Constraint Name: Indicates whether the name of the check constraint should be ignored. This affects only checks on columns. Check constraints that have table-scope are not affected.
• Compare Column Collation: Indicates whether the column COLLATION property should be compared.
• Compare Identity: Indicates whether the column's identity property should be compared.
• Compare Identity Seed: Indicates whether the column's identity seed property should be compared.
• Compare Identity Increment: Indicates whether the column's identity increment property should be compared.
• Compare Nullability: Indicates whether the column's NULL/NOT NULL property should be compared.
• Compare Diagram Objects: Indicates whether the objects created to support SQL Server diagram should be compared or ignored. Diagram is a feature embedded in the database and supported by SQL Server. By default diagram objects are ignored.
• Compare Replicated Table: Indicates whether the tables that participate in SQL Server replication should be compared. By default replicated tables are excluded.
• Alter Views With Drop and Create: Synchronizes view differences using DROP/CREATE instead of the ALTER statement.
• Alter Procedures With Drop and Create: Synchronizes stored procedures using DROP/CREATE instead of the ALTER statement.
• Alter Functions With Drop and Create: Synchronizes functions using DROP/CREATE instead of the ALTER statement.
• Compare Sequence Start Value: Indicates whether the "start value" of a sequence object should be compared and synchronized.
• Compare Sequence Min Value: Indicates whether the "min value" of a sequence object should be compared and synchronized.
• Compare Sequence Max Value: Indicates whether the "max value" of a sequence object should be compared and synchronized.

New Scripting Options

• Generate Use Database Statement: Includes a "USE <target_database>" in the synchronization script.
• Script ANSI_NULLS Setting: Indicates whether the SET ANSI_NULLS should be scripted.
• Script QUOTED_IDENTIFIER Setting: Indicates whether the SET QUOTED_IDENTIFIER should be scripted.
• Script ANSI_PADDING Setting: Indicates whether the SET ANSI_PADDING should be scripted.
• Script Filegroup: Indicates whether the filegroup clause should be scripted. It affects tables, indexes, primary keys and unique constraints.
• Script Table Change Tracking: Indicates whether the table change tracking should be scripted.
• Script Table Change Data Capture: Indicates whether the table change data capture should be scripted.
• Script Table Lock Escalation: Indicates whether the table lock escalation property should be scripted.
• Script Constraints With NOCHECK: Indicates whether check constraints and foreign keys should be created with NOCHECK clause in order to overcome problems with data integrity violation.
• Script Data Compression: Indicates whether date compression should be scripted. It affects tables and indexes.
• Script Collation: Indicates whether the column's collation property should be scripted.
• Script New Columns As NULL: Scripts the newly added column with "NULL" attribute in order to prevent columns from being rejected when the target table contains data.
• Script Full-Text Catalog Path: Indicates whether the path where the full-text catalog is created should be scripted. The path might not exist in the target server, so in some cases it is better to omit it and let SQL Server create the catalog in the default location.
• Script Sequence Start Value: Indicates whether the sequence start value should be scripted.
• Script Sequence Min Value: Indicates whether the sequence min value should be scripted.
• Script Sequence Max Value: Indicates whether the sequence max value should be scripted.

New Execution Options

• Execute Transactional Script: Indicates whether the sync script should run as a transactional script.
• Log Script Execution: Logs script fragments during the execution in a log file.
• Continue Execution On Error: Indicates whether the execution should continue when an error occurs.
• Transaction Size: Determines the max transaction size when the sync script is executed. When the max size is reached, the current transaction commits and a new one starts. By default schema transaction in unlimited.

Changes to Existing Options

• Bind Rules to Columns: Dropped; rules are always bound to columns.
• Bind Rules to User-Defined Data Types: Dropped; rules are always bound to types.
• Bind Rules with futureonly option: Dropped; rules are always bound with futureonly.
• Bind Defaults to Columns: Dropped; defaults are always bound to columns.
• Bind Defaults to User-Defined Data Types: Dropped; defaults are always bound to types.
• Bind Defaults with futureonly option: Dropped; defaults are always bound with futureonly attribute
• Build view dependencies: Dropped; dependencies are now integrated with the comparison and cannot be disabled
• Build procedure dependencies: Dropped.
• Build function dependencies: Dropped.
• Generate transactional script: This option is left unchanged, but a new one is added that affect the execution of the sync script: Execute Transactional Script
• Raise schema events: Dropped.
• Include encrypted objects: Dropped.
• Ignore ANSI_NULLS statements: It is replaced by two options: "Compare ANSI_NULLS Setting" and "Script ANSI_NULLS Setting".
• Ignore QUOTED_IDENTIFIER statements: It is replaced by two options: "Compare QUOTED_IDENTIFIER Setting" and "Script QUOTED_IDENTIFIER Setting".
• Do not script default constraints with columns: Dropped
• Include column ordinal: Changed to "Compare Column Ordinal".
• Alter assembly with drop/create: Dropped.
• Ignore column collation: It is replaced by two options: "Compare Column Collation" and "Script Column Collation".