Idempotent Scripts Required

  • Article

Idempotent, as a former transaction guy I love this word and believe you are not alone if you are not clear on the meaning or impact of this word. Wikipedia is there to help.

Idempotent: In computing, idempotence (IPA /ˈaɪdɛmˌpotn̩s/, like eye-DEM-potence) is the quality of something that has the same effect if used multiple times as it does if used only once, similar to the idempotence notion in mathematics.

So what does this have to do with Pre and Post deployment scripts?

Everything, freely translated idempotent means that we need to be able to execute each script you add as a pre of post deployment script we need to be able to execute over and over again; the first time, after a failure or if you have simply run it before already.

Why?

The answer to that part is simple! Pre and post deployment script are pre-pended or appended AS-IS to the build script by the script pre-processor. In other words the literal text of the pre or post deployment script is copied in to the build script.

Example

Suppose you want to add a file to a filegroup, you can simply add this piece of script

ALTER DATABASE [$(databasename)]
ADD FILE
(
NAME = N'fgdb_data',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fgdb_data.ndf',
MAXSIZE = 100MB,
FILEGROWTH = 10MB
)
TO FILEGROUP [TABLES]

However what if the filegroup already exists? This script will fail, therefore it is not idempotent, so lets make it idempotent.

IF NOT EXISTS(SELECT 1 FROM dbo.sysfiles WHERE name = 'fgdb_data')
BEGIN
ALTER DATABASE [$(databasename)]
ADD FILE
(
NAME = N'fgdb_data',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fgdb_data.ndf',
MAXSIZE = 100MB,
FILEGROWTH = 10MB
)
TO FILEGROUP [TABLES]
END

Bottom line this means that what ever you add to your pre and post deployment script, needs to deal with the fact an object might already exist if you try to add it, does not exit if you try to alter or drop it.  You need to be able to repeatable run the same script and always have the same result, it always succeeds, even if it was ran before (that is the most common case) or it always fails. Same inputs always yield the same outputs/results.

I hope this helps you to write better pre- and post-deployment scripts.

-GertD