GOs and Semicolons

In every programming language there are phrases or elements that appear frequently and yet seem innocuous. Two such elements in T-SQL are the GO command and the semicolon.

The GO command tells the SQL server to execute/commit any preceding T-SQL statements. When there is only one statement in a batch, the GO command has no distinct effect. But, in multi-statement batches, the placement of GO could not only be important, it could, in fact, be required. Let’s take a look by walking through an example.

The following three statements, when executed as a batch (all at once), will fail with error message 111, stating that “CREATE VIEW” must be the first statement in a batch. In our example, it is not; it is the second statement, right after we specify the database context.

/* This fails because CREATE VIEW must be the first statement in a batch transaction */
USE [master];
CREATE VIEW vwTest AS SELECT 'Col1' = 1;
DROP VIEW vwTest;

To address this error, let’s put a GO after we specify the database context. While that resolves error message 111, running the statements again as a batch will result in error message 156, stating incorrect syntax near the keyword “DROP”.

/* This fails because CREATE VIEW must be committed before continuing in a batch transaction */
USE [master];
GO
CREATE VIEW vwTest AS SELECT 'Col1' = 1;
DROP VIEW vwTest;

And so, to resolve the second error, let’s put another GO after the CREATE VIEW statement to ensure it is committed before continuing on to the DROP statement. The following statements executed as a batch will succeed.

/* This completes successfully */
USE [master];
GO
CREATE VIEW vwTest AS SELECT 'Col1' = 1;
GO
DROP VIEW vwTest;

As the preceding examples demonstrate, the GO command is useful in batch operations to specify points in the batch where prior statements should be completed before continuing to later statements. There are other use-cases, mostly related to ensuring that newly-created objects are in place before subsequently referencing them the batch.

Let’s now shift to a more subtle issue: that of the semicolon. In T-SQL, a semicolon is designated as the statement terminator (similar to many other programming languages). In all SQL Server versions through 2012 (Denali), terminating a statement with a semicolon is optional in almost every instance. However, be aware that it will not be optional in a future release (or, at least so-says Microsoft at this point). Take a look at this TechNet article and see for yourself. On the other hand, the lack of semicolons in the wild is so pervasive that it will be difficult to enforce their usage anytime soon.

Aside from the potential that terminating T-SQL statements with a semicolon might be required in the nearish future, when else might it be needed? There are a few, specific instances, such as in the definition of a common table expression (CTE). A CTE is a T-SQL statement that resolves to a record set which, once defined, can be referenced multiple times, but only within the same scope. Additional information on CTEs is available here, and perhaps will be the subject of a future post within this forum.

Regardless, CTEs require that preceding T-SQL statements are terminated with a semicolon. Let’s take a look.

The following batch will fail with error message 319, stating incorrect syntax near the keyword “with”. The full message is actually uncharacteristically helpful and suggests that it might be caused by a missing semicolon.

/* This fails because common table expression definitions require prior statements to be semicolon terminated */
PRINT 'Start'
WITH cte AS (SELECT 'Col1' = 1)
SELECT * FROM cte
;

Now that we’ve seen the error message and confirmed the batch does not execute, let’s add the semicolon and see what happens.

/* This completes sucessfully */
PRINT 'Start'; -- add a semicolon here
WITH cte AS (SELECT 'Col1' = 1)
SELECT * FROM cte
;

As you will see, the previous batch executes successfully. While it’s not a good example of using a CTE, it does demonstrate that the semicolon was necessary for the CTE construct. At this point, let’s shift perspective and look at the use of semicolons a little bit differently: what if we provide too many semicolons? Let’s add one to the batch after defining the CTE and see what happens.

/* This fails because common table expressions are only valid within a statement (and a semicolon terminates a statement) */
PRINT 'Start';
WITH cte AS (SELECT 'Col1' = 1); -- add a semicolon here
SELECT * FROM cte
;

In this case, the batch fails with error message 1o2, incorrect syntax near “;”. That error is due to SQL not being able to resolve the “FROM cte” part of the statement. As mentioned previously, a CTE is only valid within the T-SQL statement in which it was defined. Since the semicolon ended the CTE definition statement, the following FROM reference to the CTE is invalid.

So, bottom line regarding semicolons: 1) get in the habit of using them, because they are eventually going to be required, and 2) by using them frequently you’ll develop a natural sense of where statements begin and end, and thus be better able to plan and manage transactions, batches, and, in general, good coding practice.

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: