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.

Thanks to user Steve Ognibene for raising this scenario.

1: Normalizing a table

Say we have a script to create a table named dbo.PersonData:

CREATE TABLE dbo.PersonData(
  ID INT IDENTITY(1,1),
  NAME NVARCHAR(200) NOT NULL,
  Email1 NVARCHAR(200) NULL,
  Email2 NVARCHAR(200) NULL,
  Phone1 NVARCHAR(100) NULL,
  Phone2 NVARCHAR(100) NULL,
  Street1 NVARCHAR(200) NULL,
  City1 NVARCHAR(200) NULL,
  StateProvince1 NVARCHAR(50) NULL,
  PostalCode1 NVARCHAR(50) NULL,
  Street2 NVARCHAR(200) NULL,
  City2 NVARCHAR(200) NULL,
  StateProvince2 NVARCHAR(50) NULL,
  PostalCode2 NVARCHAR(50) NULL,
  CONSTRAINT PK_PersonDataID PRIMARY KEY (ID));

We want to normalize the table. To do it, we'll break it into different tables in a new schema named Contacts:

CREATE SCHEMA Contacts
GO
CREATE TABLE Contacts.Person(
    ID INT IDENTITY(1,1),
    NAME NVARCHAR(200),
 	CONSTRAINT PK_PersonID PRIMARY KEY (ID)
	);
   
CREATE TABLE Contacts.Email(
    PersonID INT,
    Email NVARCHAR(200)
);

CREATE TABLE Contacts.Phone(
    PersonID INT,
    Phone NVARCHAR(100)
);

CREATE TABLE Contacts.PostAddress(
    PersonID INT,
    Street NVARCHAR(200) NULL,
    City NVARCHAR(200) NULL,
    StateProvince NVARCHAR(50) NULL,
    PostalCode NVARCHAR(50) NULL
);

CREATE TABLE Contacts.FavoriteFoods(
  PersonID INT,
  FoodName NVARCHAR(200)
)

Now we'll commit the new schema to source control.

2. Migrating the data

Next we’ll write a migration script to migrate the data from the old dbo.PersonData table to the new tables in the Contacts schema.

To migrate <Id, Name> from dbo.PersonData into [Contacts].Person, we'll write:

--Move data from dbo.PersonData into [Contacts].Person
      SET IDENTITY_INSERT [Contacts].Person ON
      BEGIN
            INSERT INTO [Contacts].Person ( ID, Name)
            SELECT ID, NAME FROM dbo.PersonData
      END
      SET IDENTITY_INSERT [Contacts].Person OFF

To migrate Email1 and Email2 from dbo.PersonData into [Contacts].Person (creating two separate rows for a person having both Email1 and Email2):

--Move data from dbo.PersonData into [Contacts].Email
	BEGIN
    	INSERT INTO Contacts.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
	END

To migrate Phone1 and Phone2 from dbo.PersonData into [Contacts].Phone:

--Move data from dbo.PersonData into [Contacts].Phone
   BEGIN
		INSERT INTO Contacts.Phone(PersonID,Phone)
      	SELECT ID, Phone1 FROM dbo.PersonData WHERE Phone1 IS NOT NULL
        UNION
   		SELECT ID, Phone2 FROM dbo.PersonData WHERE Phone2 IS NOT NULL
   END

To migrate address details from dbo.PersonData to [Contacts].PostAddress:

 --Move data from dbo.PersonData into [Contacts].PostAddress
      BEGIN
            INSERT INTO Contacts.PostAddress(PersonID,Street,City,StateProvince,PostalCode)
            SELECT ID, Street1,City1,StateProvince1,PostalCode1 FROM dbo.PersonData
            UNION
            SELECT ID, Street2,City2,StateProvince2,PostalCode2 FROM dbo.PersonData
      END

3. Writing guard clauses

Say we want to deploy the changes above to our production database, Prod A. Prod A contains the original dbo.PersonContact table with some data. If we write a migration script without checking that the Contacts schema and the new tables [Contacts].Person, [Contacts].Phone, [Contacts].Email and [Contacts].PostAddress exist in Prod A, the deployment will fail. This is because the database state our migration script is applied to is different from the state we wrote our migration script for. This is particularly easy to miss if you're working on several database changes at the same time, or if you write the migration script some time after creating the schema.

We'll include some guard clauses in the migration script to check the database is in the right state for the script to work. If it isn't, the script will alter your database first so the the rest of the migration script can be applied. This means the migration script will always work, no matter what state the deployment environment is in.

		IF OBJECT_ID('[dbo].[PersonData]', 'U') IS NOT NULL --check if the denormalized table exists as a first step
	BEGIN
		IF NOT EXISTS (SELECT NAME FROM Sys.Schemas WHERE NAME = 'Contacts') --check if Contacts schema exists
    BEGIN...

Now let's add some logic to create the objects if they don't exist when our migration script is applied.

IF NOT EXISTS (SELECT NAME FROM Sys.Schemas WHERE NAME = 'Contacts') --create Contacts schema if it doesn't exist
      BEGIN
      EXEC sp_executesql N'CREATE SCHEMA Contacts'
      --CREATE TABLE #ShouldRunMigration (notUsed bit)
      END

      --Create [Contacts].[Person] if it doesn't exist at time of deployment
      IF (OBJECT_ID('[Contacts].[Person]', 'U') IS NULL)
      BEGIN
            CREATE TABLE [Contacts].Person(
            ID INT IDENTITY(1,1),
            NAME NVARCHAR(200),
			CONSTRAINT PK_PersonID PRIMARY KEY (ID)
		);
      END...

4. Final migration script

The final migration script looks like this:

IF OBJECT_ID('[dbo].[PersonData]', 'U') IS NOT NULL --check if the denormalized table exists as a first step
BEGIN
IF NOT EXISTS (SELECT NAME FROM Sys.Schemas WHERE NAME = 'Contacts') --create Contacts schema if it doesn't exist
      BEGIN
      EXEC sp_executesql N'CREATE SCHEMA Contacts'
      --CREATE TABLE #ShouldRunMigration (notUsed bit)
      END

      --Create [Contacts].[Person] if it doesn't exist at time of deployment
      IF (OBJECT_ID('[Contacts].[Person]', 'U') IS NULL)
      BEGIN
            CREATE TABLE [Contacts].Person(
            ID INT IDENTITY(1,1),
            NAME NVARCHAR(200),
			CONSTRAINT PK_PersonID PRIMARY KEY (ID)
		);
      END

      --Create [Contacts].[Email] if it doesn't exist
      IF (OBJECT_ID('[Contacts].[Email]', 'U') IS NULL)
      BEGIN
            CREATE TABLE Contacts.Email(
            PersonID INT,
            Email NVARCHAR(200));
      END


      --Create [Contacts].[Phone] if it doesn't exist
      IF (OBJECT_ID('[Contacts].[Phone]', 'U') IS NULL)
      BEGIN
            CREATE TABLE [Contacts].Phone(
            PersonID INT,
            Phone NVARCHAR(100));
      END

      --Create [Contacts].[PostAddress] if it doesn't exist
      IF (OBJECT_ID('[Contacts].[PostAddress]', 'U') IS NULL)
      BEGIN
            CREATE TABLE Contacts.PostAddress(
            PersonID INT,
            Street NVARCHAR(200) NULL,
            City NVARCHAR(200) NULL,
            StateProvince NVARCHAR(50) NULL,
            PostalCode NVARCHAR(50) NULL);
      END

/*--Create [Contacts].[FavouriteFoods] doesn't need to be part of the migration script
      IF OBJECT_ID('[Contacts].[FavouriteFoods]', 'U') IS NULL
      BEGIN
      CREATE TABLE Contacts.FavoriteFoods(
        PersonID INT,
        FoodName NVARCHAR(200));
      END
      */

      --Move data from dbo.PersonData into [Contacts].Person
      SET IDENTITY_INSERT [Contacts].Person ON
      BEGIN
            INSERT INTO [Contacts].Person(ID, Name)
            SELECT ID, NAME FROM dbo.PersonData
      END
      SET IDENTITY_INSERT [Contacts].Person OFF

      --Move data from dbo.PersonData into [Contacts].Email
BEGIN
            INSERT INTO Contacts.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
      END
     
      --Move data from dbo.PersonData into [Contacts].Phone
      BEGIN
            INSERT INTO Contacts.Phone(PersonID,Phone)
            SELECT ID, Phone1 FROM dbo.PersonData WHERE Phone1 IS NOT NULL
            UNION
            SELECT ID, Phone2 FROM dbo.PersonData WHERE Phone2 IS NOT NULL
      END
     
      --Move data from dbo.PersonData into [Contacts].PostAddress
      BEGIN
            INSERT INTO Contacts.PostAddress(PersonID,Street,City,StateProvince,PostalCode)
            SELECT ID, Street1,City1,StateProvince1,PostalCode1 FROM dbo.PersonData
            UNION
            SELECT ID, Street2,City2,StateProvince2,PostalCode2 FROM dbo.PersonData
      END

END

After running the migration script, we can drop the original dbo.PersonData table as a separate commit.

SQL Source Control 4

Example - writing a V2 migration script that affects objects not yet in the target database

1: Normalizing a table

2. Migrating the data

3. Writing guard clauses

4. Final migration script