It seems that batch SQL statements and batch processing in a DBMS are not exactly the same concepts. No specific information on this has been found.

The batch

In standard SQL, there is no concept of a batch. The description of Batches of SQL Statements is fairly clear almost exclusively in SQL Server.

A batch of SQL statements is a single SQL statement in which two or more SQL statements form a group or have the same effect as a group of two or more SQL statements.

Simply put, it is a group of SQL statements that are submitted to the RDBMS as a whole for execution.

The most common type of Stored Procedure, which is supported by almost all database management systems, is also known as a batch of SQL statements if it contains multiple SQL statements.

Execution of multiple SQL statements together is also known as batch processing. You can say this is an explicit batch SQL statement.

Similarly, multiple SQL statements placed in an SQL file can also be considered batch statements when the SQL script file is loaded directly and run. Even SQL files contain multiple batches.

Insert multiline Insert statement, also a batch processing, although only one sentence, but inserts multiple rows.

INSERT INTO <table> (<col1>.<col2>...).VALUES(? ,? ...). . (? ,? ...). . ;Copy the code

Similarly, batch SQL statements such as update and delete are also batch SQL statements.

The SSMS tool command GO

In SQL Server Management Studio(SSMS), GO statements are supported to signal the end of a batch of T-SQL statements. This is why many SQl Server examples include GO statements.

GO split SQL batch statements are relatively independent of each other. The success or failure of execution in one batch does not affect the execution of statements in the other batch.

GO is not a Transact-SQL statement, it is only recognized by the SQLCMD, OSQL tool, or SSMS editor as a tool statement or symbol that sends the current batch of SQL statements to the RDBMS for execution as a batch marker, or it is recognized as a batch command.

So GO is sometimes referred to as a batch separator rather than a statement. But GO can be followed by the number of times it is executed, so it can be called a utility command.

GO itself is never sent to the SQL Server Server (execution).

Other symbols can be changed in SSMS (Tools -> Options -> Query Execution).

GO must appear as a single line.

GO [count] specifies the number of times a batch is executed before GO.

A batch is compiled into a single execution plan, and therefore, one batch statement at a time.

Error handling in batch statements

Errors in batch statements are handled according to different error types.

Grammar mistakes

Batch SQL statements are processed as a single compilation unit, so a syntax error in a batch will prevent any statement in the batch from executing.

Syntax error. None of the statements in the batch will be executed.

SELECT 'Batch - Start';
SELEC 'Batch - Middle';
SELECT 'Batch - Start';
Copy the code

SQL Server error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'SELEC'.
Copy the code

Error in PostgreSQL:

ERROR: Syntax ERROR in"SELEC"Or nearby LINE 2: SELEC'Middle';
Copy the code

A runtime error in DDL terminates batch processing

If a runtime error occurs in a DDL statement when executing a batch, the remaining statements in the batch are not executed:

INSERT INTO Productcopy VALUES ('0011'.'T shirt' ,'clothes'.1000.500.'2009-09-20');
-- Productcopy database exists and therefore runs an error. Subsequent statements are not executed
CREATE TABLE Productcopy
(product_id      CHAR(4)      NOT NULL,
 product_name    VARCHAR(100) NOT NULL,
 product_type    VARCHAR(32)  NOT NULL,
 sale_price      INTEGER ,
 purchase_price  INTEGER ,
 regist_date     DATE ,
 PRIMARY KEY (product_id)
);
INSERT INTO Productcopy VALUES ('0012'.'T shirt' ,'clothes'.1000.500.'2009-09-20');
Copy the code

SQL Server error, and the following statement will not perform insert:

Msg 2714, Level 16, State 6, Line 2
There is already an object named 'Productcopy' in the database.
Copy the code

Execute the above statements in PostgreSQL’s pgAdmin4. An error is reported that “productCopy” already exists, and all statements are reverted to their original state. This is because in pgAdmin4, multiple SQL is processed as a single transaction.

On the PSQL command line, it is split into separate statements, each of which automatically commits transactions. Error “productCopy” exists, but both statements perform inserts.

A runtime error that violates a constraint terminates only the current statement

An error that violates a constraint only stops the execution of the current statement, and the remaining statements in the batch are still executed.

The current statement violates the primary key constraint, but only stops the execution of the current statement
INSERT INTO Productcopy VALUES ('0011'.'T shirt' ,'clothes'.1000.500.'2009-09-20');
INSERT INTO Productcopy VALUES ('0012'.'T shirt' ,'clothes'.1000.500.'2009-09-20');
Copy the code

SQL Server error: current statement terminated, subsequent statement still running:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__Productc__47027DF52594C47B'. Cannot insert duplicate key in object 'dbo.Productcopy'. The duplicate key value is (0011).
Copy the code

On the pgAdmin4 and PSQL command lines of PostgreSQL, the same is true in the previous section.

Restrictions on batch commands in SQL Server

  1. There can be multiple CREATE TABLE and CREATE INDEX statements in a batch.

  2. However, the CREATE VIEW, CREATE FUNCTION, CREATE PROCEDURE, and CREATE TRIGGER statements must be unique statements in the batch and cannot exist together in a batch.

As follows, there are two statements in a batch to create a view:

 -- Only one CREATE VIEW can be in a batch
CREATE VIEW Product_view1 AS SELECT * FROM Productcopy;
CREATE VIEW Product_view2 AS SELECT * FROM Product;
Copy the code

Error:

Msg 156, Level 15, State 1, Procedure Product_view1, Line 2 [Batch Start Line 0]
Incorrect syntax near the keyword 'CREATE'.
Copy the code

  1. Local variables in one batch are not available in another batch.

  2. To EXECUTE stored procedures in a batch, you must use the EXECUTE(or EXEC) keyword unless it is the first statement in the batch.

Batch, statement, and RPC

An SQL batch is a collection of one or more statements as a group, with the GO statement used to split the batch.

A single executable SQL command (one line or semicolon; Split), for an SQL statement.

RPC calls are calls from the client application to the database. For Windows services, Web applications, or other applications, any application that connects to a database is actually making an RPC call.

In the Profiler (SSMS), you can see everything that arrives at the database server. Batch processing from Management Studio, RPC calls from external applications (either batch or stored procedure calls), and Procedure execution from Management Studio.

They are all made up of T-SQL statements.

This part is from Difference between SQL Batch, Statement and RPC?

reference

  • SQL Batch and GO – SQL Server for Oracle DBAs and Developers
  • SQL Server utility statement -go