Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge about "SQL Server how to use T-SQL statement batch processing". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to handle these situations! I hope you can read carefully and learn something!

Introduction to Batch Processing

Batch processing is a T-SQL statement as a logical unit. If a statement fails parsing, no statement is run. If a statement fails at runtime, all statements preceding the statement that produced the error have already been run.

To split a script into batches, use the GO statement.

Features of GO statement:

GO statements must be on their own line, only comments can be on the same line.

It causes all statements since the beginning of the script or the last GO statement to be compiled into an execution plan and sent to the server, independent of any other batch processing.

GO statements are not T-SQL commands, but commands recognized by various SQL Server command utilities, such as the Query window in Management Studio.

1. A line of its own

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 seriously affects readability. T-SQL statements cannot precede GO statements, otherwise GO statements are often misunderstood, resulting in parsing errors or unpredictable consequences. For example, use a GO statement after the WHERE clause.

SELECT * FROM Person WHERE Id = 100 GO

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

Message 102, level 15, status 1, syntax error near 'GO' on line 1. 2. Each batch is sent to the server separately

Because each batch is processed separately, errors in one batch do not prevent another batch from running. To illustrate this, take a look at the code below.

SELECT 1/0 GO SELECT 0/1

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

Message 8134, level 16, status 1, divide-by-zero error encountered on line 1. (1 line affected)

If there are dependencies between these batches, every batch after the error fails. Dependency refers to subsequent statements, dependencies on results or variables executed previously, and so on.

GO is not a T-SQL command.

A common mistake is to think GO is a T-SQL command, when GO is actually a command recognized only by Management Studio.

When the editing tool encounters a GO statement, it treats the GO statement as a tag to terminate the batch, packages it, and sends it to the server as a separate unit, excluding the GO. Because the server itself has no idea what GO means.

Errors in batch processing

Errors in batch processing fall into two categories:

syntax error

run-time error

If the query parser finds a syntax error, the batch processing is immediately canceled. Because syntax checking occurs before the batch is compiled or executed, a failure during syntax checking means that no batch has been executed yet--regardless of where the syntax error occurred in the batch.

Runtime errors work very differently, because any statements executed before encountering runtime errors are already completed, so unless they are part of an uncommitted transaction, anything those statements do is already real.

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

When to use batch processing

Batch processing is used for several reasons, but all batch processing has one thing in common-it's used when something in a script has to happen before something else or separately.

1. Statements requiring their own batch processing

There are some orders that must have their own batch.

CREATE DEFAULT

CREATE PROCEDURE

CREATE RULE

CREATE TRIGGER

CREATE VIEW

If you want to combine any of these statements with others in a single script, you need to disperse them into separate batches by using GO statements.

2. Use batch processing to establish priorities

Batch processing may be used when priorities need to be established. In other words, before the next task starts, you need to complete the previous task. Most of the time, SQL Server handles this situation well-the first statement in the script is executed first, and the second statement in the script can depend on the server being in the proper state when the second statement runs.

Here's an example:

USE master CREATE DATABASE Test CREATE TABLE TestTable ( col1 int, col2 int )

When executing the script above, prompt that the command has completed successfully. But is it really okay?

When you look at the Test database, you find that the TestTable table does not exist, but there is an additional TestTable table in the master database.

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

You might think that changing the above code to something like this would solve:

CREATE DATABASE Test USE Test CREATE TABLE TestTable ( col1 int, col2 int )

Unfortunately, this is not the case. The error message is as follows:

Message 911, Level 16, Status 1, Line 3 Database 'Test' does not exist. Make sure you entered the name correctly.

When the parser tries to validate the code, it finds that USE refers to a non-existent database, which is essential for batch statements. The correct code is as follows:

CREATE DATABASE Test GO --This GO is a two-character USE Test CREATE TABLE TestTable ( col1 int, col2 int )

After adding a GO in this way, the problem was successfully solved.

Here's another example:

USE Test ALTER TABLE TestTable ADD col3 int INSERT INTO TestTable (col1,col2,col3) VALUES (1,1,1)

The above code indicates in the query analyzer that the col3 column does not exist. In fact, the above code can also be solved by a GO.

USE Test ALTER TABLE TestTable ADD col3 int GO --first change the database, then send the insert, at this time is separate syntax validation INSERT INTO TestTable (col1,col2,col3) VALUES (1,1,1)"SQL Server how to use T-SQL statement batch processing" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.