Table Data Verification in SQL Development Tools for Safe Schema Synchronization

Preserving table data after schema synchronization is one of the key requirements for reliable database deployment. Modern SQL development tools must minimize the risk of accidental data modification during schema changes and deployments.

To address this challenge, dbForge Schema Compare for SQL Server provides several built-in protection mechanisms, including:

• warnings about possible data loss
• automatic restoration of data in recreated objects
• table data verification after synchronization

The table data verification mechanism allows users to detect whether table data was unintentionally modified during schema synchronization.

Among SQL development tools focused on database deployment automation, this approach helps improve synchronization reliability and reduce risks during schema updates.

How Table Data Verification Works

Table data verification is based on calculating hash values for table data before and after synchronization.

The synchronization workflow in dbForge Schema Compare for SQL Server consists of the following stages:

• Calculate hashes for table data and store them in a temporary repository.
• Synchronize database objects.
• Recalculate hashes for synchronized tables.
• Compare the calculated hash values.
• Generate warnings if differences are detected.

If the hash values do not match, dbForge Schema Compare for SQL Server reports a table data verification failure and recommends checking the data manually.

Hashing Algorithm

dbForge Schema Compare for SQL Server uses the SHA1 hashing algorithm for table data verification.

SHA1 generates relatively long hash values, reducing the probability of collisions when processing large datasets and tables with multiple columns.

Many SQL development tools implement validation mechanisms, but hash-based verification provides an additional layer of confidence during synchronization operations.

Columns Excluded from Hash Calculation

A hash is not calculated for the following column types:

• newly added columns, because they do not yet contain data
• dropped columns, because their data is removed during synchronization
• synchronized columns with incompatible data type changes that lead to data loss
• computed columns
• IDENTITY columns
• TIMESTAMP columns
• ROWGUID columns

These columns are excluded because their values are either automatically generated or intentionally modified during synchronization.

Row Processing and Sorting

To calculate table hashes, dbForge Schema Compare for SQL Server performs folding of row hashes for all rows in a table.

The order of processed rows is determined by identifying columns defined in the table.

Row sorting is performed only if the table meets the following conditions:

• the table contains a PRIMARY KEY or UNIQUE KEY that uniquely identifies rows
• the key remains unchanged after synchronization
• columns included in the key are not dropped
• key columns do not receive incompatible data type changes Important

If row sorting is not performed during hash calculation, table data verification may produce false negatives in some scenarios.

For example, this may happen when the partitioning properties of a table are modified during synchronization.

Verification Scope

Table data verification is executed for:

• all altered tables after synchronization
• tables modified during dependency processing

Scripts responsible for hash generation and verification are automatically integrated into the synchronization script.

If verification fails, the Error List window displays the following warning:

Data verification for table {TableName} failed. Check table data manually.

For teams evaluating SQL development tools for schema comparison and deployment, automated table verification can help identify hidden synchronization issues before they affect production systems.

Examples of Detecting Data Loss

Example 1 — Reducing VARCHAR Length

Target database:

CREATE TABLE product (
    id int PRIMARY KEY,
    comment varchar(2000)
)

The table contains values longer than 1000 characters.

Source database:

CREATE TABLE product (
    id int PRIMARY KEY,
    comment varchar(1000)
)

During synchronization, the comment column length is reduced from 2000 to 1000 characters.

As a result, existing data in the Target database is truncated.

dbForge Schema Compare for SQL Server detects the mismatch and displays the following warning:

Data verification for table {product} failed. Check table data manually.

Example 2 — Modifying a User-Defined Type

Target database:

CREATE TYPE longstring AS varchar(2000)
CREATE TABLE product (
    id int PRIMARY KEY,
    name longstring,
    comment longstring
)

Source database:

CREATE TYPE longstring AS varchar(1000)
CREATE TABLE product (
    id int PRIMARY KEY,
    name longstring,
    comment longstring
)

During synchronization, the size of the longstring user-defined type is reduced from 2000 to 1000 characters.

This causes partial data loss in the Target database.

dbForge Schema Compare for SQL Server detects the issue and generates the following warning:

Data verification for table {product} failed. Check table data manually.