SQL changes you can run twice

A deployment fails halfway through. You fix the problem and run it again. Does the script pick up where it left off, or stop because the first table already exists?

When creating database change scripts, write them with idempotence in mind. An idempotent operation leaves the same intended state whether you run it once or several times. A second execution should not create another seed row, rewrite an unchanged value, or fail because an earlier execution already added a column.

SQL Server records the database structure in system catalog views. Those views let a script inspect what exists before deciding what needs to change.

Start with an existence check

Adding a column is a useful first example. Verify that the table exists, then look for the column in sys.columns.

IF OBJECT_ID(N'[dbo].[Example]', N'U') IS NULL
    THROW 51000, N'Required table is missing.', 1;

IF NOT EXISTS
(
    SELECT 1
    FROM sys.columns
    WHERE object_id = OBJECT_ID(N'[dbo].[Example]', N'U')
      AND name = N'Description'
)
BEGIN
    ALTER TABLE [dbo].[Example]
        ADD [Description] nvarchar(200) NULL;
END;
GO

On the first run, the script adds the column. On the second, it leaves it alone. The nullable definition also lets existing rows remain valid without inventing a value for them.

The table guard matters: a missing prerequisite should produce a useful error. Run these checks with a deployment account that can see the relevant metadata; an object hidden by permissions can appear absent.

“Exists” is only the first question

Suppose Description already exists as nvarchar(50). The previous script skips it. That is repeatable, but it does not establish the definition your application expects.

For a change that depends on the current definition, inspect the type, length, nullability, and any relevant constraints before altering it. The pack’s idem_widen_nvarchar snippet does this for an ordinary built-in nvarchar column: it checks the type, increases capacity only when needed, preserves nullability, and leaves larger or MAX columns alone.

For other changes, choose the behavior deliberately: retain an existing object, alter it to the required definition, or stop and report an unexpected state. An existence check should not silently stand in for a definition check.

Give seed data the same treatment

A repeated deployment should not add another copy of the same reference row. Use a stable key backed by a primary key or unique constraint, then insert only when that key is missing.

DECLARE @SeedKey int = 1;
DECLARE @SeedValue nvarchar(100) = N'Example';

INSERT INTO [dbo].[Example] ([Id], [Name])
SELECT @SeedKey, @SeedValue
WHERE NOT EXISTS
(
    SELECT 1
    FROM [dbo].[Example] WITH (UPDLOCK, HOLDLOCK)
    WHERE [Id] = @SeedKey
);
GO

The lookup uses update and serializable locking hints to protect the missing-key check during the statement. Keep the unique key as the database’s integrity rule, and account for transient deadlocks in your deployment runner.

Sometimes the seed row should also change when its desired value changes. The pack includes a transaction-based upsert and a separate update snippet that compares the old and new values, including transitions to or from NULL.

DECLARE @DesiredName nvarchar(100) = N'Example';

UPDATE [dbo].[Example]
SET [Name] = @DesiredName
WHERE [Id] = 1
  AND ([Name] <> @DesiredName
       OR ([Name] IS NULL AND @DesiredName IS NOT NULL)
       OR ([Name] IS NOT NULL AND @DesiredName IS NULL));
GO

That avoids rewriting equal values. It does not guarantee that triggers or external side effects are idempotent: SQL Server can fire a statement-level trigger even when no rows change.

Use CREATE OR ALTER for modules

Stored procedures, views, and functions have a convenient alternative to separate existence checks. On SQL Server 2016 SP1 or later, use CREATE OR ALTER for supported module types.

GO
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER PROCEDURE [dbo].[usp_Example]
AS
BEGIN
    SET NOCOUNT ON;
    SELECT CAST(1 AS int) AS [Result];
END;
GO

Each execution establishes the supplied definition. It can still update metadata or trigger DDL auditing, so “same definition” does not mean “no observable activity.” Existing objects must also be of a compatible kind. See the CREATE PROCEDURE reference for the procedure syntax.

What’s in the pack

The same 18 entries are available in both editor formats. Type an idem_ prefix or open the snippet picker, then tab through the editable fields.

Schema and columns

idem_schema, idem_table, idem_column, idem_widen_nvarchar

Constraints and indexes

idem_default, idem_check, idem_fk, idem_index

Procedures, views, and functions

idem_proc, idem_view, idem_function

Seed data

idem_insert, idem_update, idem_upsert

Explicit removals

idem_drop_index, idem_drop_constraint, idem_drop_column

Migration helper

idem_transaction — a transaction with an application lock for cooperating deployments.

The helper does not make arbitrary SQL idempotent. Its body still needs repeatable operations, and every cooperating deployer must use the same lock resource. Likewise, a repeatable drop operation still removes data or an integrity rule.

Install the snippets

Visual Studio Code

Extract the pack and copy vscode/sql-server-idempotent.code-snippets into your project’s .vscode folder. Open a file in SQL language mode and run Insert Snippet from the Command Palette.

For personal use across projects, open Snippets: Configure Snippets → New Global Snippets file, name it sql-server-idempotent, and paste in the provided file’s contents. Its SQL scope keeps the suggestions out of other languages. You can also download the VS Code file directly.

Visual Studio

Extract the pack to a permanent folder. In Tools → Code Snippets Manager, select SQL, choose Add, and select the extracted visual-studio folder. Open a Transact-SQL script and use Insert Snippet from the editor’s context menu.

If SQL is missing from the language list, check that SQL Server Data Tools and the SQL editor components are installed. The download contains native .snippet files; no extension installer is required.

Run it twice before you ship it

Adapt the names and definitions, run the migration in a disposable database, then run it again. Compare the intended schema and data after both executions. Also test the awkward starting states: an existing column with a different type, a disabled constraint, a missing prerequisite, or a seed value that is NULL.

The templates use GO batch separators. Your SQL client must recognize them, or your runner must split the batches. Keep them out of the transaction helper’s TRY/CATCH body, and configure the runner to stop on errors.

The pack’s JSON, XML, placeholder expansions, and archive integrity have been checked. Execution against SQL Server and live imports into either editor have not been verified. The included README describes each template’s assumptions and limits.

A useful migration describes the state you want and checks the state you have. That makes the second run a normal part of deployment, instead of a recovery exercise.