In SQL Server, When Should You Use Go and When Should You Use Semi-Colon ;

In SQL Server, when should you use GO and when should you use semi-colon ;?

GO only relates to SSMS - it isn't actual Transact SQL, it just tells SSMS to send the SQL statements between each GO in individual batches sequentially.

The ; is a SQL statement delimiter, but for the most part the engine can interpret where your statements are broken up.

The main exception, and place where the ; is used most often is before a Common Table Expression Statement.

When should I use semicolons in SQL Server?

From a SQLServerCentral.Com article by Ken Powers:

The Semicolon

The semicolon character is a statement terminator. It is a part of the ANSI SQL-92 standard, but was never used within Transact-SQL. Indeed, it was possible to code T-SQL for years without ever encountering a semicolon.

Usage

There are two situations in which you must use the semicolon. The first situation is where you use a Common Table Expression (CTE), and the CTE is not the first statement in the batch. The second is where you issue a Service Broker statement and the Service Broker statement is not the first statement in the batch.

What is the difference between ; and GO in T-SQL?

GO is not actually a T-SQL command. The GO command was introduced by Microsoft tools as a way to separate batch statements such as the end of a stored procedure. GO is supported by the Microsoft SQL stack tools but is not formally part of other tools.

You cannot put a GO into a string of SQL and send it as part of a ADO.NET command object as SQL itself does not understand the term. Another way to demonstrate this is with the profiler: set up some statements that use GO in Query Analyzer/Management Studio and then run the profiler when you execute. You will see they are issued as separate commands to the server.

The semi-colon is used to signify the end of a statement itself, not necessarily a whole batch.

http://msdn.microsoft.com/en-us/library/ms188037.aspx

Is the semicolon necessary in SQL?

semicolon indicates end of a statement, so if there are multiple statements then you should use semicolon else it will work fine.

I generally use semicolon as a practice, it can be useful even when you are running queries on sql client e.g. in Sql Developer using semicolon is very helpful if you have multiple statements on worksheet, as you can simply go to that particular statement and use F9 to execute that, without semicolon this is not possible.

When should I not use a semicolon?

T-SQL syntax does not require a semicolon to terminate a statement.

Actually, this is deprecated¹. I can't remember for sure, but I think you can still get away with not using them in the upcoming SQL Server 2012, but some version after that will likely require a semi-colon for every statement. Using a semi-colon is also technically required by the ansi standard. The point is that now is the time to get in the habit of using one for every statement.

As a practical matter, I don't expect them to follow through with this directly. Rather, I expect SQL Server Management Studio and other development tools to first start issuing warnings instead of errors, perhaps for several versions. This will help developers find and fix all the old non-compliant code. But that doesn't lessen the message: semi-colons are coming, and soon.

For a simple heuristic on when not to use a semi-colon, think of the code as if it were a procedural language that used curly brackets for blocks, like C/C++. Statements that would be paired with an opening (not closing) curly bracket if written in the procedure language should not get a semi-colon.

¹_{It's almost all the way at the bottom of the page}

Is the ending semicolon important

Terminating semi-colons will be required in some future version of SQL Server.

Although it's not currently required, it's not a bad habit to get into.

Should we end the statement in T-SQL with semi-colon?

It's good to get into the habit now because CTE/WITH and MERGE need it, as well as some Service broker stuff as mentioned in the other question. Of course, you could use ;WITH cTE AS ...

C# etc monkeys have been doing it for years.

It won't work with GO because it isn't a keyword. It's a directive for SSMS and other tools to break a larger script into batches.

Use Go After 'Use [Database_Name]?

There are two big differences between GO and ;:

1. GO splits the script into different batches.
2. ; is a statement terminator.

Using GO after a USE DatabaseName is necessary when the following statement requires it to be the first statement in a batch. As an example, the following code will fail:

Use SomeDatabase

Create Procedure spSomeProcedure
As Begin
    Select 1
End

Msg 111, Level 15, State 1, Procedure spSomeProcedure, Line 4

'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

But adding a GO between them will allow the statement to run:

Use SomeDatabase
Go

Create Procedure spSomeProcedure
As Begin
    Select 1
End

Command(s) completed successfully.

SQL Server - semicolon before THROW

If you alter the code so it actually throws an error.

BEGIN TRY
  -- some code
  SELECT 1/0;
END TRY
BEGIN CATCH
   -- some code
   ROLLBACK TRANSACTION
   THROW;

END CATCH

You see

Msg 3903, Level 16, State 1, Line 7 The ROLLBACK TRANSACTION request
has no corresponding BEGIN TRANSACTION.

It is trying to roll back to a save point called THROW.

This is valid syntax but fails at runtime in the above example as no transaction exists. If you are in an open transaction but have no such save point (as below)

BEGIN TRY
BEGIN TRAN
  SELECT 1/0;
END TRY
BEGIN CATCH
   ROLLBACK TRANSACTION
   THROW;
END CATCH

ROLLBACK

you see the following instead.

Cannot roll back THROW. No transaction or savepoint of that name was
found.

This kind of ambiguity is presumably why the requirement for a preceding semi colon before throw exists.

---

Related Topics