Scripts, Batches, and Transactions

T-SQL programs are technically called scripts . A script is usually contained within a text file on disk, which is then loaded into some tool (the Query Analyzer, or the command-line equivalent called OSQL) and can be executed. A script is made of one or more batches . Each batch is made up of zero, one, or more transactions. The following sections look at each of these entities.

Scripts

A script is made up of one or more batches. To separate one batch from another, put the word GO on a line by itself between the batches, like this:

 SELECT * FROM sysobjects WHERE type = 'u' go SELECT COUNT(*) FROM sysobjects 

This script contains two batches, one from the beginning of the file to the word GO , and another from the word GO to the end of the file.

Batches

Knowing how batches work is important for several reasons. Batches determine variable scope. This is covered again later, but you should always remember that a variable can only be used within the batch where it is declared.

NOTE

Working with System Tables This chapter makes heavy use of the sysobjects system table in examples of selecting data. Remember that this is a system table. You can select out of it all if you want, but don't insert or update any system table without a note from your mother. Doing so is a very good way to corrupt a database. So, using SELECT is okay, but nothing else.

SQL Server compiles and runs scripts batch by batch. If you have a script with several batches in it, and one of the batches contains a syntax error, the rest of the batches do execute, but the statement in the batch that had an error does not execute. If one of the statements would cause a constraint violation, then that statement doesn't execute, but all the other statements in the batch do execute.

Other runtime errors, such as arithmetic overflow errors, cause the batch to stop executing at that point, with all the preceding commands executed and none of the following commands executed.

When you use tools such as Query Analyzer or the command-line equivalent, ISQL, the tools themselves send the statements to SQL Server in batches, one batch at a time. SQL Server then compiles the single batch, processes it, and returns for the next batch as necessary. The keyword GO , then, isn't a keyword used by SQL Server; it is actually used by the various tools to determine when batches start and stop.

Batches control how certain statements execute. When you learn more about stored procedures later, in Chapter 9, you'll find that a stored procedure definition has to be in its own batch, and the stored procedure includes everything in the batch. This is an example of some rules you should know about batches:

• You can't add columns to a table and then reference the new columns with an UPDATE or INSERT within the same batch.

• The EXECUTE (or EXEC ) statement isn't required if it's on the first executable line of the batch.

• You can't combine CREATE VIEW , CREATE PROCEDURE , CREATE RULE , CREATE TRIGGER , or CREATE DEFAULT statements in a batch.

Now it's time to add a little flexibility to how you work inside batches. How can you handle counting rows or storing intermediate values? You can't. Yet.