Example V2 migration scripts
Published 06 August 2013
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 migrations feature that supports more things (including Git, branching, and merging).
We'll be publishing more information about this soon.
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