SQL:Batches and Transactions

A batch is a group of one or more Transact-SQL statements sent at one time from an application to Microsoft® SQL Server™ 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, so none of the statements in the batch are executed.

A run-time error, such as an arithmetic overflow or a constraint violation, has one of two effects:

• Most run-time errors stop the current statement and the statements that follow it in the batch.
• A few run-time errors, such as constraint violations, stop only the current statement. All the remaining statements in the batch are executed.

The statements executed before the one that encountered the run-time error are not affected. The only exception is if the batch is in a transaction and the error causes the transaction to be rolled back. In this case, any uncommitted data modifications made before the run-time error are rolled back.

USE AdventureWorks
GO /* Signals the end of the batch */
CREATE VIEW auth_titles
AS
SELECT *FROM authors
GO /* Signals the end of the batch */
SELECT * FROM auth_titles
GO /* Signals the end of the batch */

Several batches combined into one transaction:

BEGIN TRANSACTION
GO
USE AdventureWorks
GO
CREATE TABLE mycompanies
(
id_num int IDENTITY(100, 5),
company_name nvarchar(100)
)
GO
INSERT mycompanies (company_name)
VALUES ('New Moon Books')
INSERT mycompanies (company_name)
VALUES ('Binnet & Hardley')
INSERT mycompanies (company_name)
VALUES ('Algodata Infosystems')
GO
SELECT *
FROM mycompanies
ORDER BY company_name ASC
GO
COMMIT
GO