Idempotency tips for SQL Server database creation scripts

If you run the same SQL Server database/table creation script more than once, it should not fail just because an object (table, view, index, etc.) already exists. This is when idempotent database scripts are useful.

For table creation, the basic idea is simple: check first, then create only if needed. In SQL Server, a common way to do that is with OBJECT_ID, which can be used to verify whether a table already exists.

Let's take a simple example:

IF OBJECT_ID(N'dbo.Customers', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.Customers
    (
        CustomerId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        Name NVARCHAR(100) NOT NULL,
        CreatedUtc DATETIME2 NOT NULL
    );
END

Here, the N'U' parameter means a user (U) table. If dbo.Customers already exists, the CREATE TABLE statement is skipped and no errors are reported.

Another option is to check the system catalog view sys.tables. That can be useful if you prefer a more explicit metadata query. SQL Server exposes one row in sys.tables for each user table in the current database.

IF NOT EXISTS
(
    SELECT 1
    FROM sys.tables
    WHERE name = N'Customers'
      AND schema_id = SCHEMA_ID(N'dbo')
)
BEGIN
    CREATE TABLE dbo.Customers
    (
        CustomerId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        Name NVARCHAR(100) NOT NULL,
        CreatedUtc DATETIME2 NOT NULL
    );
END

A few practical tips:

• Always use the schema name such as dbo.Customers.
• Keep the existence check and the CREATE TABLE close together in the same script.
• Prefer repeatable scripts for deployments, local setup, and test environments.
• Use separate ALTER TABLE steps for later schema changes instead of rewriting the original create script.

Another small tip: creating a table only when it does not exist is good for initial setup, but it does not handle schema changes. If the table exists with the wrong columns or constraints, the script will still skip creation. In that case, you need explicit migration logic.

In summary, make your database creation scripts safe to run more than once. For SQL Server tables, checking with OBJECT_ID or sys.tables before CREATE TABLE is an easy place to start.

Hope this helps!