What is a batch?

In Transact-SQL, a batch is a set of SQL statements submitted together and executed as a group, one after the other. Batches can be stored in command files.

Is an *.sql file containing several SQL statements considered a batch? What else do we conside开发者_高级运维r a batch?

Is an *.sql file containing several sql statements considered a batch?

Sort of. A *.sql file is often a batch, but it could also contain several batches. You delimit separate batches via a batch separator. You might need multiple batches in a file because some statements (especially certain ALTER commands) can only be executed once per batch. This can make things like performing ALTERs in a loop tricky, because certain statement (SET) will apply through the end of a batch, and because Sql Server will having implicit transaction which commit at the end of each batch.

Another trick here is how you separate individual batches in a file/document. In 99% of Sql Server tools, the batch separator is "GO". However, this is something that is configurable by the tool. It's not part of the SQL language itself, and therefore you can run into the odd person now and then who uses something else, or get confused that SQL Server complains if you send it a "GO" command directly.

Batches are not part of TSQL, they are a function of the SQL Management Studio Editor. So, for example, you cannot submit an ADO.NET command object with "GO" as part of your command text.

The batch marker - "GO" tells the query editor to submit the SQL up to that point before proceeding during execution.

It is possible to configure the query editor to use a different batch marker beside "GO", but this would just confuse the heck out of people.

from Books online

CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must start the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement.

A table cannot be changed and then the new columns referenced in the same batch.

From this you might infer that multiple batches are needed to ensure the database structure changes have taken effect before you try to use them.