48

The GO statement from SQL Server caused me great curiosity and I don't really know how to use it properly.

I noticed that queries with or without GO don't return errors and seem to work the same, so what is the purpose of it and why should I use it?

And what is the difference between semicolons ; and GO at the end of a query?

1
77

GO is not a part of the TSQL language. It's a batch separator used by SQLCMD and SSMS.

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.

SQL Server Utilities Statements - GO

It originated with the command line interfaces, and persists in sqlcmd to this day. You can write a batch over multiple lines in the command line interface, and the batch isn't sent to the server until you type go.

1> select name, create_date
2> from sys.objects
3> where name = 'foo'
4> go
name                                                                                                                             create_date
-------------------------------------------------------------------------------------------------------------------------------- -----------------------
foo                                                                                                                              2020-07-20 09:56:42.393

(1 rows affected)
1>

You can configure what to use for a batch separator in SSMS. GO is the default, but it can be just about anything else.

NUTS

0
23

It splits the command into individual batches.

For instance, variables' scoped ends with GO.

; separates commands but doesn't end scope.
GO separates batches and ends scope.

You may think of it as if the whole command got split by GO and each part got executed per se.

ie.

This works:

declare @a int;
set @a = 1;
select @a

But this doesn't work:

declare @a int
set @a = 1
GO
select @a -- will throw error as variable is not declared
0
12

These are the explanations:

  • ; = Is a statement terminator. Is not mandatory but it might be one day. for now you MUST use it in two situations:
  1. In a Common Table Expression (CTE), where the CTE is not the first statement in the batch.
  2. When you issue a Service Broker statement and the Service Broker statement is not the first statement in the batch.
  • GO = Is a batch separator. It tells to SQL Server "stop here and execute all the previous code before moving on". Is not mandatory but there is a feature for you: if you write GO 10, GO 100, GO 1000 it will execute the same batch of code 10, 100, 1000 times

    GO means nothing to SQL Server itself, but is interpreted by the tools (such as SSMS or SQLCMD) that send your scripts to SQL Server. Some other tools may not understand 'GO'. Some tools interpret GO slightly differently. In SSMS each batch is performed in the same session so transactions can span batches, at least some of the command line tools included with SQL Server do not maintain the same session for each batch in a file, so an open transaction will be rolled back when GO is encountered.

0
9

GO has been well addressed in other answers. Let me add some about semi-colon:

A long long time ago, this wasn't something we used. But the SQL standard (ANSI/ISO SQL) specified that a SQL statement should end with semi-colon. So at a certain version, MS allowed it to comply with ANSI SQL. But it was, and mostly still is, a no-op. I.e., it makes no difference if you have it or not

I wasn't involved when the SQL language was invented (I was a kid), but I can imagine that semi-colon was specified as a statement terminator to simplify parsing of SQL. I.e., when you submit SQL to the db engine, it first has to parse (understand what you said, basically) the statement(s). You can probably imagine that is might be easier for the parsing code in the engine if it could look for some certain character that means "end-of-statement". Hence semi-colon. But as I noted, SQL Server didn't use semi-colon for parsing.

Now, with time, some new language elements that was introduced required that the preceding statement was terminated with semi-colon (WITH as in CTE and THROW being two examples). Without it, SQL Server couldn't parse (understand what you said) and you got a error generated from the parsing phase.

At one time MS said that not ending a statement with semi-colon was deprecated. I.e., they tried to make us believe that at some point in time (some future release), we have to end all our statements with semi-colon. I was probably not the only one who found that a bit amusing - imagine the backwards compatibility aspects! Jumping to present time, if you read the documentation, you find that this is no longer the case (in fact, there are no deprecated featured today - none!).

1
1

I would like to add here that, if you add Go statement between different statement, it will continue executing your queries irrespective of any error thrown. If you skip Go statement, then your execution will stop after any error and the next statements won't be executed. The best use case e.g., if you have multiple insert statements, and you want to continue your execution if there any any error in your insert statement(you want only the logs of execution maybe) then please write this batch separator. If you want to stop execution at the statement where there is an error, want to make some changes and then continue execution later on, do not write GO separator.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.