Batches
A batch is a group of one or more Transact-SQL statements sent at the same time from an application to SQL Server 2005 for execution. SQL Server compiles the statements of a batch into a single executable unit, called an execution plan. The statements in the execution plan are then executed one at a time.
A compile error, such as a syntax error, prevents the compilation of the execution plan. Therefore, no statements in the batch are executed.
A run-time error, such as an arithmetic overflow or a constraint violation, has one of the following effects:
- Most run-time errors stop the current statement and the statements that follow it in the batch.
- Some run-time errors, such as constraint violations, stop only the current statement. All the remaining statements in the batch are executed.
The statements that are executed before the statement that encountered the run-time error are not affected. The only exception is when the batch is in a transaction and the error causes the transaction to be rolled back. In this case, any uncommitted data modifications that are made before the run-time error are rolled back.
For example, assume there are 10 statements in a batch. If the fifth statement has a syntax error, no statements in the batch are executed. If the batch is compiled and the second statement then fails while running, the results of the first statement are not affected because it has already executed.
SQL Server 2005 provides statement-level recompilation. That is, if a statement triggers a recompilation, only that statement is recompiled and not the whole batch. This behavior differs from SQL Server 2000. Consider the following example that contains a CREATE TABLE statement and four INSERT statements in the same batch.
CREATE TABLE dbo.t3(a int)
INSERT INTO dbo.t3 VALUES (1)
INSERT INTO dbo.t3 VALUES (1,1)
INSERT INTO dbo.t3 VALUES (3)
GO
SELECT * FROM dbo.t3
First, the batch is compiled. The CREATE TABLE statement is compiled, but because the table dbo.t3 does not yet exist, the INSERT statements are not compiled.
Second, the batch starts to execute. The table is created. The first INSERT is compiled and then immediately executed. The table now has one row. Then, the second INSERT statement is compiled. The compilation fails, and the batch is terminated. The SELECT statement returns one row.
In SQL Server 2000, the batch starts to execute and the table is created. The three INSERT statements are compiled one by one but are not executed. Because the second INSERT causes a compilation error, the whole batch is terminated. The SELECT statement returns no rows.
Rules for Using Batches
The following rules apply to using batches:
- CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must start the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement.
- A table cannot be changed and then the new columns referenced in the same batch.
- If an EXECUTE statement is the first statement in a batch, the EXECUTE keyword is not required. The EXECUTE keyword is required if the EXECUTE statement is not the first statement in the batch.
Important
Batch files may contain credentials stored in plain text. Credentials may be echoed to the screen of the user during batch execution.
See Also
Concepts
Batch Execution Environment and MARS
Specifying Batches
Batch Examples
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
5 December 2005 |
|