T-SQL Batch Processing

Introduction to Batch processing

A batch is a T-SQL statement that acts as a logical unit. If a statement cannot be parsed by parsing, then no statement is run. If a statement fails at run time, the statement before the statement that produced the error is already running.

To divide a script into batches, you can use the GO statement.

Features of the GO statement:

• The GO statement must be on its own line, and only the comment can be on the same line.
• It makes all statements from the beginning of the script or the most recent go statement compiled into an execution plan and sent to the server, regardless of any other batches.
• The GO statement is not a T-SQL command, but a command that is recognized by various SQL Server command utilities such as the query window in Management Studio.

　　1. Self-made line

The go command should be on its own line. Technically, you can start a new batch on the same line after the Go command, but this will severely affect readability. T-SQL statements cannot be placed before the GO statement, otherwise the GO statement is often incorrectly understood, resulting in grammatical parsing errors or unintended consequences. For example, a go statement is used after the WHERE clause.

GO

The parser doesn't know what to do with it.

There is a syntax error near the 1 line ' GO '.

　　2. Each batch is sent to the server separately

Because each batch is processed separately, an error in one batch does not prevent another batch from running. To illustrate this, take a look at the following code.

1/0 GO 0/1     

If there are no dependencies between these batches, each batch is fully autonomous at run time.

1  rows encountered a divide-by-zero error. (1 rows affected) 

If there is a dependency between these batches, then each batch will fail after the error occurs. Dependency refers to the following statement, depending on the result or variable that was executed earlier, and so on.

　　3. Go is not a T-SQL command

　　A common mistake is to think of go as a T-SQL command, in fact go is a command that can only be recognized by the editing tool (Management Studio).

When the edit tool encounters a go statement, it sees the GO statement as a token that terminates the batch, packages it, and sends it as a standalone unit to the server, excluding go. Because the server itself doesn't know what go means.

errors in batch processing

Errors in batches fall into the following two categories:

• Syntax error
• Run-time error

If the Query Analyzer finds a syntax error, the batch processing is immediately canceled. Because a grammar check occurs before a batch is compiled or executed, a failure during a grammar check means that no batches have been executed-regardless of where the syntax error occurred in the batch.

Run-time errors work in a very different way, because any statements that are executed before a run-time error are completed, so that anything they do is realistic unless they are part of the uncommitted transaction.

In general, run-time errors will terminate the execution of batches from where the error occurred to the end of this batch process. The next batch does not affect.

When to use batch processing

There are several reasons to use batching, but all batches have one thing in common--when something in the script has to happen before or after something else, you need to use batch processing.

　　1. Require a statement of their own batch processing

There are some commands that must have their own batch processing.

• CREATE DEFAULT
• CREATE PROCEDURE
• CREATE RULE
• CREATE TRIGGER
• CREATE VIEW

If you want to combine any of these statements with other statements in a single script, you need to scatter them into their batches by using the GO statement.

　　2. Use batch process to establish priority

Batch processing may be used when priority is required. In other words, you need to complete the previous task before the next task starts. Most of the time, SQL Server can handle this very well-the first statement in the script is executed first, and the second statement in the script can depend on the appropriate state of the server at the time the second statement runs.

Let's look at an example:

Use master DATABASE Test TABLEintint )      

When you execute the above script, the prompt command is completed successfully. But is it really okay?

When looking at the test database, I found that the table testtable does not exist, but the master database has more than one testtable table.

Why is the table created in the master database, the answer depends on what the current database is when you run the CREATE TABLE statement. In this case, it happens to be the master database, so the table is created in that database.

You might think that changing the code above might solve the problem:

DATABASE test use test TABLEintint )      

Unfortunately, no, the error message is as follows:

3 Rows of database ' Test ' does not exist. Be sure to enter the name correctly.

When the parser tries to validate the code, it discovers that the use refers to a nonexistent database, which is an integral part of the batch statement, and the correct code is as follows:

DATABASE Test Go--This GO is the two protagonist use  Test TABLEintint )
       

After adding a go, the problem is solved successfully.

Let's look at an example below:

Use Test TABLE testtable    int  to TestTable (col1,col2,col3) VALUES (1, 1,1)        　　  

The above code hints in Query Analyzer that the Col3 column does not exist. In fact, the above code can also be solved by a go.

Use Test TABLE testtable    int GO --Change the database first, then send the INSERT, this is the separate syntax verification into  TestTable ( COL1,COL2,COL3) VALUES (1,1,1)          　　

T-SQL Batch Processing