Batches

A batch is a set of SQL statements submitted together and executed as a group. A script is often a series of batches submitted one after the other. A batch is compiled only once as a whole and is terminated by an end-of-batch signal. Because of this, certain restrictions on batch size exist.

These rules apply to batches:

• CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. They must be submitted one at a time.
• Rules and defaults cannot be bound to columns and used within the same batch. That is, the sp_bindrule and sp_bindefault system stored procedures cannot be used in the same batch as INSERT statements that invoke the rule or default.
• CHECK constraints follow the same rule as rules and defaults. A table created with CHECK constraints cannot enforce those constraints in the same batch as the definition.
• You cannot drop an object and then reference or re-create it in the same batch.
• You cannot alter a table and then reference the new columns in the same batch.
• Options changed with a SET statement take effect only at the end of the batch. For details, see the SET statement.
• You can submit batches through a graphical tool such as the ISQL/w utility, through a command-line utility such as isql, or through files (scripts) passed to isql. A file submitted to isql can include more than one batch of SQL statements if each batch is terminated by the GO command batch separator.
• The SQL Server batch size of 128K limits the maximum amount of explicit data values that can be inserted or updated in a single batch. Since some memory is required for a query's execution plan, the actual size of the data you can insert or update is somewhat less than 128K. For example, you might be able to insert or update one column of 125K or two columns of 60K each. This limit does not apply to text or image data inserted or updated with WRITETEXT or UPDATETEXT, data inserted with the bcp command-line utility, data inserted from another table, or data passed by remote procedure calls.

Examples

A.    Multiple SELECTs

This example shows multiple selects in one batch. Two results sets will be returned.

SELECT COUNT(*) FROM titles
SELECT COUNT(*) FROM authors

B.    Create and Use an Object

This example shows a table creation, an insert, and then a select.

CREATE TABLE test
(
    column1         char(10)            NOT NULL, 
    column2         int                NULL
)
INSERT test
    VALUES ('hello', 598)
SELECT * FROM test

C.    INSERT with Constraints in Separate Batches

This example shows how batches that include table definition and inserts into the table should be written. First, create the table in a separate batch from the INSERT statements(s), and then place the INSERT(s) in a separate batch.

This INSERT fails because 1234 is not a valid publisher ID.

CREATE TABLE publishers
(
pub_id        char(4)            NOT NULL 
    CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED
    CHECK (pub_id in ('1389', '0736', '0877', '1622', '1756')
        OR pub_id like '99[0-9][0-9]'),
pub_name        varchar(40)        NULL,
city            varchar(20)        NULL,
state        char(2)            NULL,
country        varchar(30)        NULL
    DEFAULT    ('USA')
)
go

INSERT publishers (pub_id, pub_name) VALUES('1234', 'Publishers test')

See Also