:::: MENU ::::

what is T-SQL batch

dbDigger, T-SQL Interview Questions

what is T-SQL batch

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:

1. Most run-time errors stop the current statement and the statements that follow it in the batch.

2. 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.
It is important to note that CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must begin 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 altered 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.
Each batch is compiled into a single execution plan. If the batch contains multiple SQL statements, all of the optimized steps needed to perform all the statements are built into a single execution plan.

GO: Signals the end of a batch of Transact-SQL statements to the SQL Server™ utilities. GO is not a Transact-SQL statement; it is a command recognized by the osql and isql utilities and SQL Query Analyzer. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO. SQL Query Analyzer and the osql and isql command prompt utilities implement GO differently.

A T-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments. Users must follow the rules for batches. For example, any execution of a stored procedure after the first statement in a batch must include the EXECUTE keyword. The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command. This utility command that requires no permissions. It can be executed by any user.

Consult us to explore the Databases. Contact us