title: How to write idempotent migrations when using migration tooling
body: <p>Consider the two migration scripts below. The scripts are run by a migration tool that executes the script and on success it inserts into a <code>[dbo].[MigrationHistory]</code> to track migration history.</p>
  <p><strong>(1) Check whether the table exists</strong></p>
  <pre class="lang-sql prettyprint-override"><code>-- File: 0050-CreatePersonsTable.sql
  
  -- Only check if the table exists; create if not.
  
  IF NOT EXISTS (
    SELECT TOP 1 *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME = 'Persons'
  )
  BEGIN
    CREATE TABLE [dbo].[Persons] (
      PersonID int,
      LastName varchar(255),
      FirstName varchar(255),
      Address varchar(255),
      City varchar(255)
    )
  END
  </code></pre>
  <p><strong>(2) Check the migration history table</strong></p>
  <pre class="lang-sql prettyprint-override"><code>-- File: 0050-CreatePersonsTable.sql
  
  -- Check migration history table to see if '0050-CreatePersonsTable.sql' 
  -- was already applied
  
  IF NOT EXISTS (
      SELECT * 
      FROM [dbo].[MigrationHistory] 
      WHERE ScriptName = '0050-CreatePersonsTable.sql'
  )
  BEGIN
    CREATE TABLE [dbo].[Persons] (
      PersonID int,
      LastName varchar(255),
      FirstName varchar(255),
      Address varchar(255),
      City varchar(255)
    )
  END
  
  -- On success the migration tool inserts '0050-CreatePersonsTable.sql'
  -- into the [dbo].[MigrationHistory] table.
  </code></pre>
  <p><strong>Question:</strong></p>
  <p>I think the first migration script is a misleading way to write a migration.</p>
  <p>The <code>IF NOT EXISTS (...)</code> predicate is misleading. What if a an earlier executed migration already created the table? Then this migration will be skipped and the database will be in an unexpected state. Now to remedy this I could add an additional check to see if the table exists and then execute a different set of instructions. This is okay but then what if the table exists and the expected columns exists but the data types are wrong (e.g., LastName is an nvarchar). Now I could write <em>another</em> check to see whether the data types are correct and if not then fix them.</p>
  <p>These predicates can quickly get out of control.</p>
  <p>Why not avoid all of this and just check the <code>[dbo].[MigrationHistory]</code> table? The migration tool in this case executes the migrations sequentially. That is 0001 to 0049 migrations are run before <code>0050-CreatePersonsTable.sql</code>. I <em>expect</em> the database to be in a specific state when this migration is applied. If the <code>[dbo].[Persons]</code> table already exists (for whatever reason) then I <em>want the migration to fail</em> because the database is in an unexpected state.</p>
  <p><strong>Claim:</strong> The second migration is the better and safer migration.</p>
  <p>Is there something that I am overlooking?</p>