SQL Source Control 4

We're going to remove the migrations V2 beta from SQL Source Control in version 5. We're replacing it with an improved version of the original migration scripts feature, which supports more version control systems and actions.

Download the SQL Source Control 5 beta

Scripts created using the V2 beta of migrations won't be compatible with SQL Source Control 5. To learn more, see Upgrading from old versions of migration scripts.

This page refers to the Migrations V2 beta. For information about how to use V1 migration scripts, see Working with migration scripts.

About guard clauses

V2 migrations scripts should contain guard clauses. A guard clause is additional SQL to make sure the script has the right effect in any environment you deploy it to. They are also known as preconditions.

Guard clauses should:

make changes to the database so the rest of the migration script can work when it needs to
stop the migration script making changes if it doesn't need to

You should include a guard clause in every V2 migration script. These prevent the migration script making changes if it doesn't need to be run. It is dangerous to assume that a script will only be run in the right preconditions. If you don't include a guard clause, the migration script might fail or create unexpected changes in the database.

1. Renaming the table [dbo].[Person] to [dbo].[Customer]

This migration script includes a guard clause to check that the table [dbo].[Person] exists in the database. If the table doesn't exist, no changes are made.

Migration script

--Rename table [dbo].Person to [dbo].Customer
IF NOT EXISTS ( SELECT  1
                FROM    [information_schema].[Tables]
                WHERE   table_schema = 'dbo'
                        AND TABLE_NAME = 'Person' )
    PRINT 'Object ''[dbo].[Person]'' could not be found - skipping
                                                       migration.';
ELSE
    EXEC sp_rename '[dbo].[Person]', 'Customer'

2. Updating the column Description in the table [dbo].[Widgets] with a default value for existing rows before adding a NOT NULL constraint

This migration script includes a guard clause to check that the [dbo].[Widgets] table contains a Description column. If the column doesn't exist, no changes are made.

Migration script

--first check that there is a [Description] column
IF NOT EXISTS ( SELECT  *
                FROM    sys.columns
                WHERE   name LIKE 'description'
                        AND OBJECT_NAME(object_ID) = 'Widgets'
                        AND OBJECT_SCHEMA_NAME(object_ID) = 'dbo' )
    BEGIN 
        PRINT 'Column [Description] in [dbo].[Widgets] 
                could not be found - skipping migration.';
        RETURN --None of the statements in a batch
               --following the RETURN statement are executed.
    END
--check to see if the column has already been made NOT NULL
IF NOT EXISTS ( SELECT  *
                FROM    sys.columns
                WHERE   name LIKE 'description'
                        AND OBJECT_NAME(object_ID) = 'Widgets'
                        AND OBJECT_SCHEMA_NAME(object_ID) = 'dbo'
                        AND is_nullable = 1 )
    BEGIN 
        PRINT 'Column [Description] in [dbo].[Widgets]
                is already not nullable.';
        RETURN --None of the statements in a batch
               --following the RETURN statement are executed.
    END
--we can't do the change if there are any dependencies. 
--There would be an error
IF EXISTS ( SELECT  *
            FROM    sys.sql_dependencies
            WHERE   class IN ( 0, 1 )
                    AND  referenced_major_id =  OBJECT_ID('widgets')
                    AND  COL_NAME( referenced_major_id,  referenced_minor_id) =
                                                              'Description' )
    BEGIN
        PRINT 'Column [Description] couldn''t be altered because
                it is being referenced.';
        RETURN --None of the statements in a batch
               --following the RETURN statement are executed.
    END

UPDATE  widgets
SET     Description = 'unknown'
WHERE   description IS NULL
IF NOT EXISTS ( SELECT  *
                FROM    sys.columns c
                        INNER JOIN sys.default_constraints d
                              ON c.default_object_id = d.object_id
                WHERE   OBJECT_NAME(c.object_ID) = 'Widgets'
                        AND c.name = 'description'
                        AND OBJECT_SCHEMA_NAME(c.object_ID) = 'dbo' )
    ALTER TABLE [dbo].[Widgets] ADD CONSTRAINT WidgetDefault 
    DEFAULT 'unknown' FOR Description;
ALTER TABLE [dbo].[Widgets] ALTER COLUMN description VARCHAR(50) NOT NULL

3. Splitting the column Address into the columns StreetAddress and PostCode and updating existing rows

This migration script includes a guard clause to check that the Customer table contains an Address column. If the column doesn't exist, no changes are made.

If the column does exist, the guard clause checks that the StreetAddress and PostCode columns also exist. If they don't exist, the script creates the columns before updating the rows.

Migration script

IF NOT EXISTS ( SELECT  *
                FROM    sys.columns
                WHERE   name LIKE 'address'
                        AND OBJECT_NAME(object_ID) = 'customer'
                        AND OBJECT_SCHEMA_NAME(object_ID) = 'dbo' )
    BEGIN 
        PRINT 'Column ''address'' in dbo.customer could not be found -
                skipping migration.';
        RETURN --None of the statements in a batch following the RETURN statement are executed.
    END
PRINT N'Address exists'
IF NOT EXISTS ( SELECT  *
                FROM    sys.columns
                WHERE   name LIKE 'StreetAddress'
                        AND OBJECT_NAME(object_ID) = 'customer'
                        AND OBJECT_SCHEMA_NAME(object_ID) = 'dbo' )
    BEGIN
        PRINT N'Creating StreetAddress'
        ALTER TABLE [Customer]
        ADD StreetAddress VARCHAR(100)
    END
ELSE
    PRINT 'The StreetAddress column already exists'

IF NOT EXISTS ( SELECT  *
                FROM    sys.columns
                WHERE   name LIKE 'Postcode'
                        AND OBJECT_NAME(object_ID) = 'customer'
                        AND OBJECT_SCHEMA_NAME(object_ID) = 'dbo' )
    BEGIN
        PRINT N'Creating Postcode'
        ALTER TABLE [Customer]
        ADD Postcode VARCHAR(20)
    END
ELSE
    PRINT 'The postcode column already exists'
IF EXISTS ( SELECT  1
            FROM    dbo.customer
            WHERE   streetaddress IS NULL )
    EXECUTE sp_executeSQL N'
--Split data into columns StreetAddress and PostCode
UPDATE customer SET StreetAddress=SUBSTRING(ADDRESS, 0, CHARINDEX('';'', ADDRESS))
UPDATE customer SET Postcode=SUBSTRING(ADDRESS,CHARINDEX('';'', ADDRESS)+1, LEN(Address) )'
ELSE
    PRINT 'this operation has already been completed'

4. Moving data from the table [dbo].[PersonData] to the tables [dbo].[Person] and [dbo].[Email]

This migration script includes a guard clause to check that the table [dbo].[PersonData] exists in the database. If the table doesn't exist, no changes are made.

If the table exists, the guard clause checks that the [dbo].[Person] and [dbo].[Email] tables exist. If they don't exist, the script creates the tables before updating the rows.

Migration script

IF NOT EXISTS ( SELECT  1
                FROM    Information_Schema.Tables
                WHERE   table_schema = 'dbo'
                        AND TABLE_NAME = 'PersonData' )
    BEGIN
        PRINT 'Table [dbo].[PersonData] could not be found 
               - skipping migration.';
        RETURN;
    END 

-- Create [dbo].Person if it doesn't exist at the time of deployment
IF NOT EXISTS ( SELECT  1
                FROM    Information_Schema.Tables
                WHERE   table_schema = 'dbo'
                        AND TABLE_NAME = 'Person' )
    BEGIN
        CREATE TABLE Person
            (
              ID INT IDENTITY(1, 1)
                     NOT NULL ,
              NAME NVARCHAR(200) ,
              CONSTRAINT PK_ID PRIMARY KEY ( ID )
            );
    END
-- Create [dbo].Email if it doesn't exist
IF NOT EXISTS ( SELECT  1
                FROM    Information_Schema.Tables
                WHERE   table_schema = 'dbo'
                        AND TABLE_NAME = 'Email' )
    BEGIN
        CREATE TABLE Email
            (
              PersonID INT ,
              Email NVARCHAR(200)
                CONSTRAINT FK_Person_ID
                FOREIGN KEY ( PersonID ) REFERENCES Person ( ID )
            );
    END

--Move data from [dbo].PersonData into [dbo].Person
SET IDENTITY_INSERT [dbo].Person ON
INSERT  INTO [dbo].Person
        ( ID ,
          Name
        )
        SELECT  ID ,
                NAME
        FROM    dbo.PersonData
SET IDENTITY_INSERT [dbo].Person OFF
-- Move data from [dbo].[PersonData] to [dbo].Email
INSERT  INTO dbo.Email
        ( PersonID ,
          Email
        )
        SELECT  ID ,
                Email1
        FROM    dbo.PersonData
        WHERE   Email1 IS NOT NULL
        UNION
        SELECT  ID ,
                Email2
        FROM    dbo.PersonData
        WHERE   Email2 IS NOT NULL

5. Making “subtractive” schema changes (eg dropping tables or columns)

You can use a migration script to make intentional “subtractive” schema changes (e.g. dropping tables or column) that would otherwise cause a data loss warning and abort a continuous integration step.

This migration script includes a guard clause to check that the table [dbo].DropTbWoDataLoss exists in the database. If the table doesn't exist, no changes are made.

Migration script

IF NOT EXISTS (SELECT 1 FROM [information_schema].[Tables] WHERE table_schema = 'dbo' AND TABLE_NAME = 'DropTbWoDataLoss')
     PRINT 'Object ''[dbo].[DropTbWoDataLoss]'' could not be found - skipping migration.';
Else
     DROP TABLE [dbo].[DropTbWoDataLoss]
END

SQL Source Control 4

Example V2 migration scripts

About guard clauses

1. Renaming the table [dbo].[Person] to [dbo].[Customer]

2. Updating the column Description in the table [dbo].[Widgets] with a default value for existing rows before adding a NOT NULL constraint

3. Splitting the column Address into the columns StreetAddress and PostCode and updating existing rows

4. Moving data from the table [dbo].[PersonData] to the tables [dbo].[Person] and [dbo].[Email]

5. Making “subtractive” schema changes (eg dropping tables or columns)