Knowledge points for batch processing in SQL Server

A batch is a set of T-SQL statements for a logical unit.

In order to divide a script into batches, you can use the GO statement.

1. The go statement must be in one line.

2. The GO statement allows each batch to be sent individually to the server, regardless of other batch processors. (This is likely to be a concurrent implementation, so in many cases, you may need to consider the order issue)

3. The GO statement is not a T-SQL command, but rather a molded identification command that is applied by various SQL Server commands.

Errors in batch processing:

1. Grammatical errors are detected before they are executed.

2. Run-time errors, which can only be detected after running (or in), are generally violations of integrity and other errors.

A statement that requires a batch of its own:

1. Create Default

2. Create Procedure

3. Create Rule

4. Create Trigger

5. Create View

Note: If you want to combine these statements in a separate script, you need to distribute them in their batch by using the GO statement.

EXEC's trap:

1. Exec and the code that invokes it all run under a separate scope-that is, the calling code cannot reference a variable in the EXEC statement, and exec cannot reference the variables in the calling code after they are resolved to the string used for the EXEC statement. Consider using sql-executesql if you need to pass values between dynamic SQL and the routines that call it.

2. By default, exec runs under the security context of the current user-not the security contexts of the calling object. (objects are often run in the context of the object owner, not the current user)

3. exec runs in the same connection and transaction environment as the calling object.

4. Execution of the exec string requires that the concatenation of the function calls be preceded by the actual call to exec--the function concatenation cannot be performed in the same statement that executes the EXEC call.

5. Exec cannot be used within user-defined functions.