Procedural, Transact SQL — Batches

When building solutions in T-SQL, you’ll often be asked to generate a change script. This change script is a batch of SQL statements that when executed sequentially (and completely) will make a change to the database for some business reason.

When you build these, you can use every tool you’ve learned about SQL, but there are some things you’ll need to keep in mind.

SQL Server will compile all the statements you send in that batch into a single executable unit, called an execution plan. The statements in this execution plan are then executed once.

Any compile error, syntax error, etc, will prevent the compilation of the execution plan. As a result: no statements from your batch will be executed.

But a run-time error will cause additional problems. Most run-time errors will stop at the current statement causing the error and the statements following it won’t be run. If you can wrap your whole code in a transaction, you could then use a try catch, or a manual method, to ROLLBACK your transaction should a run-time error occur.

Constraint violation errors, only stop the current statement. All the remaining statements in the batch should still execute.

If you’re releasing DDL changes via a script, use a new .sql file for each object you’re releasing. First, it makes source control much easier. Second, you can’t chain together multiple DDL statements, at least, not without separating them with GO statements. But then, if you are sharing a variable across these releases, you’re going to run outside the scope of those variables. This could get dicey!

In the future, you’re going to have to learn to make complex change scripts that can alter a table and manage the data within that table in a production environment. These can get very interesting! Remember, when you make a change that you’re not 100% sure you can pull off, test it in your local development environment first.

Test it multiple times.

Ask someone you trust for a sanity check on your code. Better safe than sorry!

Shannon Lowder is the Database Engineer you've been looking for!
Look no further for expertise in:
Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.