141

I have read and read over MSDN, etc. Ok, so it signals the end of a batch.

What defines a batch? I don't see why I need go when I'm pasting in a bunch of scripts to be run all at the same time.

I've never understood GO. Can anyone explain this better and when I need to use it (after how many or what type of transactions)?

For example why would I need GO after each update here:

 UPDATE [Country]
   SET [CountryCode] = 'IL'
 WHERE code = 'IL'

 GO

 UPDATE [Country]
   SET [CountryCode] = 'PT'
 WHERE code = 'PT'
user2864740
  • 60,010
  • 15
  • 145
  • 220
PositiveGuy
  • 46,620
  • 110
  • 305
  • 471
  • 1
    possible duplicate of [What is the use of GO in SQL Server Management Studio?](http://stackoverflow.com/questions/2299249/what-is-the-use-of-go-in-sql-server-management-studio) – tvanfosson Oct 02 '11 at 13:05
  • FWIW, it seems as if a `go` also resets/clears `declare @foo` variable declarations - I was getting *you need to declare @foo* errors, until I commented out the `go`. – JL Peyret Apr 09 '18 at 22:52

6 Answers6

111

GO is not properly a TSQL command.

Instead it's a command to the specific client program which connects to an SQL server (Sybase or Microsoft's - not sure about what Oracle does), signalling to the client program that the set of commands that were input into it up till the "go" need to be sent to the server to be executed.

Why/when do you need it?

  • GO in MS SQL server has a "count" parameter - so you can use it as a "repeat N times" shortcut.

  • Extremely large updates might fill up the SQL server's log. To avoid that, they might need to be separated into smaller batches via go.

    In your example, if updating for a set of country codes has such a volume that it will run out of log space, the solution is to separate each country code into a separate transaction - which can be done by separating them on the client with go.

  • Some SQL statements MUST be separated by GO from the following ones in order to work.

    For example, you can't drop a table and re-create the same-named table in a single transaction, at least in Sybase (ditto for creating procedures/triggers):

> drop table tempdb.guest.x1          
> create table tempdb.guest.x1 (a int)
> go
  Msg 2714, Level 16, State 1
  Server 'SYBDEV', Line 2
  There is already an object named 'x1' in the database.   
  
> drop table tempdb.guest.x1          
> go
> create table tempdb.guest.x1 (a int)
> go
>
Community
  • 1
  • 1
DVK
  • 126,886
  • 32
  • 213
  • 327
  • CREATE TABLE does not need to be separate though... how would one create a temp table in a stored proc for example? – gbn Apr 19 '10 at 15:28
  • @gbn - you're right... i'll try tor recall which operations needed to be separated into their own transaction and update the Q when I do. – DVK Apr 19 '10 at 15:47
  • 4
    GO statement does not create transaction. If You include multiple GO statements in one BEGIN TRANSACTION statement and in the end You will do a ROLLBACK, it will rollback all GO's. And if in one GO in the middle You will get some error, and in the end You will do COMMIT, all GO without error will be commited. Is kind of tricky. – T.Z. Jun 07 '13 at 07:19
  • @DarkAvanger - you may be correct (never tried that). However, I wasn't trying to imply that you can NOT have multiple GOs in a single transaction - merely, that when having no declared transaction, GO will create one for you. – DVK Jun 07 '13 at 13:01
  • 7
    `GO` does not "create a transaction for you.". If you aren't running in an explicit transaction each statement will create its own transaction anyway. It is completely orthogonal. If you want to split a larger update into smaller steps you can still do it in a single batch as in the common `WHILE @@ROWCOUNT > 0` pattern. – Martin Smith Dec 20 '13 at 20:39
  • @MartinSmith - sorry, but how is that NOT "creating a transaction" then? – DVK Dec 20 '13 at 20:43
  • 3
    If you aren't running in an explicit transaction then `UPDATE T1 SET X =2;UPDATE T1 SET X =2;` will run as two separate transactions *anyway*. The addition of `GO` makes absolutely no difference. And similarly if you *are* running in an explicit transaction it spans batches and again `GO` makes no difference. – Martin Smith Dec 20 '13 at 20:46
  • @MartinSmith - oh. I see. You're saying that GO doesn't actually do anything that NOT having a GO would? I'm uncertain if that's the case, at least in AquaDataStudio and isql, but willing to take your word for it – DVK Dec 20 '13 at 20:52
  • 4
    Just as clarification for anyone reading this later... `GO` has absolutely nothing to do with transactions at all, and makes the answers second point about transactions and the size of a log file incorrect. `GO` will have no effect whatsoever. The first and third answers are correct. In addition, there are times in which you need to separate statements into separate batches, for example you can't add a column to a table and then use that column later in the same batch. (continued) – Robert McKee Apr 05 '16 at 16:12
  • 4
    Additionally, because some errors will abort a batch (some errors only abort a statement), it plays a role in error detection and recovery as well. And certain statements (`CREATE VIEW`, etc) need to be in their own batch. – Robert McKee Apr 05 '16 at 16:15
27

GO is not a statement, it's a batch separator.

The blocks separated by GO are sent by the client to the server for processing and the client waits for their results.

For instance, if you write

DELETE FROM a
DELETE FROM b
DELETE FROM c

, this will be sent to the server as a single 3-line query.

If you write 

DELETE FROM a
GO
DELETE FROM b
GO
DELETE FROM c

, this will be sent to the server as 3 one-line queries.

GO itself does not go to the server (no pun intended). It's a pure client-side reserved word and is only recognized by SSMS and osql.

If you will use a custom query tool to send it over the connection, the server won't even recognize it and issue an error.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 6
    Why do you have to batch at all?? – PositiveGuy Apr 19 '10 at 15:43
  • 4
    So then GO means send it and then don't run the next batch until the client receives "OK, that batch is done and was successful" basically is what the GO does so that the next batch can be run successfully and the client knows for sure the batch before it is done server-side. – PositiveGuy Apr 19 '10 at 15:44
  • 3
    @coffeeaddict: basically, yes. In addition, some statements require to be first in their batches (like `CREATE SCHEMA`); other require being the *only* statements in their batches (like `SET SHOWPLAN_XML ON`) – Quassnoi Apr 19 '10 at 15:48
21

Many command need to be in their own batch, like CREATE PROCEDURE

Or, if you add a column to a table, then it should be in its own batch. If you try to SELECT the new column in the same batch it fails because at parse/compile time the column does not exist.

GO is used by the SQL tools to work this out from one script: it is not a SQL keyword and is not recognised by the engine.

These are 2 concrete examples of day to day usage of batches.

Edit: In your example, you don't need GO...

Edit 2, example. You can't drop, create and permission in one batch... not least, where is the end of the stored procedure?

IF OBJECT_ID ('dbo.uspDoStuff') IS NOT NULL
    DROP PROCEDURE dbo.uspDoStuff
GO
CREATE PROCEDURE dbo.uspDoStuff
AS
SELECT Something From ATable
GO
GRANT EXECUTE ON dbo.uspDoStuff TO RoleSomeOne
GO
gkrogers
  • 8,126
  • 3
  • 29
  • 36
gbn
  • 422,506
  • 82
  • 585
  • 676
5

Sometimes there is a need to execute the same command or set of commands over and over again. This may be to insert or update test data or it may be to put a load on your server for performance testing. Whatever the need the easiest way to do this is to setup a while loop and execute your code, but in SQL 2005 there is an even easier way to do this.

Let's say you want to create a test table and load it with 1000 records. You could issue the following command and it will run the same command 1000 times:

CREATE TABLE dbo.TEST (ID INT IDENTITY (1,1), ROWID uniqueidentifier)
GO
INSERT INTO dbo.TEST (ROWID) VALUES (NEWID()) 
GO 1000

source: http://www.mssqltips.com/tip.asp?tip=1216

Other than that it marks the "end" of an SQL block (e.g. in a stored procedure)... Meaning you're on a "clean" state again... e.G: Parameters used in the statement before the code are reset (not defined anymore)

Steav
  • 1,478
  • 13
  • 28
  • Ok, so why do you need GO. So that you know the table was created before the insert statement is run? I still don't get it. – PositiveGuy Apr 19 '10 at 15:15
  • See The way I think about this, is that if I don't have GOs in your example, the Table is created first, it's there now, so the insert should work. I don't get what the GO is for if I created the table...it's available to the next insert isn't it?!?!?! – PositiveGuy Apr 19 '10 at 15:20
  • 2
    @coffeeaddict: no. the "batch" is parsed and compiled in one go. At compile time, dbo.TEST does not exist. You aren't instantiating an object and SQL is not line by line procedural code – gbn Apr 19 '10 at 15:25
3

As everyone already said, "GO" is not part of T-SQL. "GO" is a batch separator in SSMS, a client application used to submit queries to the database. This means that declared variables and table variables will not persist from code before the "GO" to code following it.

In fact, GO is simply the default word used by SSMS. This can be changed in the options if you want. For a bit of fun, change the option on someone else's system to use "SELECT" as a batch seperator instead of "GO". Forgive my cruel chuckle.

The Dixie Flatline
  • 57
  • 1
  • 2
  • 1
    There is a actually a serious point to be made here: You should treat GO as if it were a keyword even though it isn't. You should also never change it. Bugs caused by reusing special identifiers can be very hard to debug. – Jørgen Fogh Jun 16 '15 at 11:36
  • @The Dixie Flatline: are you sure about the declared variables not persisting? In MSSQL 2016 I get a "variable already declared" error when running: declare $test int; set $test = 5; select $test go; declare $test int; -- Replace $ with , can't use multiple in SE comments. – Wouter Oct 10 '16 at 10:08
0

It is used to split logical blocks. Your code is interpreted into sql command line and this indicate next block of code.

But it could be used as recursive statement with specific number.

Try:

exec sp_who2  
go 2

Some statement have to be delimited by GO:

use DB
create view thisViewCreationWillFail
Deadsheep39
  • 561
  • 3
  • 16