Today I want to share some tips for writing and maintaining database change or migration scripts.
Never assume the state of the target database. If your queries are written in such a way that they can be executed over and over, you will have accomplished this goal. This is helpful so that you don’t have to worry about accidentally running them again, or you can continue to add steps to them incrementally. Some of the things you would need to address are:
Before adding or deleting a table column, check the existence first.
Before adding data to a table, make sure the data does not already exist. I like to define the new data in a CTE or # table. Then I can LEFT OUTER JOIN to the table into which I want to insert, and only add the records from my temp table having no match in the permanent table.
Constraints can be an issue if you are managing data or modifying schema. In that case, it is a lot easier to maintain a migration or change script that covers all of the tasks you need to accomplish. This way you can be sure the modifications are executed in the necessary order.
If you need to test for existence of an object before executing your modifying command, and you cannot separate the commands with GO, then you can encapsulate your command in a string, and use the EXEC (‘SQL String’) technique. This allows your script to compile while allowing multiple commands that would require a batch separation to be part of a single transaction.
Embed your change script in a Transaction. Use Try/Catch to make sure they all succeed, or none succeed. In this way, you can fix errors that are expressed, and run the script without fear of not being able to recover from a partial execution.
Cheers,
Ben