How to Version SQL Server Database Schema

by Zoran Horvat
February 24, 2013

Introduction

While developing a software we typically have no special requirements to meet regarding versioning. Even when multiple programmers work on the same software, they are often not required to keep strict record of intermediate software versions. We are happy with any software that executes correctly on the development environment. But once the time comes to release the package, we need to mark it with version number, because that is the point in time when two packages begin to exist in parallel - one is the development version which keeps evolving in-house; the other one is released version which begins its life at customer's environment.

There is a long history of versioning of files that are deployed to the customer. Executable files and extension files (DLLs) normally contain version information. It is available when looking at file's properties in Windows system. Picture below shows properties of CALC.EXE which ships with one particular version of Windows operating system. This particular instance has file version 6.1.7600.16385.

Other resources, like XML files or databases, must also be versioned. But in this case, it is not content of the resource that is tagged with version number, but rather its structure - XML schema and database schema. File or database content changes as time progresses, but schema is remains constant - all until new version of the application is deployed, the one that relies on a different data schema and requires underlying schema to be changed as part of the software updating process. In database terms, this might require adding new tables or altering existing ones; adding indices, inserting new values to code tables, etc. In either case, we need a way to determine current schema so that we can decide which operations are appropriate to make current version evolve into target version of the package being installed.

One more thing must be kept on mind. We are typically not informed in advance which is the version of client's system. For example, one client might have been so good as to install every intermediate patch after, say, version 2.0 of the software was release. His current version might be something like 2.4.76.1817. Some other client was supposedly not interested in patching anything, and he might be living along with original version 2.0.17.912 for many months. But now suppose that a brand new version 3.0.9.86 was released and they both want to have it. There is a very simple scheme that makes this possible: we make installation package 3.0.9.86 such that it is applicable to any installation from generation 2 - 2.0.17.912, 2.1.x.x or 2.4.76.1817 regardless. It is installation process that decides what particular actions to make in order to transform system from actual version into target version 3.0.9.86.

This specific installation scheme requires that we keep track of all intermediate versions. The idea is then to upgrade each intermediate version to the next one. Namely, version 2.0.17.912 is transformed, possibly through many steps, into something like 2.1.x.x, then into 2.2.x.x and so on until, by pure coincidence, version 2.4.76.1817 is reached. At the same time, the other system which already was with this version would simply skip all the intermediate steps. Finally, both client systems can now be treated the same, as they have been brought to the same base version. This version is then further transformed, e.g. through 2.5.x.x, 2.6.x.x and so on, until all patches have been applied and ultimate target 3.0.9.86 is reached.

The only open question remaining is how to keep track of database schema versions. In the remaining text, we will first outline the solution. We will then provide reusable implementation in T-SQL, followed by a fully functional demonstration.

SQL Server Database Versioning Design

Solution is designed around a single table which tracks schema version changes. A couple of stored procedures are provided to back up required scenarios. The following picture shows table definition for schema version tracking. Each schema version is associated with time interval during which it is valid. Only one version can have ValidTo field set to NULL, and that is the current version.

And here is the typical content of this table:

There are a number of supporting stored procedures, out of which two are designed to be called from the outside world:

• CompareAndAlterSchema(@expectedVer VARCHAR(50), @newVer VARCHAR(50), @statement NVARCHAR(max)) - Compares current schema version against @expectedVer argument. If the two versions match, executes @statement and changes current version to @newVer. Note that @statement is optional - it is ignored if set to NULL. Also, if current version is greater than @expectedVer, operation is silently skipped. This ensures that one alter can be run multiple times without fear that some operations will fail in the repeated run. Important events occur if current schema version is less than @expectedVer. This indicates that some alter was not run (or at least was not run successfully). If such state is detected, stored procedure raises fatal error and terminates execution of all further statements in the script.
• CompareAndRollbackSchema(@expectedVer VARCHAR(50), @newVer VARCHAR(50), @statement NVARCHAR(max)) - Compares current schema version against @expectedVer argument. If the two versions match, executes @statement and deletes current version, at the same time making @newVer the new current version by setting its ValidTo field to NULL. Note that @newVer must be the immediate predecessor of @expectedVer, i.e. there must exist no other version greater than @newVer and less than @expectedVer, or otherwise no changes will be made to the database. Additionally, if current version is less than @expectedVer then operation is silently skipped - this case occurs if schema rollback is performed more than once. If current version is greater than @expectedVer, then stored procedure raises fatal error and terminates execution of further statements. This situation indicates that some rollback script which precedes current script was not run.

Demonstration

In this section we will demonstrate the use of stored procedures mentioned above to easily write alters and rollback scripts for a small database. This database will only contain three tables: versioning.SchemaVersion table, which is required to keep track of schema version changes, and two custom tables that are meant to keep data about registered users. Here is the schema diagram which clearly explains intentions:

But, as it typically happens in practice, the first set of requirements is smaller than the final state. Suppose that customer has initially only requested that database is capable of tracking registered users by their username and password. Only later would another request arrive, asking to extend the database so that each registered user is enriched with one email address and one or more postal addresses. In the following sections will produce the final database incrementally.

Creating Database with Schema Version 1.0

First requirement, to create the database, would be fulfilled by this database script (_CREATE_DATABASE.sql):

-- _CREATE_DATABASE.sql
-- Creates the database with specified name
-- Associates the database with specific login and username

USE master
GO

DECLARE @databaseName NVARCHAR(50) = 'Demo'
DECLARE @loginName NVARCHAR(50) = 'demo'
DECLARE @loginPassword NVARCHAR(50) = 'demo'
DECLARE @userName NVARCHAR(50) = 'demo'

DECLARE @statement NVARCHAR(max)

IF NOT EXISTS (SELECT * FROM dbo.syslogins WHERE name=@loginName)
    BEGIN
        SET @statement = N'CREATE LOGIN ' + @loginName +
                         N' WITH PASSWORD = ''' + @loginPassword + N''''
        EXEC sp_executesql @statement
    END

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name=@databaseName)
    BEGIN
        SET @statement = N'CREATE DATABASE ' + @databaseName
        EXEC sp_executesql @statement
    END

SET @statement = N'
USE ' + @databaseName + N'
IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name=''' + @userName + N''')
    BEGIN
        CREATE USER ' + @userName + N' FOR LOGIN ' + @loginName + N'
        EXEC sp_addrolemember ''db_owner'', ' + @userName + N'
    END'
EXEC sp_executesql @statement

After this script is run, the Demo database would be created. But in order to track its schema changes, we need to execute the first alter (ALTER_01_00.sql script). This script introduces versioning schema with versioning.SchemaVersion table and supporting stored procedures. Here is the script:

-- ALTER_01_00.sql
-- Creates versioning schema.
-- Creates versioning.SchemaVersion table.
-- Creates stored procedures that are used to track
--    schema changes.

USE Demo

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name='versioning')
    EXEC sp_executesql N'CREATE SCHEMA [versioning]'

IF OBJECT_ID('[versioning].[SchemaVersion]') IS NULL
    CREATE TABLE [versioning].[SchemaVersion]
    (
        [SchemaVersionID] INT PRIMARY KEY IDENTITY,
        [ValidFrom] DATETIME NOT NULL,
        [ValidTo] DATETIME NULL,
        [Major] INT NOT NULL,
        [Minor] INT NOT NULL,
        [Build] INT NOT NULL,
        [Revision] INT NOT NULL
    )

IF NOT EXISTS (SELECT TOP 1 * FROM [versioning].[SchemaVersion])
    INSERT INTO [versioning].[SchemaVersion]
        ([ValidFrom], [ValidTo], [Major], [Minor], [Build], [Revision])
    VALUES
        (GETUTCDATE(), NULL, 1, 0, 0, 0)

IF OBJECT_ID('[versioning].[GetSchemaVersion]') IS NULL
    EXEC sp_executesql N'
-- Reads current database schema version.
-- Parameters:
--   @version - On output contains current schema version
CREATE PROCEDURE [versioning].[GetSchemaVersion]
    @version VARCHAR(50) OUT
AS
BEGIN
    DECLARE @major INT
    DECLARE @minor INT
    DECLARE @build INT
    DECLARE @revision INT

    SET @version=''''

    SELECT @major=Major, @minor=Minor, @revision=Revision, @build=Build
    FROM [versioning].[SchemaVersion]
    WHERE [ValidTo] IS NULL

    IF @major IS NOT NULL
        SET @version =
            CAST(@major AS VARCHAR(50)) + ''.'' +
            CAST(@minor AS VARCHAR(50)) + ''.'' +
            CAST(@build AS VARCHAR(50)) + ''.'' +
            CAST(@revision AS VARCHAR(50))

END'

IF OBJECT_ID('[versioning].[GetVersionParts]') IS NULL
    EXEC sp_executesql N'
-- Breaks version into four integers, representing major version number,
-- minor version number, revision number and build number.
-- Parameters:
--    @version - Version specified as dot-separated segments (e.g. 1.2.3.4)
--    @major - On output major version number extracted from @version
--    @minor - On output minor version number extracted from @version
--    @build - On output build number extracted from @version
--    @revision - On output revision number extracted from @version
CREATE PROCEDURE [versioning].[GetVersionParts]
    @version VARCHAR(50),
    @major INT OUT,
    @minor INT OUT,
    @build INT OUT,
    @revision INT OUT
AS
BEGIN
    DECLARE @startPos INT = 1
    DECLARE @endPos INT

    SET @endPos = CHARINDEX(''.'', @version, @startPos)
    SET @major = CAST(SUBSTRING(@version, @startPos, @endPos - @startPos) AS INT)
    SET @startPos = @endPos + 1

    SET @endPos = CHARINDEX(''.'', @version, @startPos)
    SET @minor = CAST(SUBSTRING(@version, @startPos, @endPos - @startPos) AS INT)
    SET @startPos = @endPos + 1

    SET @endPos = CHARINDEX(''.'', @version, @startPos)
    SET @build = CAST(SUBSTRING(@version, @startPos, @endPos - @startPos) AS INT)
    SET @startPos = @endPos + 1

    SET @endPos = LEN(@version) + 1
    SET @revision = CAST(SUBSTRING(@version, @startPos, @endPos - @startPos) AS INT)

END'

IF OBJECT_ID('[versioning].[FindNextVersion]') IS NULL
    EXEC sp_executesql N'
-- Finds version which immediately follows specified version.
-- Parameters:
--  @prevVersion - Version for which immediately following version is required.
--  @nextVersion - On output contains version which immediately
--                 follows @prevVersion; NULL if such version was not found
CREATE PROCEDURE [versioning].[FindNextVersion]
    @prevVersion VARCHAR(50),
    @nextVersion VARCHAR(50) OUT
AS
BEGIN

    DECLARE @major INT
    DECLARE @minor INT
    DECLARE @build INT
    DECLARE @revision INT

    DECLARE @nextMajor INT
    DECLARE @nextMinor INT
    DECLARE @nextBuild INT
    DECLARE @nextRevision INT

    EXEC [versioning].[GetVersionParts] @prevVersion, @major OUT, @minor OUT,
                                        @build OUT, @revision OUT

    SELECT TOP 1 @nextMajor = Major, @nextMinor = Minor, @nextBuild = Build,
                 @nextRevision = Revision
    FROM [versioning].[SchemaVersion]
    WHERE
        (Major > @major) OR
        (Major = @major AND Minor > @minor) OR
        (Major = @major AND Minor = @minor AND Build > @build) OR
        (Major = @major AND Minor = @minor AND Build = @build AND
         Revision > @revision)
    ORDER BY Major, Minor, Build, Revision

    SET @nextVersion = NULL
    IF @nextMajor IS NOT NULL
        SET @nextVersion =
            CAST(@nextMajor AS VARCHAR(50)) + ''.'' +
            CAST(@nextMinor AS VARCHAR(50)) + ''.'' +
            CAST(@nextBuild AS VARCHAR(50)) + ''.'' +
            CAST(@nextRevision AS VARCHAR(50))

END'

IF OBJECT_ID('[versioning].[CompareSchemaVersions]') IS NULL
    EXEC sp_executesql N'
-- Compares two schema versions and returns an integer number
--     indicating the comparison result.
-- Parameters:
--    @ver1 - First version to compare
--    @ver2 - Second version to compare
-- Result:
--    -1 - @ver1 is smaller than @ver2
--    0  - @ver1 and @ver2 are equal
--    +1 - @ver1 is larger than @ver2
CREATE PROCEDURE [versioning].[CompareSchemaVersions]
    @ver1 VARCHAR(50),
    @ver2 VARCHAR(50)
AS
BEGIN
    DECLARE @major1 INT
    DECLARE @minor1 INT
    DECLARE @build1 INT
    DECLARE @revision1 INT
    DECLARE @major2 INT
    DECLARE @minor2 INT
    DECLARE @build2 INT
    DECLARE @revision2 INT
    DECLARE @res INT

    EXEC [versioning].[GetVersionParts] @ver1, @major1 OUT, @minor1 OUT,
                                        @build1 OUT, @revision1 OUT

    EXEC [versioning].[GetVersionParts] @ver2, @major2 OUT, @minor2 OUT,
                                        @build2 OUT, @revision2 OUT

    SET @res = 0

    IF @major1 < @major2
        SET @res = -1
    ELSE IF @major1 > @major2
        SET @res = 1
    ELSE IF @minor1 < @minor2
        SET @res = -1
    ELSE IF @minor1 > @minor2
        SET @res = 1
    ELSE IF @build1 < @build2
        SET @res = -1
    ELSE IF @build1 > @build2
        SET @res = 1
    ELSE IF @revision1 < @revision2
        SET @res = -1
    ELSE IF @revision1 > @revision2
        SET @res = 1

    RETURN @res

END'

IF OBJECT_ID('[versioning].[CompareWithCurrentSchemaVersion]') IS NULL
    EXEC sp_executesql N'
-- Compares specified schema version with current schema version
-- and returns an integer number indicating the comparison result.
-- Parameters:
--    @ver - Version to compare with current schema version.
-- Result:
--    -1 - @ver is smaller than current schema version.
--    0  - @ver is equal to current schema version.
--    +1 - @ver is larger than current schema version.
CREATE PROCEDURE [versioning].[CompareWithCurrentSchemaVersion]
    @ver VARCHAR(50)
AS
BEGIN
    DECLARE @cur VARCHAR(50)
    DECLARE @res INT

    EXEC [versioning].[GetSchemaVersion] @cur OUT

    EXEC @res = [versioning].[CompareSchemaVersions] @ver, @cur

    RETURN @res

END'

IF OBJECT_ID('[versioning].[AssertSchemaVersion]') IS NULL
    EXEC sp_executesql N'
-- Compares specified schema version with current schema version and if
-- current schema version is smaller, terminates execution with fatal error.
-- Otherwise continues and returns a number indicating comparison between
-- @ver and current schema version. Alternatively, if @invertVer argument is 1,
-- inverts logic and raises error if current schema version is greater than
-- the specified schema version.
-- Parameters:
--    @ver - Version which must be asserted. Procedure fails if current schema
--           version is smaller than this version.
--  @invertVer - Indicates whether to invert logic (1) or not (0); with inverted
--               logic, procedure tests whether current version is greater than
--               the specified version.
-- Result:
--    0  - current schema version is equal to @ver
CREATE PROCEDURE [versioning].[AssertSchemaVersion]
    @ver VARCHAR(50),
    @invertVer BIT = 0
AS
BEGIN
    DECLARE @cmp INT
    DECLARE @msg VARCHAR(200)
    DECLARE @curVersion VARCHAR(50)

    EXEC @cmp = [versioning].[CompareWithCurrentSchemaVersion] @ver

    IF @invertVer = 1 SET @cmp = -@cmp

    IF @cmp > 0
        BEGIN
            EXEC [versioning].[GetSchemaVersion] @curVersion OUT
            SET @msg = ''Error executing on schema version '' + @curVersion +
                        '' when expecting '' + @ver + ''.''
            RAISERROR(@msg, 20, 0) WITH LOG
        END

    RETURN @cmp

END'

IF OBJECT_ID('[versioning].[ChangeSchemaVersion]') IS NULL
    EXEC sp_executesql N'
-- Changes schema version from @prevVersion to @nextVersion and returns
-- status indicating whether version was updated or not.
-- Parameters:
--    @prevVersion - Version number which should be changed
--    @nextVersion - New version number, which should be set after
--                   procedure completes
-- Return value:
--   0 - version was successfully changed
--   1 - @prevVersion did not match current schema version;
--       no changes were made to the system
--   2 - @nextVersion is not larger than @prevVersion
CREATE PROCEDURE [versioning].[ChangeSchemaVersion]
    @prevVersion VARCHAR(50),
    @nextVersion VARCHAR(50)
AS
BEGIN

    DECLARE @res INT = 0
    DECLARE @major INT
    DECLARE @minor INT
    DECLARE @build INT
    DECLARE @revision INT
    DECLARE @prevMajor INT
    DECLARE @prevMinor INT
    DECLARE @prevBuild INT
    DECLARE @prevRevision INT
    DECLARE @curVersion VARCHAR(50)

    EXEC [versioning].[GetVersionParts] @nextVersion, @major OUT,
                                        @minor OUT, @build OUT, @revision OUT

    EXEC [versioning].[GetSchemaVersion] @curVersion OUT

    DECLARE @cmp INT

    EXEC @cmp = [versioning].[CompareSchemaVersions] @prevVersion, @nextVersion

    DECLARE @currentCompare INT
    EXEC @currentCompare =
        [versioning].[CompareWithCurrentSchemaVersion] @prevVersion

    IF @cmp >= 0
        SET @res = 2
    ELSE IF @currentCompare <> 0
        SET @res = 1
    ELSE
        BEGIN

            DECLARE @curTime DATE
            SET @curTime = GETUTCDATE()

            UPDATE [versioning].[SchemaVersion]
            SET [ValidTo] = @curTime
            WHERE [ValidTo] IS NULL

            INSERT INTO
            [versioning].[SchemaVersion]
                ([ValidFrom], [ValidTo], [Major], [Minor],
                 [Build], [Revision])
            VALUES
                (@curTime, NULL, @major, @minor, @build, @revision)

        END

    RETURN @res

END'

IF OBJECT_ID('[versioning].[CompareAndAlterSchema]') IS NULL
    EXEC sp_executesql N'
-- Compares current schema version with specified @expectedVer and,
-- if these two versions match, executes @statement and updates
-- schema version to @newVer. If @expectedVer is less than current
-- schema version, makes no changes (this is the case when alter
-- is run more than once). Otherwise, if @expectedVer is greater than
-- current schema version, raises fatal error and terminates
-- (this is the case when some intermediate alter was not run).
-- Parameters:
--   @expectedVer - Version which should match current schema version;
--                  if greater than current schema version,
--                  fatal error is raised.
--   @newVer - New schema version which should be set after successful execution.
--   @statement - Optional statement which should be executed if current
--                schema version matches version specified by @expectedVer.
--                Ignored if NULL or empty string.
CREATE PROCEDURE [versioning].[CompareAndAlterSchema]
    @expectedVer VARCHAR(50),
    @newVer VARCHAR(50),
    @statement NVARCHAR(max)
AS
BEGIN

    DECLARE @cmp INT

    EXEC @cmp = versioning.AssertSchemaVersion @expectedVer

    IF @cmp = 0
        BEGIN

            IF @statement IS NOT NULL AND LEN(@statement) > 0
                EXEC sp_executesql @statement

            EXEC versioning.ChangeSchemaVersion @expectedVer, @newVer

        END

END'

IF OBJECT_ID('[versioning].[RollbackSchemaVersion]') IS NULL
    EXEC sp_executesql N'
-- Rollbacks schema version from @prevVersion to @nextVersion and
-- returns status indicating whether version was rollbacked
-- successfully or not.
-- Parameters:
--   @prevVersion - Version number which should match current version
--   @nextVersion - Version number which will be new current version;
--                  must equal immediate predecessor of @prevVersion
-- Return value:
--   0 - Version was rollbacked successfully
--   1 - @prevVersion does not match current version
--   2 - @nextVersion does not match immediate predecessor of @prevVersion
CREATE PROCEDURE [versioning].[RollbackSchemaVersion]
    @prevVersion VARCHAR(50),
    @nextVersion VARCHAR(50)
AS
BEGIN

    DECLARE @res INT
    DECLARE @curCompare INT
    DECLARE @followingVersion VARCHAR(50)
    DECLARE @followerCompare INT
    DECLARE @major INT
    DECLARE @minor INT
    DECLARE @build INT
    DECLARE @revision INT

    EXEC @curCompare =
        [versioning].[CompareWithCurrentSchemaVersion] @prevVersion

    EXEC [versioning].[FindNextVersion] @nextVersion, @followingVersion OUT

    EXEC @followerCompare =
        [versioning].[CompareSchemaVersions] @prevVersion, @followingVersion

    IF @curCompare <> 0
        SET @res = 1
    ELSE IF @followerCompare <> 0
        SET @res = 2
    ELSE
        BEGIN

            DELETE FROM [versioning].[SchemaVersion] WHERE [ValidTo] IS NULL

            EXEC [versioning].[GetVersionParts] @nextVersion, @major OUT,
                                                @minor OUT, @build OUT,
                                                @revision OUT

            UPDATE [versioning].[SchemaVersion]
            SET [ValidTo] = NULL
            WHERE
                Major = @major AND
                Minor = @minor AND
                Build = @build AND
                Revision = @revision

        END
END'

IF OBJECT_ID('[versioning].[CompareAndRollbackSchema]') IS NULL
    EXEC sp_executesql N'
-- Changes schema version back to previous version and optionally
-- executes specified statement. Use this stored procedure
-- to execute statement which returns database schema to state
-- in which it was when previous schema version was active.
-- Parameters:
--  @expectedVer - Should match current schema version
--  @newVer - Should match schema version which immediately
--            precedes @expectedVer; this is version to which
--            schema will be rollbacked
--  @statement - Optional statement which should be executed
--               when schema version is rollbacked; ignored if
--               null or empty string
CREATE PROCEDURE [versioning].[CompareAndRollbackSchema]
    @expectedVer VARCHAR(50),
    @newVer VARCHAR(50),
    @statement NVARCHAR(max)
AS
BEGIN

    DECLARE @cmp INT
    DECLARE @schemaRollbacked INT
    DECLARE @msg VARCHAR(200)

    EXEC @cmp = versioning.AssertSchemaVersion @expectedVer, 1

    IF @cmp = 0
        BEGIN

            EXEC @schemaRollbacked =
                [versioning].[RollbackSchemaVersion] @expectedVer, @newVer

            IF @schemaRollbacked <> 0
                BEGIN
                    SET @msg = ''Cannot rollback to schema version '' + @newVer
                    RAISERROR(@msg, 20, 0) WITH LOG
                END

            IF @statement IS NOT NULL AND LEN(@statement) > 0
                EXEC sp_executesql @statement

        END

END'

As said before, this alter is mandatory. It establishes database schema version 1.0.0.0. After executing this script, further alters can be coded with using the versioning.CompareAndAlterSchema stored procedure, as it is going to be demonstrated shortly. This technique provides very simple and elegant database alters.

Altering Database Schema to Version 1.1

The time has come to consider the first request: adding client.RegisteredUser table to the database. Below is the alter script (ALTER_01_01.sql) which changes database schema version from 1.0 to 1.1 and performs the necessary changes:

-- ALTER_01_01.sql
-- Creates client schema.
-- Creates RegisteredUser table.

USE Demo

EXEC [versioning].[CompareAndAlterSchema] '1.0.0.0', '1.0.1.0', NULL

EXEC [versioning].[CompareAndAlterSchema] '1.0.1.0', '1.0.1.1',
    N'CREATE SCHEMA [client]'

EXEC [versioning].[CompareAndAlterSchema] '1.0.1.1', '1.0.1.2',
    N'CREATE TABLE [client].[RegisteredUser]
      (
          RegisteredUserID INT PRIMARY KEY IDENTITY,
          UserName NVARCHAR(100) NOT NULL,
          Password NVARCHAR(100) NOT NULL
      )'

EXEC [versioning].[CompareAndAlterSchema] '1.0.1.2', '1.1.0.0', NULL

Observe how simple and elegant this script is. Yet it has several important qualities.

First of all, script can be run multiple times. Each statement is executed only if current version matches the first argument of the stored procedure. When a statement is executed, schema version is moved to new value, preparing ground for the next statement.

Special actions are taken if current schema version does not match the first argument of versioning.CompareAndAlterSchema stored procedure. If current schema version is greater than the value, then statement is skipped and nothing happens. This case is when alter has already been executed before. Another case is if current schema version less than the specified expected version. In this case, script execution immediately terminates in order to prevent damage made to database schema. This situation occurs if some preceding alter was not executed, or was executed only partially.

Altering Database Schema to Version 1.2

Now that we have created the client schema and RegisteredUser table in it, we can face the next requirement. We are asked to introduce email address as mandatory field. Existing users should get "N/A" value in this field, while new users would have to provide valid value. Further on, postal address table should be added. So here is the ALTER_01_02.sql script which does all this:

-- ALTER_01_02.sql
-- Creates client.PostalAddress table.
-- Adds EmailAddress column to client.RegisteredUser table.

USE Demo

EXEC [versioning].[CompareAndAlterSchema] '1.1.0.0', '1.1.1.0', NULL

EXEC [versioning].[CompareAndAlterSchema] '1.1.1.0', '1.1.1.1',
    N'CREATE TABLE [client].[PostalAddress]
      (
          PostalAddressID INT PRIMARY KEY IDENTITY,
          RegisteredUserID INT NOT NULL,
          Country NVARCHAR(100) NOT NULL,
          City NVARCHAR(100) NOT NULL,
          StreetName NVARCHAR(150) NOT NULL,
          StreetNumber NVARCHAR(30) NOT NULL,
          CONSTRAINT fk_RegisteredUser FOREIGN KEY (RegisteredUserID)
          REFERENCES [client].[RegisteredUser](RegisteredUserID)
      )'

EXEC [versioning].[CompareAndAlterSchema] '1.1.1.1', '1.1.1.2',
    N'ALTER TABLE [client].[RegisteredUser]
      ADD [EmailAddress] VARCHAR(100) NULL'

EXEC [versioning].[CompareAndAlterSchema] '1.1.1.2', '1.1.1.3',
    N'UPDATE [client].[RegisteredUser]
      SET [EmailAddress] = ''N/A'''

EXEC [versioning].[CompareAndAlterSchema] '1.1.1.3', '1.1.1.4',
    N'ALTER TABLE [client].[RegisteredUser]
      ALTER COLUMN [EmailAddress] VARCHAR(100) NOT NULL'

EXEC [versioning].[CompareAndAlterSchema] '1.1.1.4', '1.2.0.0', NULL

Once again, the script is straight-forward, but it still provides all the advanced features required to properly track schema changes. Most importantly, it prevents damage that might occur if same statements are run mutliple times, or some statements are skipped.

When these four scripts are run, the database will be in place with requested tables and a relation between them. Below is the content of the versioning.SchemaVersion table, sorted in chronological order of events. The table now contains all information about changes in database schema version:

Reverting Changes Back to Version 1.1

Now suppose that something went wrong while alter 1.2 was running. It sometimes happens that database is inconsistent, either due to some unregistered manual change, or due to a mistake in the deployment. Even the alter itself might be incorrect. In either case, it is a good idea to have a rollback script ready so that changes made by the alter can be reverted immediately. In this way, we are quitting the system upgrade and leaving it for some happier time. That would buy us some additional time to investigate what went wrong and how to avoid the same problems happening again in the next attempt.

Rollback scripts using our system are written as easily as alters. The only change is that stored procedure versioning.CompareAndRollbackSchema is used. This stored procedure receives current and preceding version. If these two versions exactly match what is found in the versioning.SchemaVersion table, the third parameter, containing a command, is executed. This third parameter is optional. If it is null or empty string, the statement is skipped and only the schema version is reverted.

Here is the rollback script (ROLLBACK_01_02.sql) which can be applied to schema version 1.2 in order to revert it back to version 1.1:

-- ROLLBACK_01_02.sql
-- When applied to schema version 1.2.0.0,
-- rollbacks changes made by ALTER_01_02.sql
-- and returns schema version back to 1.1.0.0.

USE Demo

EXEC [versioning].[CompareAndRollbackSchema] '1.2.0.0', '1.1.1.4', NULL

EXEC [versioning].[CompareAndRollbackSchema] '1.1.1.4', '1.1.1.3', NULL

EXEC [versioning].[CompareAndRollbackSchema] '1.1.1.3', '1.1.1.2', NULL

EXEC [versioning].[CompareAndRollbackSchema] '1.1.1.2', '1.1.1.1',
    N'ALTER TABLE [client].[RegisteredUser]
      DROP COLUMN [EmailAddress]'

EXEC [versioning].[CompareAndRollbackSchema] '1.1.1.1', '1.1.1.0',
    N'DROP TABLE [client].[PostalAddress]'

EXEC [versioning].[CompareAndRollbackSchema] '1.1.1.0', '1.1.0.0', NULL

Good sides of this script are similar to alters listed above. If current schema version is lower than what is specified as the first argument of the stored procedure, the whole operation is silently skipped. This allows us to run the rollback script multiple times without causing any damage. On the other hand, if current schema version is higher than what is expected, stored procedure raises fatal error and terminates further execution. This protects from unintentional running an incorrect rollback script or running scripts out of order.

Reverting Changes Back to Version 1.0

In a similar fashion to previous script, we can prepare a rollback script (ROLLBACK_01_01.sql) which, when applied to database schema version 1.1.0.0, rollbacks changes to version 1.0.0.0:

-- ROLLBACK_01_01.sql
-- When applied to schema version 1.1.0.0,
-- rollbacks changes made by ALTER_01_01.sql
-- and returns schema version back to 1.0.0.0.

USE Demo

EXEC [versioning].[CompareAndRollbackSchema] '1.1.0.0', '1.0.1.2', NULL

EXEC [versioning].[CompareAndRollbackSchema] '1.0.1.2', '1.0.1.1',
    N'DROP TABLE [client].[RegisteredUser]'

EXEC [versioning].[CompareAndRollbackSchema] '1.0.1.1', '1.0.1.0',
    N'DROP SCHEMA [client]'

EXEC [versioning].[CompareAndRollbackSchema] '1.0.1.0', '1.0.0.0', NULL

This script leaves the database in the state after installing the versioning schema. Officially, this state is labeled with version number 1.0.0.0.

Removing Versioning Schema

We could go even further with rollbacks, to remove the versioning schema entirely. This time, there will be no help from the versioning.CompareAndRollbackSchema stored procedure, because this stored procedure, along with other stored procedures that it uses, is going to be dropped. Here is the full ROLLBACK_01_00.sql script:

-- ROLLBACK_01_00.sql
-- Drops stored procedures that are used to track
--    schema changes.
-- Drops versioning.SchemaVersion table.
-- Drops versioning schema.

USE Demo

IF OBJECT_ID('[versioning].[CompareAndRollbackSchema]') IS NOT NULL
    DROP PROCEDURE [versioning].[CompareAndRollbackSchema]
GO

IF OBJECT_ID('[versioning].[CompareAndAlterSchema]') IS NOT NULL
    DROP PROCEDURE [versioning].[CompareAndAlterSchema]
GO

IF OBJECT_ID('[versioning].[AssertSchemaVersion]') IS NOT NULL
    DROP PROCEDURE [versioning].[AssertSchemaVersion]
GO

IF OBJECT_ID('[versioning].[CompareWithCurrentSchemaVersion]') IS NOT NULL
    DROP PROCEDURE [versioning].[CompareWithCurrentSchemaVersion]
GO

IF OBJECT_ID('[versioning].[CompareSchemaVersions]') IS NOT NULL
    DROP PROCEDURE[versioning].[CompareSchemaVersions]
GO

IF OBJECT_ID('[versioning].[ChangeSchemaVersion]') IS NOT NULL
    DROP PROCEDURE [versioning].[ChangeSchemaVersion]
GO

IF OBJECT_ID('[versioning].[RollbackSchemaVersion]') IS NOT NULL
    DROP PROCEDURE [versioning].[RollbackSchemaVersion]
GO

IF OBJECT_ID('[versioning].[FindNextVersion]') IS NOT NULL
    DROP PROCEDURE [versioning].[FindNextVersion]
GO

IF OBJECT_ID('[versioning].[GetVersionParts]') IS NOT NULL
    DROP PROCEDURE [versioning].[GetVersionParts]
GO

IF OBJECT_ID('[versioning].[GetSchemaVersion]') IS NOT NULL
    DROP PROCEDURE [versioning].[GetSchemaVersion]
GO

IF OBJECT_ID('[versioning].[SchemaVersion]') IS NOT NULL
    DELETE FROM [versioning].[SchemaVersion]

IF OBJECT_ID('versioning.SchemaVersion') IS NOT NULL
    DROP TABLE [versioning].[SchemaVersion]
GO

IF EXISTS (SELECT * FROM sys.schemas WHERE name='versioning')
    EXEC sp_executesql N'DROP SCHEMA [versioning]'
GO

When this script is executed, the database is left completely empty. It is possible, of course, to drop the database entirely, along with associated login and user:

-- _DROP_DATABASE.sql
-- Drops the Demo database.

USE Demo

IF EXISTS (SELECT * FROM dbo.sysusers WHERE name='demo')
    DROP USER demo
GO

USE master
GO

ALTER DATABASE Demo SET single_user WITH ROLLBACK IMMEDIATE

IF EXISTS (SELECT * FROM sys.databases WHERE name='Demo')
    DROP DATABASE Demo
GO

IF EXISTS (SELECT * FROM dbo.syslogins WHERE name='demo')
    DROP LOGIN demo
GO

Demonstration Recap

In previous sections we have first produced this set of scripts (in order of their application):

            
_CREATE_DATABASE.sql
ALTER_01_00.sql
ALTER_01_01.sql
ALTER_01_02.sql
                
    

The first two scripts were there to establish the database and versioning schema, namely to establish schema version 1.0. The following two alters are custom alters, and they contain statements meant to create database objects according to user requirements.

Each of the scripts is accompanied with its inverse script, the one that negates effects of the original script. These are, in order of their application (which is opposite to the order in the first list):

            
ROLLBACK_01_02.sql
ROLLBACK_01_01.sql
ROLLBACK_01_00.sql
_DROP_DATABASE.sql
                
    

As these scripts are executed, one at the time, the database gradually shrinks and finally disappears.

User Guide

In this section we are going to provide a precise sequence of steps to perform in order to create proper database schema upgrades and rollbacks. Some advices will also follow, which can help ease the process of upgrading the database and avoiding troubles.

Establishing Schema Version 1.0.0.0

In order to use this system, each database must first be brought to schema version 1.0.0.0. This is done by executing the ALTER_01_00.sql listed above (with database name set appropriately). Only after this script has been executed can we start using the versioning stored procedures.

Setting Meaningful Version Values

When upgrading a database, it is good practice to allot certain version numbers to alters in progress. One scheme that we are using is to add meaning to build number (third segment of the version number). Even values represent completed alters. Odd values represent incomplete alters. When build segment is odd, revision segment indicates how many steps in the current alter have been completed.

For example, consider schema version 2.24.18.2214. Following alter would begin with setting the next odd value to build number: 2.24.19.0. This version clearly indicates that alter has been initiated but nothing was done yet. Statements in the alter would simply increment the revision number. In that sense, version would follow values 2.24.19.1, 2.2.4.19.2, etc. Finally, once all steps in the alter are executed, version number would indicate total number of statements in the alter (e.g. 2.24.19.22).

After this, we change version number to next stable value, i.e. value with even build number, e.g. 2.24.20.17962. In this version number, all segments are as required by the software versioning process. The only limitation is that build number must be even.

Beginning the Database Alter Script

Every alter should start with a single call to versioning.CompareAndAlterSchema stored procedure, which asserts current schema version and updates it to the next available even build number:

EXEC [versioning].[CompareAndAlterSchema] '2.24.18.2214', '2.24.19.0', NULL

Implementing Statements Consisting the Database Alter

Each step in alter should have its dedicated revision number.

One step should be exactly one SQL statement.

Revision number should increase in steps of one.

By following these rules, we know exactly which is the last statement of the alter that has been executed. As a consequence, alter can be re-run or stopped and continued without fear that it could damage the database.

EXEC [versioning].[CompareAndAlterSchema] '2.24.19.11', '2.24.19.12',
    N'ALTER TABLE [client].[RegisteredUser]
      ADD [EmailAddress] VARCHAR(100) NULL'

Ending the Database Alter

Database alter should end with a single call to versioning.CompareAndAlterSchema stored procedure. This call should change schema version to next available even value of the build segment:

EXEC [versioning].[CompareAndAlterSchema] '2.24.19.22', '2.24.20.17962', NULL

Reverting the Alter

Whenever writing a database alter, immediately create appropriate rollback script.

For each statement in the database alter, write one call to versioning.CompareAndRollbackSchema with inverse statement.

EXEC [versioning].[CompareAndRollbackSchema] '2.24.19.12', '2.24.19.11',
    N'DROP TABLE [client].[RegisteredUser]'

If there is no sense to perform any inverse action, then still call the stored procedure with proper version numbers and NULL statement. For example, there is no sense in removing default value for a column if that column is about to be dropped.

EXEC [versioning].[CompareAndRollbackSchema] '2.24.19.12', '2.24.19.11', NULL

Statements in the rollback script should appear in opposite order of corresponding statements in the original database script.

Rollback script should end with a call to versioning.CompareAndRollbackSchema which establishes schema version which was in effect before the database script being reverted was applied:

EXEC [versioning].[CompareAndRollbackSchema] '2.24.19.0', '2.24.18.2214', NULL

---