SQL Source Control 3

Help for older versions available.

These pages cover SQL Source Control 3, which is not the latest version. Help for other versions is also available.

Example V2 migration scripts

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.

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 Toggle source code

  1. --Rename table [dbo].Person to [dbo].Customer
  2. IF NOT EXISTS ( SELECT 1
  3. FROM [information_schema].[Tables]
  4. WHERE table_schema = 'dbo'
  5. AND TABLE_NAME = 'Person' )
  6. PRINT 'Object ''[dbo].[Person]'' could not be found - skipping
  7. migration.';
  8. ELSE
  9. 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 Toggle source code

  1. --first check that there is a [Description] column
  2. IF NOT EXISTS ( SELECT *
  3. FROM sys.columns
  4. WHERE name LIKE 'description'
  5. AND OBJECT_NAME(object_ID) = 'Widgets'
  6. AND OBJECT_SCHEMA_NAME(object_ID) = 'dbo' )
  7. BEGIN
  8. PRINT 'Column [Description] in [dbo].[Widgets]
  9. could not be found - skipping migration.';
  10. RETURN --None of the statements in a batch
  11. --following the RETURN statement are executed.
  12. END
  13. --check to see if the column has already been made NOT NULL
  14. IF NOT EXISTS ( SELECT *
  15. FROM sys.columns
  16. WHERE name LIKE 'description'
  17. AND OBJECT_NAME(object_ID) = 'Widgets'
  18. AND OBJECT_SCHEMA_NAME(object_ID) = 'dbo'
  19. AND is_nullable = 1 )
  20. BEGIN
  21. PRINT 'Column [Description] in [dbo].[Widgets]
  22. is already not nullable.';
  23. RETURN --None of the statements in a batch
  24. --following the RETURN statement are executed.
  25. END
  26. --we can't do the change if there are any dependencies.
  27. --There would be an error
  28. IF EXISTS ( SELECT *
  29. FROM sys.sql_dependencies
  30. WHERE class IN ( 0, 1 )
  31. AND referenced_major_id = OBJECT_ID('widgets')
  32. AND COL_NAME( referenced_major_id, referenced_minor_id) =
  33. 'Description' )
  34. BEGIN
  35. PRINT 'Column [Description] couldn''t be altered because
  36. it is being referenced.';
  37. RETURN --None of the statements in a batch
  38. --following the RETURN statement are executed.
  39. END
  40.  
  41. UPDATE widgets
  42. SET Description = 'unknown'
  43. WHERE description IS NULL
  44. IF NOT EXISTS ( SELECT *
  45. FROM sys.columns c
  46. INNER JOIN sys.default_constraints d
  47. ON c.default_object_id = d.object_id
  48. WHERE OBJECT_NAME(c.object_ID) = 'Widgets'
  49. AND c.name = 'description'
  50. AND OBJECT_SCHEMA_NAME(c.object_ID) = 'dbo' )
  51. ALTER TABLE [dbo].[Widgets] ADD CONSTRAINT WidgetDefault
  52. DEFAULT 'unknown' FOR Description;
  53. 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 Toggle source code

  1. IF NOT EXISTS ( SELECT *
  2. FROM sys.columns
  3. WHERE name LIKE 'address'
  4. AND OBJECT_NAME(object_ID) = 'customer'
  5. AND OBJECT_SCHEMA_NAME(object_ID) = 'dbo' )
  6. BEGIN
  7. PRINT 'Column ''address'' in dbo.customer could not be found -
  8. skipping migration.';
  9. RETURN --None of the statements in a batch following the RETURN statement are executed.
  10. END
  11. PRINT N'Address exists'
  12. IF NOT EXISTS ( SELECT *
  13. FROM sys.columns
  14. WHERE name LIKE 'StreetAddress'
  15. AND OBJECT_NAME(object_ID) = 'customer'
  16. AND OBJECT_SCHEMA_NAME(object_ID) = 'dbo' )
  17. BEGIN
  18. PRINT N'Creating StreetAddress'
  19. ALTER TABLE [Customer]
  20. ADD StreetAddress VARCHAR(100)
  21. END
  22. ELSE
  23. PRINT 'The StreetAddress column already exists'
  24.  
  25. IF NOT EXISTS ( SELECT *
  26. FROM sys.columns
  27. WHERE name LIKE 'Postcode'
  28. AND OBJECT_NAME(object_ID) = 'customer'
  29. AND OBJECT_SCHEMA_NAME(object_ID) = 'dbo' )
  30. BEGIN
  31. PRINT N'Creating Postcode'
  32. ALTER TABLE [Customer]
  33. ADD Postcode VARCHAR(20)
  34. END
  35. ELSE
  36. PRINT 'The postcode column already exists'
  37. IF EXISTS ( SELECT 1
  38. FROM dbo.customer
  39. WHERE streetaddress IS NULL )
  40. EXECUTE sp_executeSQL N'
  41. --Split data into columns StreetAddress and PostCode
  42. UPDATE customer SET StreetAddress=SUBSTRING(ADDRESS, 0, CHARINDEX('';'', ADDRESS))
  43. UPDATE customer SET Postcode=SUBSTRING(ADDRESS,CHARINDEX('';'', ADDRESS)+1, LEN(Address) )'
  44. ELSE
  45. 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 Toggle source code

  1. IF NOT EXISTS ( SELECT 1
  2. FROM Information_Schema.Tables
  3. WHERE table_schema = 'dbo'
  4. AND TABLE_NAME = 'PersonData' )
  5. BEGIN
  6. PRINT 'Table [dbo].[PersonData] could not be found
  7. - skipping migration.';
  8. RETURN;
  9. END
  10.  
  11. -- Create [dbo].Person if it doesn't exist at the time of deployment
  12. IF NOT EXISTS ( SELECT 1
  13. FROM Information_Schema.Tables
  14. WHERE table_schema = 'dbo'
  15. AND TABLE_NAME = 'Person' )
  16. BEGIN
  17. CREATE TABLE Person
  18. (
  19. ID INT IDENTITY(1, 1)
  20. NOT NULL ,
  21. NAME NVARCHAR(200) ,
  22. CONSTRAINT PK_ID PRIMARY KEY ( ID )
  23. );
  24. END
  25. -- Create [dbo].Email if it doesn't exist
  26. IF NOT EXISTS ( SELECT 1
  27. FROM Information_Schema.Tables
  28. WHERE table_schema = 'dbo'
  29. AND TABLE_NAME = 'Email' )
  30. BEGIN
  31. CREATE TABLE Email
  32. (
  33. PersonID INT ,
  34. Email NVARCHAR(200)
  35. CONSTRAINT FK_Person_ID
  36. FOREIGN KEY ( PersonID ) REFERENCES Person ( ID )
  37. );
  38. END
  39.  
  40. --Move data from [dbo].PersonData into [dbo].Person
  41. SET IDENTITY_INSERT [dbo].Person ON
  42. INSERT INTO [dbo].Person
  43. ( ID ,
  44. Name
  45. )
  46. SELECT ID ,
  47. NAME
  48. FROM dbo.PersonData
  49. SET IDENTITY_INSERT [dbo].Person OFF
  50. -- Move data from [dbo].[PersonData] to [dbo].Email
  51. INSERT INTO dbo.Email
  52. ( PersonID ,
  53. Email
  54. )
  55. SELECT ID ,
  56. Email1
  57. FROM dbo.PersonData
  58. WHERE Email1 IS NOT NULL
  59. UNION
  60. SELECT ID ,
  61. Email2
  62. FROM dbo.PersonData
  63. 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 Toggle source code

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

Didn't find what you were looking for?