Home
Do you ignore Idempotency?
  v3.0 Posted at 20/10/2010 7:57 PM by System Account

Many developers worry about Idempotency. They make sure that their scripts can run multiple times without it affecting the database, upon subsequent running of the script.

This usually involves a check at the start to see if the object exists or not.
eg. If this table exists, then don't create the table.

Seems popular, seems like a good idea, right?  Wrong! And here is why.

Database scripts should be run in order (into separate sequential files), as per the rule Do you script out all changes?

Therefore developers should not worry about idempotency, as the script will run in the order it was created. Actually, if they are doing this, then *they want to see the errors*. It means that the database is not in the state that they expect.

IF EXISTS (SELECT 1 FROM 
               INFORMATION_SCHEMA.TABLES 
           WHERE 
               TABLE_TYPE='BASE TABLE' AND 
               TABLE_NAME='Employees'
           ) 
    ALTER TABLE [dbo].[Employees]( …… ) ON [PRIMARY] 
ELSE 
    CREATE TABLE [dbo].[Employees]( …… ) ON [PRIMARY]
Bad example – worrying about the idempotency should not be done, if you plan to run your scripts in the order they were created
CREATE TABLE [dbo].[Employees](
    ……
) ON [PRIMARY]
Good example – not worrying about the idempotency. If errors occur we don’t want them to be hidden + it is easier to read
Figure: Viagra isn't the cure to your Idempotency problems
See the concept of Idempotence on WikiPedia

Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address:

    Comments: