In Sql server, when should you use GO and when should you use semi-colon ; ?

Question

I've always been confused with when I should use the GO keyword after commands and whether a semi-colon is required at the end of commands.

When I run the Generate-script in sql server management studio, it seems to use GO all over the place, but not the semi-colon.

Please can someone explain to me the differences and why/when i should use them.

thanks

Answer 1

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.

Answer 2

GO is a batch terminator, a semi-colon is a statement terminator.

you will use GO when you want to have multiple create proc statements in 1 script because create proc has to be the first statement in a batch. If you use common table expressions then the statement before it needs to be terminated with a semi-colon

Answer 3

You should use a semi-colon to terminate every SQL statement. This is defined in the SQL Standards,

Sure, more often than not SQL Server allows you to be sloppy and omit it but why get into bad habits? ;)

As others have pointed out, the statement preceding a common table expression (CTE) must be terminated with a semi-colon. As a consequence, from folk who have not fully embraced the semi-colon terminator, we see this:

;WITH ...

which I think looks really odd.

Additionally, a MERGE statement must be terminated by a semi-colon. Do you see a pattern here? These are a couple of the newer additions to TSQL which closely follow SQL Standards. Looks like the SQL Server team are going down the road of mandating the use of the semi-colon terminator.

Answer 4

GO

Go is a batch separator. This means that everything in that batch is local to that particular batch.

Any declarations of Variables, Table Variables, etc do not go across GO statements.

#Temp tables are local to a connection, so they span across GO statements.

Semicolon

A Semicolon is a statement terminator. This is purely used to identify that a particular statement has ended.

In most cases, the statement syntax itself is enough to determine the end of a statement.

CTE's however, demand that the WITH is the first statement so you need a semicolon before the WITH.

Answer 5

The reason why you see so many GO's in Generated DDL scripts is because of the following rule about batches.

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

One of the use cases for Generated DDL is to generate multiple objects in a single file. Because of this a DDL generator must be able to generate batches. As others have said the GO statement ends the batch.