Make Deployable SQL Scripts Idempotent

Problem

Imagine you’ve spent weeks preparing a software release for production, only to deploy it late one night and receive an error that the table or column already exists. This occurs in production environments, even when you use migration-based deployment methods such as DbUp. How can you ensure or at least reduce the likelihood of an error like this in the future?

Solution

In this article, I’ll argue that SQL deployment scripts should be idempotent, as this dramatically reduces the likelihood of errors. First, I’ll define idempotence and then explain how to write idempotent scripts. Next, I’ll explain why you should make your scripts idempotent, even when using a migration-based deployment tool like DbUp. By the end of this article, you’ll understand why any script used in a deployment should be idempotent.

Breaking Down Idempotent

The first part, idem, comes from Latin and means “same,” which is where the “do it again and nothing changes” idea comes from.

If, like me, you have a hard time even pronouncing this word, here is a phonetic respelling: “EYE-dim” + “potent.”

Idempotent means a process that, when executed multiple times, produces the same final state each time. Said another way, you can execute a function or procedure multiple times, and after the first successful run, there are no additional changes between executions. This also assumes nothing else changes between executions, for example, the database version and settings stay the same.

Is idempotent the same as deterministic? The answer is no. A key difference is that a deterministic function always returns the same output for the same input. An idempotent script doesn’t guarantee the same output. It only ensures that rerunning it won’t introduce additional side effects, so the end state remains the same.

Idempotent DDL Scripts

The term DDL (Data Definition Language) refers to a set of statements used to modify a database’s structure. A few examples of DDL statements include the following:

/* MSSQLTips.com */
CREATE TABLE TableA (Column1 INT NOT NULL);
GO
ALTER TABLE dbo.TableA ADD Column2 NVARCHAR (100) NOT NULL;
GO
DROP TABLE dbo.TableA;
GO

How do you determine whether a DDL script is idempotent? A quick way is to ask yourself, “What happens if this runs twice?” If the answer is “an error or other negative outcome,” it’s likely not idempotent. First, let’s take a look at a DDL script that is not safe to rerun.

/* MSSQLTips.com */
CREATE TABLE dbo.TableA (Column1 INT NOT NULL PRIMARY KEY);
GO

If we run the script above twice, the second execution produces the following error message.

Results:

Msg 2714, Level 16, State 6, Line 1
There is already an object named 'TableA' in the database.

Now, how can we ensure the script will not error on the second or even the 100th execution? The code below will do the job.

/* MSSQLTips.com */
IF OBJECT_ID('dbo.TableA', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.TableA (Column1 INT NOT NULL PRIMARY KEY);
END
GO

Why not just use DROP TABLE IF EXISTS

Someone might argue that using DROP TABLE IF EXISTS is a better option than the script used above. However, if the script below is run on a mature table, something has undoubtedly changed, such as the addition of data, a foreign key reference, or new indexes. Dropping the table would destroy the data and indexes and cause a foreign key constraint error, so I wouldn’t consider this a desirable idempotent script. As mentioned above, idempotent scripts generally do not cause destructive side effects.

/* MSSQLTips.com */
DROP TABLE IF EXISTS dbo.TableA;
GO
 
CREATE TABLE dbo.TableA (Column1 BIGINT NOT NULL);
GO

Deploying SQL Scripts

I prefer migration-based deployment for database objects over a state-based approach. Notice I used the word prefer because I believe state-based is a solid approach. However, the main reason I like a migration-based approach is the granular control it provides, which allows complex changes. Over the past several years, I’ve used DbUp, a .NET library for executing SQL scripts in a few applications. DbUp prevents repeated executions by maintaining a history table of executed scripts. If you would like a short article on the pros and cons of each method, read “CI/CD for Databases: State-Based vs. Migration-Based Deployments.”

I’ve heard the argument that such a history/version mechanism ensures idempotence, but to me it doesn’t, because something could change outside the usual deployment process. For example, imagine you have a system down and need to fix it immediately. In the perfect world, you would create a new release and deploy it, but life doesn’t always go perfectly. Any fix you apply in production likely won’t be added to DbUp and could cause issues down the road. That’s why I would still make the script idempotent.

Idempotent DML Scripts

The concept of idempotent scripts for modifying the database schema is pretty straightforward. When you are dealing with DML (Data Modification Language) scripts, it’s a bit trickier. Here are a few tasks you might do in a DML script where idempotence would apply:

Let’s use the INSERT statement as our DML example going forward. Microsoft defines the INSERT statement as adding one or more rows to a table or a view in SQL Server. Let’s imagine we are populating a lookup or dimension table that captures the various statuses (New, Complete, Active, etc.) of an entity.

First, we need to create our table; based on what we learned above, the script could look like this:

/* MSSQLTips.com */
IF OBJECT_ID('dbo.EntityStatus', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.EntityStatus(Id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, StatusName NVARCHAR(100)
    );
END
GO
 
INSERT INTO dbo.EntityStatus
(
    StatusName
)
VALUES
(N'New'),
(N'Complete'),
(N'Active');
GO

We have now created and populated the table.

What would happen if the query above were re-executed?

/* MSSQLTips.com */
IF OBJECT_ID('dbo.EntityStatus', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.EntityStatus(Id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, StatusName NVARCHAR(100)
    );
END
GO
 
INSERT INTO dbo.EntityStatus
(
    StatusName
)
VALUES
(N'New'),
(N'Complete'),
(N'Active');
GO
 
SELECT 
      Id,
      StatusName
FROM dbo.EntityStatus;
GO
Results with Duplicates

When we define a duplicate as a record with the same StatusName, we have a few duplicates, as shown in the screenshot above.

Solution for unique data

If we are only supposed to have a single row per StatusName, it might be helpful to add a unique index, as shown in the code below. However, we first need to delete the duplicate records.

/* MSSQLTips.com */
DELETE FROM dbo.EntityStatus
WHERE Id > 3;
GO
 
DROP INDEX IF EXISTS UX_EntityStatus_StatusName ON dbo.EntityStatus;
GO
 
CREATE UNIQUE INDEX UX_EntityStatus_StatusName
ON dbo.EntityStatus (StatusName);
GO

That unique index prevents duplicate statuses in the column, but it still fails when we try to insert duplicate rows.

Another approach using not exists

What would be an idempotent way to run the INSERT statement to prevent this problem? I usually use a NOT EXISTS predicate with a table construct.

/* MSSQLTips.com */
INSERT INTO dbo.EntityStatus
(
    StatusName
)
SELECT v.StatusName
FROM
(
    VALUES
        (N'New'),
        (N'Complete'),
        (N'Active')
) v (StatusName)
WHERE NOT EXISTS
(
    SELECT 1 FROM dbo.EntityStatus es WHERE es.StatusName = v.StatusName
);
GO

Now, when we rerun the insert, there is no error message.

Check if table and columns exist

You could take this one step further and first check if the table and column actually exist, like in the example below:

/* MSSQLTips.com */
IF EXISTS
(
    SELECT 1
    FROM sys.tables t
        JOIN sys.schemas s
            ON s.schema_id = t.schema_id
        JOIN sys.columns c
            ON c.object_id = t.object_id
    WHERE s.name = N'dbo'
          AND t.name = N'EntityStatus'
          AND c.name = N'StatusName'
)
BEGIN
    INSERT INTO dbo.EntityStatus
    (
        StatusName
    )
    SELECT v.StatusName
    FROM
    (
        VALUES
            (N'New'),
            (N'Complete'),
            (N'Active')
    ) v (StatusName)
    WHERE NOT EXISTS
    (
        SELECT 1 FROM dbo.EntityStatus es WHERE es.StatusName = v.StatusName
    );
END;
GO

The code above first checks that the schema, column, and table exist, then performs the insert. Some might say this is overkill, but it could prevent your database deployment from failing, especially if you create the table in the same deployment and then populate it later with a second script.

Downside

A drawback of making your scripts idempotent is the additional code required. More code typically takes longer to develop and can increase the risk of bugs. Additionally, you have to check whether an item exists before creating it, which can increase the server’s overall load. To me, avoiding a failed deployment outweighs the costs of those drawbacks. Also, for DDL scripts, I don’t make hundreds of updates per release, so it shouldn’t be too taxing on the server. But your environment might be different.

Conclusion

In this article, I argued that to reduce T-SQL script deployment issues, including errors and duplicate data, making your scripts idempotent is a robust approach. When your scripts are idempotent, you can run them multiple times without negatively affecting your database. You can make both DDL and DML scripts idempotent, but DDL has the greatest potential for idempotency. There are certainly scripts where it makes little sense or is impractical to make them safe for retry. For example, if you want a script to insert a log record each time it’s run for audit purposes. However, I believe you should always default to asking yourself, “Can I make this script idempotent?”

Next Steps

  • A tool that comes in handy when inserting data is the VALUES clause, also called the table constructor. Joe Celko wrote the informative article, “The VALUES clause or building tables out of nothing,” which explores the topic in detail.
  • If you are interested in learning more about the NOT EXISTS predicate, I wrote an article titled “Find Missing SQL Server Data with NOT EXISTS.” In it, I argue for using NOT EXISTS over the LEFT OUTER JOIN when checking for the existence of data.
  • Since we look at some if logic in this article, it might be beneficial to review Eric Blinn’s easy-to-read article on IF statements, “SQL IF Statement for Conditional Logic.”

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *