My Technical Notes

Wednesday, 2 April 2014

Writing a simple Database Update SQL Script

When deploying changes to a live system, we want the process to be as simple and self-contained as possible, especially if someone else is tasked with deploying it.

To this end, I have found a simple pattern for writing a SQL change script. The changes to the database should be atomic so that either all changes are applied, or none are applied. Therefore a transaction should be used. Secondly, because some statements lower down in the script depend on structural changes made earlier in the script, we should follow all statements with the statement GO. Note that GO is not a T-SQL statement, but rather a word used by SSMS when interpreting your script.

BEGIN TRANSACTION DBChanges; -- begin a transacton

SET XACT_ABORT ON; -- Roll back everything if error occurs in script

PRINT 'Statement 1'
GO -- follow all statements with 'GO'

PRINT 'Statement 2'

COMMIT TRANSACTION DBChanges; -- commit all changes

No comments: