Batches

A batch is a set of Transact-SQL statements that are sent to and executed by SQL Server as a single unit. The most important characteristic of a batch is that it is parsed and executed on the server as an undivided entity. In some cases, batches are created implicitly. For example, if you execute a set of Transact-SQL statements from Query Analyzer, the program will treat that set as one batch and do so invisibly:

      Insert Into Part (Make, Model, Type)      Values ('Toshiba', 'Portege 7010CT', 'notebook')      Insert Into Part (Make, Model, Type)      Values ('Toshiba', 'Portege 7020CT', 'notebook')      Insert Into Part (Make, Model, Type)      Values ('Toshiba', 'Portege 703OCT', 'notebook') 

Some tools, such as the Query window in Management Studio and SQLCMD in SQL Server 2005, and Query Analyzer, osql, and isql in earlier versions of SQL Server, use the Go command to divide Transact-SQL code into explicitly set batches. In the following example, the code for dropping a stored procedure is in one batch and the code for creating a new stored procedure is in another. The batch is explicitly created using the Go command.

      If Exists (Select * From sysobjects                 Where id = object_id(N'[dbo].[prPartList]')                 And OBJECTPROPERTY(id, N'IsProcedure') = 1)         Drop Procedure [dbo].[prPartList]      Go      Create Procedure prPartList      As          Select * from Part      Return 0      Go 

In a Query window of Management Studio, you can highlight part of the code and execute it. Management Studio treats the selected piece of code as a batch and sends it to the server and ignores the rest of the code (see Figure 5-1).

Figure 5-1: Executing selected code in the Query window

In other utilities and development environments, batches may be divided in some other manner. In ADO, OLE DB, ODBC, and DB-Library, each command string prepared for execution (in the respective object or function) is treated as one batch.

Note 

In SQLCMD (and command line tools in earlier version of SQL Server), the Go command has a different function. It is also a signal to the tool to start executing the code entered so far.

Using Batches

Batches reduce the time and processing associated with transferring statements from client to server, as well as that associated with parsing, compiling, and executing T-SQL statements. If you need to execute a set of 100 Insert commands against a database, it is preferable to group them in one batch rather than send them to the server as 100 separate statements. The overhead involved in sending 100 separate statements and receiving 100 separate results is very high. Network traffic will be increased unnecessarily, and the whole operation will be slower for the user.

Batches and Errors

The fact that the batch is compiled as an undivided entity has interesting implications for statements that contain syntax errors. Results will vary according to whether the syntax error occurs in a statement or in the name of a database object. If you create a batch that includes a statement containing a syntax error, the whole batch will fail to execute.

Consider the following batch:

      Insert into Part (Make, Model, Type)      Values ('Toshiba', 'Portege 7020CT', 'Notebook'}      Selec * from Part 

It consists of two commands, the second of which contains a syntax error—a missing letter in the Select keyword. If you execute this batch, SQL Server will not compile or execute it but will return the following error:

      Server: Msg 170, Level 15, State 1, Line 3      Line 3: Incorrect syntax near 'Selec' 

If you make a typo in the name of the database object (for instance, in a table or column name), the situation is very different. Note that the name of the table in the following Insert statement is incorrect:

      Insert into art (Make, Model, Type)      Values ('Toshiba', 'Portege 7020CT', 'Notebook'}      Select * from Part 

In this example, the application will notice an error and stop execution as soon as it encounters it:

      Server: Msg 208, Level 16, State 1, Line 1      Invalid object name 'art'. 

SQL Server executes the batch in three steps: it parses, compiles, and then executes. In the first phase, SQL Server verifies batch syntax. It focuses on the sequence of keywords, operators, and identifiers. The first batch used a statement with an error in a keyword. SQL Server picked up the error during the parsing phase.

The error in the second batch (an invalid object name) was picked up during execution. To further demonstrate this fact, let's investigate the following example, where the error is in the second statement:

      Insert into Part (Make, Model, Type)      Values ('Toshiba', 'Portege 7020CT', 'Notebook'}      Select * from art 

In this case, the application behaves differently:

       (1 row(s) affected)      Server: Msg 208, Level 16, State 1, Line 1      Invalid object name 'art'. 

Both commands are parsed and compiled, then the first command is executed, and finally the second command is canceled. Users with experience of early versions of Microsoft SQL Server remember that such a scenario would produce very different results in those early versions.

Microsoft SQL Server 2005 supports deferred name resolution (actually introduced in SQL Server 7.0). Deferred name resolution allows the server to compile Transact-SQL statements even when dependent objects do not yet exist in the database. This feature can prove to be very useful when you are creating or transferring objects from one database or server to another. You do not have to worry about dependencies and the order in which objects are created. Unfortunately, the introduction of this feature also has some strange secondary effects. In the case of the last example,

• The server has successfully compiled a batch, since the name resolution is not part of the compilation;

• The first command was executed without a problem; and

• When a problem was encountered in the second command, the server canceled all further processing and returned a runtime error.

Keep this problem in mind when writing batches. Developers in modern programming languages like Visual Basic or Visual C++ usually employ sophisticated error-handling strategies to avoid situations like this. Transact-SQL also contains programming constructs for error handling. We will explore them in the Chapter 6.

The situation could be worse. Particular runtime errors (for example, constraint violations) do not stop execution of the batch. The following case attempts to use an Insert statement to insert a value in the identity column:

      Select PartId, Make + ' ' + Model Part from Part      Insert into Part (PartId, Make, Model, Type)      Values (1, 'IBM', 'Thinkpad 390D', 'Notebook')      Select PartId, Make + ' ' + Model Part from Part      Go 

The result is a "partial failure":

      PartId        Part      -----------------------------------------------------------      1             Toshiba Portege 7020CT      (1 row(s) affected)      Server: Msg 544, Level 16, State 1, Line 1      Cannot insert explicit value for identity column in table      'Part' when IDENTITY_INSERT is set to OFF.           PartId   Part      ------------------------------------------------------------           1        Toshiba Portege 7020CT           (1 row(s) affected) 

In some cases, "partial successs" may be tolerable, but in the real world it is generally not acceptable.

Let's investigate a case in which several batches are written, divided by a Go statement, and executed together. Although the user has issued a single command to execute them, the client application will divide the code into batches and send them to the server separately. If an error occurs in any batch, the server will cancel its execution. However, this does not mean that execution of the other batches is canceled. The server will try to execute the next batch automatically.

In some cases, this may be useful, but in most cases, it may not be what the user expects to happen. In the following example, one column needs to be deleted from the Part table. One way to perform this action (very popular until we were spoiled;) with fancy tools like Enterprise Manager, Management Studio, or the Alter Table… Drop Column statement) would be to do the following:

1. Create a provisional table to preserve the information that is currently in the Part table.

2. Copy information from the Part table to the provisional table.

3. Drop the existing Part table.

4. Create a Part table without the column you want to delete.

5. Copy the preserved information back to the Part table.

6. Drop the table.

The code necessary to implement this functionality could be created in a set of five batches:

      Create Table TmpPart (PartId int,                            Make varchar(50),                            Model varchar(50}} GO      Insert into TmpPart (PartId, Make, Model)      Select PartId, Make, Model from Part      GO      Drop Table Part      GO      Create Table Part (PartId int,                         Make varchar(50),                         Model varchar(50)) GO Insert into Part (PartId, Make, Model) Select PartId, Make, Model from TmpPart GO Drop Table TmpPart GO 

In theory, this set of batches would work perfectly. However, there is just one problem—it doesn't take errors into account. For example, if a syntax error occurs in the first batch, the temporary table will not be created. Part information will not be preserved in it, and when the code drops the table, the information will be lost. To observe a method that you can use to handle errors, read the next chapter.

DDL Batches

Data Definition Language (DDL) is that part of Transact-SQL dedicated to the creation and modification of database objects. Some DDL statements must stand alone in the batch, including the following statements:

Create Procedure	Create Trigger	Create Default
Create Rule	Create View	Create Function
Set Showplanjext	Set Showplan_All	Set Showplan_XML

If any of these statements is combined with other statements in a batch, the batch will fail. Create statements must stand alone because every other statement that follows them will be interpreted as a part of the Create statement. Set Showplan_Text, Set Showplan_All, and Set Showplan_XML must stand alone in the batch because they are setting how SQL Server 2005 processes following batches and shows execution plans.

Self-sufficient Content

During compilation, the batch is converted into a single execution plan. For this reason, the batch must be self-sufficient. In the real world, this concept has vast implications for the scope of database objects, variables, and comments.

Scope of Objects

Some DDL statements can be inside batches together with other commands, but keep in mind that the resulting object will not be accessible until the batch is completed. For example, it is not possible to add new columns to the table and to access those new columns in the same batch. Therefore, the following batch will fail:

      Alter Table dbo.Part ADD Cost money NULL      select PartId, Cost from dbo.Part      Go 

The Select statement is not able to access the Cost column, and the whole batch will fail:

      Server: Msg 207, Level 16, State 3, Line 1      Invalid column name 'Cost'. 

Therefore, the batch has to be divided in two:

      Alter Table Part ADD Cost money NULL      Go      Select PartId, Cost from Part      Go 

Note 

Some DDL statements can be combined witb DML statements tbat reference tbem. For example, it is possible to create a table and insert records into it in the same batcb. Tbis is very important wben you are working witb temporary tables.

Scope of Variables

All variables referenced in a batch must also be declared in that batch. The following code will result in the failure of the second batch:

      Declare @Name as varchar (50)      Go      Select @Name = 'Dejan'      Go