Idempotent SQL change script

Claus Munch on Dec 2, 2021 · 1 min read

Idempotent SQL change script

Today I had a small chat with one of our developers, about adding a column to a table "the right way". 
I told him, that depending on how they needed to deploy it, it might be beneficial to make it idempotent. 

Heres's an example of that script: 

USE [M42Demo]; 
IF COL_LENGTH('dbo.QA', 'minnyecol') IS NULL 
  BEGIN 
    ALTER TABLE [dbo].[QA] ADD minnyecol VARCHAR(2000) NULL; 
    PRINT 'Added column ''minnyecol'' to [dbo].[QA]'; 
  END 
ELSE 
  PRINT 'Column ''minnyecol'' already exists in [dbo].[QA]';

About the author

Claus Munch has been working with SQL Server in all versions, since 2001. For more than 10 years, he has been running the national SQL Server usergroup. Claus is currently employed by Miracle 42, since 2020.