Idempotent statements are those where applying the statement multiple times yields the same result. Commonly in database development this is significant during deployment activities.

If we create a new table the deployment script will merely have the CREATE TABLE syntax. Normally this is fine. However, what about the second time you run the statement?

The script will error.

Ideally you would want to be able to run the script multiple times where at the end of each run the database is left in the same expected state without error. In order to do this you would normally perform existence checks before running the statement.

While SSDT is a great tool for internal development and deployment this is one area it is lacking since it does not offer any options for existence checking.  This is why we have pre-deployment scripting that checks things such as expected version and application name, and why database drift is a problem when using SSDT to generate deployment scripts.

Obviously, during the creation of the deployment script there is a review process and during that process someone could make the script fully idempotent, but that can require significantly more time depending on the number of objects that changed. A balance is typically made between making the script fully idempotent and expecting the database to be in a known state.

However, there are times when it needs to be done regardless, such as when you need to account for shipped hotfixes, etc.

This is one area that I believe Redgate SQL compare is a bit more advanced.

Phil Factor put together a good list of statements to perform existence checks over a variety of scenarios in his stairway article on idempotent statements.
Do you have questions or comments?

Let’s discuss below.