SQL Source Control 5

Migration script examples

Example cases

Starting with a blank script

  Splitting a column

From a schema point of view, when you split a column you actually create two new columns and drop the original one. If you deploy this change, any data in the dropped column will be lost.

To avoid this, you can write a migration script to copy the data to the new columns before dropping the original column. For a walkthrough of this process, see Splitting a column without data loss.

Example: splitting a column

1. Schema change
Create two new columns.

(tick) Commit

2. Migration script
Split and copy data to new columns.

(tick) Commit

3. Schema change
Drop the original column.

(tick) Commit

  Merging columns

From a schema point of view, when you merge a column, you create a new column and drop the original columns. If you deploy this change, any data in the dropped columns will be lost. 

To avoid this, you can write a migration script to copy the data from the original columns to the new column before dropping the original columns.

Example: merging columns

1. Schema change
Create the new column.

(tick) Commit

2. Migration script
Copy data to the new column.

(tick) Commit

3. Schema change
Drop the original columns.

(tick) Commit

  Splitting or merging tables

You can follow the same steps for splitting or merging a tables as you would for splitting or merging columns. To avoid data loss, break the change into multiple commits, and write a migration script to copy the data:

Splitting a table
Commit 1 Commit 2 Commit 3
Create the new table. Migration script to copy the data to the new table. Drop the columns in the original table.
Merging tables
Commit 1 Commit 2 Commit 3
Create the new table. Migration script to copy data from the old tables to the new table. Drop the original tables.
  Adding a NOT NULL constraint to a column

If you add a NOT NULL constraint to a column in a table that contains NULL entries, without a default value, the deployment will fail.

Rather than adding a default value, you can write a migration script to update all the existing NULL entries with a NOT NULL value before adding the NOT NULL constraint to the column:

Example: adding a NOT NULL constraint to a column

1. Migration script
Update NULL entries with NOT NULL values.

(tick) Commit

2. Schema change
Add the NOT NULL constraint to the column.

(tick) Commit

  Changing the data type or size of a column

When you change a column's data type, data might be lost. For example, if the data type you change it to doesn't accommodate some of the rows, data will be truncated during deployment. 

To avoid this, you can create a migration script to appropriately modify rows that would otherwise be truncated.

Replacing uncommitted schema changes

  Renaming a table

When you rename a table in Management Studio, SQL Source Control interprets this as dropping and recreating the table. If the table contains data, the data will be lost. 

To avoid this, you can rename the table using the Object Explorer, then select that uncommitted change on the migrations tab. Generate a migration script for this change and replace the DROP and CREATE statements in the generated script with the sp_rename stored procedure.

Tutorials


Didn't find what you were looking for?