Supported changes

See what SQL Flow does with each kind of source schema change, from added and dropped columns to renamed tables, TRUNCATE and primary key changes.

This page lists every kind of source schema change and what SQL Flow does with it. Each change has one of three outcomes: it's applied on the target automatically, its table is held for your review, or the pipeline stops before the change. See Overview for how the outcomes work.

What SQL Flow does with each change

Added columns

An added column reaches every target, in order with the data. Rows changed after the DDL carry the new column's source values.

• Type. The column gets its source type on a target of the same engine, and the mapped type on another engine.
• Collation. A character column keeps the source column's collation. A target that doesn't support that collation holds the table for review.
• Existing rows. Rows that already exist get the value they have on the source: NULL for a nullable column, or the default of a NOT NULL column. The target column carries no default afterwards.
• Names. The column is added under its automatic target name, or under the name you set for it in Configure. A name that collides with another target column holds the table for review.
• Position. The new column becomes the target table's last column.

A default that isn't a constant, such as NEWID(), GETDATE() or an expression, gives each existing row its own value. The change stream doesn't carry those values, so the pipeline stops before the change. The same applies to a nullable column added with a default that the source writes into the existing rows (WITH VALUES). On SQL Server, the stop message for a non-constant default includes:

its per-row values exist only in the physical rewrite. Use a constant literal default, an edition that adds it as metadata only, or re-seed the table

To recover, see Changes that stop the pipeline.

Dropped columns

A dropped column is dropped from the target table automatically, at the same point in the stream. Its values are discarded on the target.

Dropping a column that is part of the table's key holds the table for review, because every change is applied by that key. The proposed DDL drops the column, with a comment that the key constraint must be dropped and created again first.

Other column changes

A change of a column's collation, identity property, computed definition, sparse setting or encryption stops the pipeline before the change.

Renamed columns

A rename of a column outside the key is applied where the target can rename the column exactly, and held for review elsewhere. A rename of a key column is always held for review.

Type changes

These type changes are applied where the target can make the same change exactly, and held for review elsewhere:

• a longer or shorter variable-length column, such as varchar(100) to varchar(200);
• a larger integer type, such as int to bigint;
• a change of a decimal or numeric column's precision or scale, outside the key.

If an existing target value doesn't fit a shorter length or a smaller scale, the change fails, changes nothing, and the pipeline stops at the change.

Any other type change, such as int to varchar, bigint to int or datetime to date, stops the pipeline before it. So does a precision or scale change of a decimal key column.

Nullability and defaults

A nullability change is applied where the target can make the same change exactly, and held for review elsewhere. Making a column NOT NULL fails and changes nothing if a target row holds NULL in it, and the pipeline then stops at the change.

Target columns carry no defaults: every row's value comes from the source. So adding, changing or dropping the default of an existing column needs nothing on the target.

Index changes

Creating, rebuilding or dropping an index changes nothing on the target, as long as the table's row key stays the same. A change to the index that serves as the row key is a primary key change.

On SQL Server, some rebuilds move a table's rows in a way the capture can't follow, and stop the pipeline before the change:

• A resumable online rebuild of the table's clustered index or heap. Rebuild it WITH (ONLINE = ON) without RESUMABLE, or WITH (ONLINE = OFF).
• An online rebuild of a partitioned table's clustered index or heap. Rebuild it WITH (ONLINE = OFF).

SQL Server Reconstruction stops at more index operations. See Reconstruction limitations.

Added, dropped and renamed tables

• Added tables. A pipeline captures the tables you selected. A table created on the source later isn't captured until you add it. Adding a table restarts the capture, so the next start copies every table again. See Changing tables.
• Dropped tables. DROP TABLE of a captured table stops the pipeline. To retire a table, remove it from the pipeline before you drop it on the source.
• Renamed tables. A captured table that is renamed on the source stops the pipeline, because the target table still has the old name. To continue, replace the old name with the new one on the pipeline's Tables tab, rename the target table to match or create it with Create tables & keys, and start the pipeline. The start copies every table again.

A partition switch into or out of a captured table moves rows without logging them as changes, so it stops the pipeline too.

Primary key changes

SQL Flow applies every change by the table's row key: its primary key, or the unique index it chose when the pipeline started. When DDL drops, disables or redefines that key, for example as not unique, filtered or over a nullable column, SQL Flow holds the table at that change, and the review card shows it as a key change.

The reason on the card ends with "Restore the row key on the source, then reload the table." The table can rejoin the stream only with a fresh copy, and the proposed DDL is a comment that asks you to write the matching target key change by hand. See What to do in each state.

Rebuilding the key index, or creating and dropping other indexes, isn't a key change.

TRUNCATE TABLE

What happens to a TRUNCATE TABLE depends on the source and capture mode:

To continue after a stop, see Changes that stop the pipeline.

A captured TRUNCATE empties the target table, in order with the changes before and after it, on every relational database target. On other targets:

• Fabric Open Mirroring stops the pipeline, because its change files can't express a truncate.
• Kafka and Google Pub/Sub stop the pipeline at a truncate of a table with a key. For a table without a key, they publish the truncate as an event keyed by the table name.

With a row filter, a captured truncate still empties the whole target table.

Changes that stop the pipeline

When the capture can't follow a change exactly, the pipeline stops before the change. Nothing after the change is applied to any table, and restarting can't get past it, so the pipeline ends up Failed: needs operator and raises the Pipeline failed: needs operator alert. See Automatic restart.

On SQL Server, the stop message has this shape, and the reason names the change:

SQL Server log-native schema history gap for '<table>': <reason>. No successor plan, row output, or checkpoint was published.

To continue:

1. Read the fault on the pipeline's Auto-restart card or in the red banner above the tabs.
2. Do one of these:
   • Reload the pipeline. Actions > Reload from source copies every table again from a fresh snapshot, so the pipeline continues after the change. See Lifecycle.
   • Change the table set. For a renamed or dropped table, edit the pipeline's tables on the Tables tab, then start the pipeline. The start copies every table again. See Changing tables.

Next time, prefer a change SQL Flow can carry: for example, add a column with a constant default instead of NEWID().

A target table that doesn't have exactly the shape SQL Flow expects also stops the pipeline at the change, for example a table you altered by hand, or one where you added the new column in advance. Undo the manual change on the target, then start the pipeline.

See also

• Overview
• Reviewing held changes
• Schema changes by target
• Data type mapping
• Changing tables