SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Making Deployments Simpler with Re-runnable Scripts

By Ed Elliott, (first published: 2015/11/17)

When we deploy code to SQL Server we do it through either one or more T-SQL scripts. Whether these scripts are manually created or automatically generated using a tool like SSDT or SQL Compare we should make sure that where possible the scripts are re-runnable or easily re-generated. What we gain when scripts are re-runnable is the ability to remove a lot of uncertainty from releases and the ability to quickly recover from failure or mistake and it makes the job of actually performing a release easier on the person who does it.

Idempotent

The first thing that we need to do is to start thinking about writing scripts so that they are idempotent which means that no matter how many times you do something you always end up with the same result.

This is an example of an idempotent script in T-SQL:

  if object_id('abc.def') is not null
  begin
    drop procedure abc.def
  end
GO
  create procedure abc.def
  as
    select 1
GO

What happens is that we check if the stored procedure exists and if it does then we drop it so when we get to the create statement we know that it will succeed (given that the code compiles).

Types of objects

There are different types of objects in SQL Server and different types of changes we can make to them. Top level objects such as tables or stored procedures all have an entry in sys.objects and return a valid object_id when you call object_id in the database that contains them. Other changes such as adding a column to a table cannot be verified using object_id as it is not a top level object.

To handle non-top level objects we need to query the database to see if the object exists and we should really be using the INFORMATION_SCHEMA views.

For example if we want to see if a column called last_name exists on the person table and add the column if it does not exist we can do:

  
  if not exists(
    select * from INFORMATION_SCHEMA.COLUMNS
      where TABLE_NAME = 'person' and COLUMN_NAME = 'last_name')
  begin
              
    alter table person
      add last_name varchar(200) null
  end

Further if we want to change the definition of a column then we can check that to see if we need to do it or not:

  if not exists(
    select * from INFORMATION_SCHEMA.COLUMNS
      where TABLE_NAME = 'person' and COLUMN_NAME = 'last_name' and CHARACTER_MAXIMUM_LENGTH = 255)
    begin
                
      alter table person
        alter column last_name varchar(255) null
  end

If we use this verification before making any modifications then we ensure that we can keep re-running the script and we will get the same results and when you are under pressure knowing that you can re-run any script is a benefit that you only really appreciate when you are under pressure.

Manual Scripts / Automatic Scripts

When we use tools to generate scripts anything that the tool generates such as create / alter steps can be regenerated quickly using those tools so if you have deployed half of a script generated with SQL Compare then the best thing to do is to run another compare and then run the updated script. Even if you use a tool to generate your deployment scripts then you may well still be using a T-SQL script to deploy your reference data.

What is Reference Data?

This is the data that is in your database that is not part of the business data, the valuable data. The reference data is typically used to ensure referential integrity so if you have an employee_type table with an employee_id of 1 with an employee_type of 'Full Time''. Having reference data means we can ensure referential integrity and often should match the same values that exist in code enums.

How can we ensure Reference Data is Idempotent?

The simplest way to ensure that data deployment scripts is to use what is arguably the most complicated DML statement, the MERGE statement. If we look at our employee_type table:

  create table hr.employee_type(
    employee_type_id int identity(1,1) not null,
    employee_type varchar(12) not null,
    constraint [pk_employee_type] primary key clustered (employee_type_id)
  )

To make deployments to this table idempotent, I would suggest removing the identity on the employee_type_id especially when you need to match the same id that exists in the application code. If you do not remove it then you need to identity insert on and off again when you have finished and this now means unless you take some sort of lock on the table or some other object you cannot run two deployment scripts at the same time as you could get into a classic race condition such as:

  • Script 1 - set identity insert on
  • Script 1 - run data modification
  • Script 2 - set identity insert on
  • Scrtipt1 - set identity insert off
  • Script 2 - run data modification <-- this will fail

If we remove the identity for this column and because we are inserting known values we really do not need the identity column the table definition becomes:

  create table hr.employee_type(
    employee_type_id int not null,
    employee_type varchar(12) not null,
    constraint [pk_employee_type] primary key clustered (employee_type_id)
  )

Of course to remove the identity from an existing system we can do it in an idempotent way:


begin tran
  --does the column exist with an identity column
if exists (select * from sys.columns where object_id = object_id('hr.employee_type') and name ='employee_type_id' and is_identity = 1)
begin
  --add temp column - in a separate batch so the 2nd batch compiles
  alter table hr.employee_type
    add type_id_temp int null;

end
go

  --has the temp column been created?
if exists(select * from sys.columns where object_id = object_id('hr.employee_type') and name ='type_id_temp')
begin
  
  exec sp_executesql N'update hr.employee_type set type_id_temp = employee_type_id;';

  --if the primary key is still there then remove it
  if exists(select * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME = 'employee_type' and CONSTRAINT_NAME = 'pk_employee_type')
  begin
    alter table hr.employee_type
      drop constraint [pk_employee_type];
  end

  --re-add constraint on out new column, make it not null first
  if exists(select * from [INFORMATION_SCHEMA].[COLUMNS] where TABLE_NAME = 'employee_type' and COLUMN_NAME = 'type_id_temp' and IS_NULLABLE = 'YES')
  begin
  alter table hr.employee_type
    alter column type_id_temp int not null;
  end

  --drop the original identity column
  if 2 = (select count(*) from [INFORMATION_SCHEMA].[COLUMNS] where TABLE_NAME = 'employee_type' and COLUMN_NAME in ('type_id_temp', 'employee_type_id'))
  begin
  alter table hr.employee_type
      drop column employee_type_id;
  end

  --rename the temp columnt to the original name
  if exists (select * from [INFORMATION_SCHEMA].[COLUMNS] where TABLE_NAME = 'employee_type' and COLUMN_NAME = 'type_id_temp')
  begin
    exec sp_rename 'hr.employee_type.type_id_temp', 'employee_type_id', 'COLUMN';
  end

  --put the primary key constraint back on
  if not exists(select * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME = 'employee_type' and CONSTRAINT_NAME = 'pk_employee_type')
  begin
    alter table hr.employee_type
      add constraint [pk_employee_type] primary key clustered (employee_type_id);
  end

end

commit tran


Now we have our table without the identity constraint we can use a MERGE statement without having to enable identity inserts:

  SET NOCOUNT ON

MERGE INTO [hr].[employee_type] AS Target
USING (VALUES
  ('Full Time',1)
 ,('Contractor',2)
 ,('Part Time',3)
) AS Source ([employee_type],[employee_type_id]) ON (Target.[employee_type_id] = Source.[employee_type_id])
WHEN MATCHED AND (
  NULLIF(Source.[employee_type], Target.[employee_type]) IS NOT NULL OR NULLIF(Target.[employee_type], Source.[employee_type]) IS NOT NULL) THEN

 UPDATE SET
  [employee_type] = Source.[employee_type]
WHEN NOT MATCHED BY TARGET THEN

 INSERT([employee_type],[employee_type_id])
 VALUES(Source.[employee_type],Source.[employee_type_id])

WHEN NOT MATCHED BY SOURCE THEN 
 DELETE;
GO
  

The alternative to using a MERGE statement would be to have 3 separate statements, one to insert any rows that are missing, one to update all the rows to make sure the names are the same - perhaps you want to rename "Full Time" to "Permanent" and a third statement to delete any rows that have been removed from your code. You would also need to do something to make sure that the statements were idempotent so either use if exists or join the data in the script against the live table but it is quite a lot of work to so I would recommend using the MERGE statement.

To help create the MERGE statements you can use the stored procedure sp_generate_merge which is on github: https://github.com/readyroll/generate-sql-merge/blob/master/master.dbo.sp_generate_merge.sql

Writing deployments scripts or making changes to SQL Server databases is often considerably simpler for the person or tool doing the release if they can re-run them or at least re-generate a new script that can be re-run.

Get in the habit of making your scripts idempotent and you will set yourself down a path of more reliable deployments.

 
Total article views: 5494 | Views in the last 30 days: 9
 
Related Articles
FORUM

Adding new columns in an existing table at a particular position using script.

Adding new columns in an existing table at a particular position using script.

FORUM

Generating Scripts for Database Objects

Creating Seperate Scripts Per Object

FORUM

Scripting Objects Including Permissions

Scripting Objects Including Permissions

SCRIPT

Conversion of default objects to column defaults

This script can convert the usage of bound default objects in tables to column defaults

SCRIPT

Conversion of rule objects to column check constraints

This script can convert the usage of bound rule objects in tables to column check constraints

Tags
releases    
 
Contribute