29

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?

46

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

  • 27
    @DaniloMatrangoloMarano It's also worth noting the fun fact that you can use GO and a number to indicate how many times you want the previous T-SQL query batch to execute. For example SELECT 'WOOHOO' AS WOOHOO; GO 10 results in the previous SELECT statement running 10 times. – J.D. Jan 11 at 23:00
  • 10
    the wonders of this website, even what is seems to be a simple question , someone will make an over and beyond brilliant explanation, and even the comment gave me something new to know. – encryptoferia Jan 13 at 1:00
  • I like to reconfigure SSMS to use PARTY as the batch separator. Adding a bunch of rows to a table then becomes PARTY 1999 😎 . Another fun separator is 👍 – Max Vernon Jan 15 at 21:36
  • Just don't set it to ROLLBACK :) – David Browne - Microsoft Jan 15 at 21:58
15

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
  • But what is the difference between GO and ;? I will edit my question. – Danilo Matrangolo Marano Jan 11 at 22:46
  • 3
    @DaniloMatrangoloMarano as shown in this example where it has "forgotten about" the variable a, the batch separator seems to reset the context. – RonJohn Jan 12 at 8:22
7

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!).

  • 4
    The doc still states "it will be required in a future version", albeit that future version will likely be beyond our lifetime. Deprecated or not, I still recommend semi-colon statement terminators to avoid surprises and comply with the ANSI standard. – Dan Guzman Jan 12 at 11:10
  • 1
    I don't disagree with it being a good practice to have them there, Dan. As for deprecation, I was going by the rather short list of deprecated features for SQL Server 2019. I guess that BOL isn't totally in sync regarding this. docs.microsoft.com/en-us/sql/database-engine/… – Tibor Karaszi Jan 12 at 12:29
  • 1
    The SQL 2017 deprecation page lists semicolons under the section "deprecated in a future version" so I guess it's not deprecated now but that not using them will be deprecated per the text "The following SQL Server Database Engine features are supported in the next version of SQL Server. The specific version of SQL Server has not been determined." Lots of wiggle room, especially considering "We strive not to remove a deprecated feature from future releases to make upgrades easier." – Dan Guzman Jan 12 at 12:49
  • 1
    Yes, but the SQL 2019 deprecation list is empty. I.e., not using semicolon is not deprecated. And since not using semi-colon is available in 2019, there's no reason to go back to what MS thought they could deprecate a couple of years ago. That is irrelevant now, since we have the new list where this isn't deprecated anymore. I guess we're both being pedantic here, dealing with nuances without any real-world significance. It is a good idea to have them there, I agree. But realistically, MS won't remove them for along long time. (And we argue on how MS states this in the doc...). – Tibor Karaszi Jan 12 at 13:03
  • 3
    Semicolons are required in Oracle. Semicolons are required in C and many other programming languages. Do you want your fingers to learn the semicolon habit or not? After a year of flipping between SQL Server and Oracle on almost an hourly basis, I decided to go for 100% semicolons in SQL Server (except the odd one that I forget!). – grahamj42 Jan 12 at 18:47
7

These the explainations:

  • ; = 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
  • 3
    At is worth pointing out that 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'. – David Spillett Jan 13 at 10:32

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.