LearnNowOnline

Blog

Transact-SQL Programming: Batches and Scripts

Excerpt by Don Kiely | March 25, 2013

A batch is a collection of SQL statements that SQL Server processes as a single unit. SQL Server compiles a single execution plan for each batch; all the optimized steps necessary to perform the statements are built into the plan. If one of the statements contains a compile error, none of the statements in the batch will execute. You can execute batches directly in Management Studio, but they can also reside in external files, which you can execute by using the sqlcmd utility from the command line.

The following example shows two data definition language (DDL) statements that create a table and a view. You must process these statements as separate batches. If you attempt to process them as a single unit, you will receive an error message that 'CREATE VIEW' must be the first statement in a query batch, as you can see in the Figure below.

TIP: Recall that you can select a set of statements in the query editor in Management Studio and click the Execute button, and SQL Server will execute only those statements as a batch, even if there are other statements in the code file. This is an incredibly handy feature, one that we'll use repeatedly throughout this course.

USE tempdb;

CREATE TABLE dbo.Test

(

ID int NOT NULL,

TestName varchar(50) NOT NULL

);

CREATE VIEW dbo.vwTest

AS

SELECT TestName FROM dbo.Test;

If you look at the TSQL.sql file in Management Studio, you'll see that there is a red squiggly line under the CREATE VIEW statement. As you can see in the Figure below, if you hover your mouse over the statement, you'll see what the problem is.

NOTE: It's interesting that the error message you get when you attempt to run the previous T-SQL batch gives the error message "'CREATE VIEW' must be the first statement in a query batch," and the IntelliSense error is "Incorrect syntax: 'CREATE VIEW' must be the only statement in the batch." According to the Books Online entry for the CREATE VIEW statement, it must be the first statement in the batch, so it appears that is the correct error and the "only statement" error message is incorrect.

The following code succeeds because it contains a GO statement before it creates the view.

USE tempdb;

CREATE TABLE dbo.Test

(

ID int NOT NULL,

TestName varchar(50) NOT NULL

);

GO

CREATE VIEW dbo.vwTest

AS

SELECT TestName FROM dbo.Test;

GO

The GO keyword signals the end of a T-SQL batch to the various tools that execute T-SQL code, including Management Studio. GO is not a T-SQL statement as such. Instead, it is a command recognized by code execution tools. GO indicates that the tool should send the batch to the instance of SQL Server it is connected to and execute the statements. A little used feature of the GO command is that you can optionally pass it an integer argument, and SQL Server will execute the statements in the batch the specified number of times.

This post is an excerpt from the online courseware for our SQL Server 2012: Introduction to T-SQL course written by expert Don Kiely.