What is the use of GO in SQL Server Management Studio & Transact SQL?

Question

SQL Server Management Studio always inserts a GO command when I create a query using the right click "Script As" menu. Why? What does GO actually do?

Kohl

@ChrisF -- that's not a duplicate, though the accepted answer also answers this question. That question is about using "GO" in a transaction -- it just turns out that it's not really a SQL command at all. This question is much more general and attempts to provide a definitive answer for questions about the GO command in SSMS. – Kohl

Also take a look at this link: What are batching statements good for? – Motorboating

Microsoft documentation: SQL Server Utilities Statements - GO: The batch preceding GO will execute the specified number of times. – Augend

Answer 1

It is a batch terminator, you can however change it to whatever you want

Berton

gbn make it SELECT and look at what happens :-) – Berton

Thanks! However then what is the point of the GO statement? This may sound stupid but what does 'batch of code' mean? msdn says after GO the variables' lifespan expire. Sounds nothing to do with transaction commitment right? Is there any circumstances where I should keep the GO statement in my scripts? – Tenpin

It means that all T-SQL prior to it will execute "at once". From what I understand, it is interchangeable with a semicolon (OLEDB/Oracle). For instance if you have a large post deployment script, a GO statement between lines may help memory used in the script. – Baillargeon

This answer doesn't really explain "what it actually does" or the why – Enterectomy

After reading the answer of @tvanfosson: Is it really interchangable with semicolon? – Varuna

@Enterectomy i Agree... i still have no clue what is batch terminator supposed to mean in this context – Dannica

Can it be replaced by ; (semicolon) at the end of the statment? – Biophysics

@Enterectomy @ahmed NO the GO is not the same as the semi-colon ; statement terminator - the omission of the semi-colon ; on statements is also deprecated (a fun task to go put them all in your SQL when that omission gets enforced) – Sneeze

Answer 2

Since Management Studio 2005 it seems that you can use GO with an int parameter, like:

INSERT INTO mytable DEFAULT VALUES
GO 10

The above will insert 10 rows into mytable. Generally speaking, GO will execute the related sql commands n times.

Rebarebah

Simplicity is the ultimate sophistication. Leonardo Da Vinci – Warehouse

What do you mean by "related sql commands"? Is it the command above? All commands above? The commands above until the previous GO if there is one? – Hightest

Answer 3

The GO command isn't a Transact-SQL statement, but a special command recognized by several MS utilities including SQL Server Management Studio code editor.

The GO command is used to group SQL commands into batches which are sent to the server together. The commands included in the batch, that is, the set of commands since the last GO command or the start of the session, must be logically consistent. For example, you can't define a variable in one batch and then use it in another since the scope of the variable is limited to the batch in which it's defined.

For more information, see http://msdn.microsoft.com/en-us/library/ms188037.aspx.

Kohl

In what situation is batching things with GO actually useful? – Carrick

@Carrick See #20711826 – Motorboating

so if 1 statement in the batch fails, do all fails? – Jenellejenesia

Answer 4

GO is not a SQL keyword.

It's a batch separator used by client tools (like SSMS) to break the entire script up into batches

Answered before several times... example 1

Troxell

In my defense I did look through the suggested duplicates before I submitted the question -- and your example didn't show up, nor is it really a duplicate, though the answer is applicable. – Kohl

It is hard to search for "GO" here :-) – Troxell

Answer 5

Just to add to the existing answers, when you are creating views you must separate these commands into batches using go, otherwise you will get the error 'CREATE VIEW' must be the only statement in the batch. So, for example, you won't be able to execute the following sql script without go

create view MyView1 as
select Id,Name from table1
go
create view MyView2 as
select Id,Name from table1
go

select * from MyView1
select * from MyView2

Reportage

Also create procedures – Keitel

This is the only answer that really addresses the first part of the OP's question "...always inserts a GO command.... Why?". It seems, most of the time, out of fear. The only time it's required is when a command must be in it's own batch. – Riff

This answer is why this GO command really matters – Arborization

Also true for CREATE TABLE and a few other similar statements - with proper table creation syntax of course for that statement. – Sneeze

Answer 6

I use the GO keyword when I want a set of queries to get committed before heading on to the other queries.

One thing I can add is, when you have some variables declared before the GO command you will not be able to access those after the GO command. i.e

DECLARE @dt DateTime = GETDATE();
UPDATE MyTable SET UpdatedOn = @dt where mycondition = 'myvalue';
GO

-- Below query will raise an error saying the @dt is not declared.
UPDATE MySecondTable SET UpdatedOn = @dt where mycondition = 'myvalue'; -- Must declare the scalar variable "@dt".
GO

---

Update

I see, people requesting when to use the Go command, so I thought, I should add why I use the Go command in my queries.

When I have huge updates in the tables and I usually run these updates while going off from work (which means, I wouldn't be monitoring the queries), since it is convenient to come the next day and find the tables ready for other operations.

I use Go command when I need to run long operations and want to separate the queries and complete part of the transactions such as:

-- First Query
Update MyBigTable1 SET somecol1='someval1' where somecol2='someval2'
GO
-- Second Query
Update MyBigTable2 SET somecol1='someval1' where somecol2='someval2'
GO
-- Third Query
Update MyBigTable3 SET somecol1='someval1' where somecol2='someval2'

Executing above queries will individually commit the modifications without resulting in huge roll-back logs formation. Plus if something fails on third query, you know first 2 queries were properly executed and nothing would be rolled-back. So you do not need to spend more time updating/deleting the records again for the previously executed queries.

To sum it up in just one sentence, "I use the GO command as a check point as in the video games." If you fail after the check point (GO command), you do not need to start over, rather your game starts from the last check point.

Bellbird

so far the only answer that make sense. Thank you! – Monson

Is this a shorthand then for opening 3 different transactions and using try catch rollback in each of them? – Islander

Answer 7

Go means, whatever SQL statements are written before it and after any earlier GO, will go to SQL server for processing.

Select * from employees;
GO    -- GO 1

update employees set empID=21 where empCode=123;
GO    -- GO 2

In the above example, statements before GO 1 will go to sql sever in a batch and then any other statements before GO 2 will go to sql server in another batch. So as we see it has separated batches.

Veiled

What does batch mean? The commands are executed in sequence? – Hightest

Does this mean if you don't use "GO" you could get the updated employees although the select should be executed first? – Impatient

Answer 8

Use herDatabase
GO ; 

Code says to execute the instructions above the GO marker. My default database is myDatabase, so instead of using myDatabase GO and makes current query to use herDatabase

Snoddy

And why shouldn't it work without "GO"? I just tested it - there's no difference. "GO" seems to be redundant for the "USE" statement. – Impatient

Answer 9

One usage that I haven't seen listed is Error Resilience. Since only the commands between two GOs are run at a time, that means a compile error in one command can be separated from others. Normally any compile errors in a batch cause the entire thing to not be executed.

exec do.Something
GO
sel from table
print 'here'
GO
print 'there'

In above, 'here' will not be printed because of the error in the 'sel' statement.

Now, adding a GO in the middle:

exec do.Something
GO
sel from table
GO
print 'here'
GO
print 'there'

You get an error for 'sel' as before, but 'here' does get output.

Tripp

Answer 10

tldr; In most cases nowadays GO is mostly IMO optional. Using GO is best in LARGE transaction batches where you would have compiled many different scripts together in a large script and don't want errors where similar variables are used and so that parts of the transaction is committed to the server when desired instead of all of the script being rolled back due to an error.

LARGE TRANSACTION 1 --> Runs Successfully

GO; --> Is in the server

LARGE TRANSACTION 2 --> Runs Successfully

GO; --> Is in the server

LARGE TRANSACTION 3 --> Errors

GO; --> Without the other GO statements this would rollback Transaction 1 & 2

---

Not sure the best way to provide this SO wise however I do feel like what I've read so far doesn't really sum it all up and include an example that I've come across.

As stated many times before GO simply "commits" a batch of commands to the server.

I think understanding sessions also helps with understanding the necessity (or optionality) of the GO statement.

(This is where my technicality may fail but the community will point it out and we can make this answer better)

Typically developers are working in a single session and typically just executing simple statements to the database. In this scenario GO is optional and really...all one would do is throw it at the end of their statements.

Where it becomes more helpful is probably an option given by Jamshaid K. where you would have many large transactions that you would want committed in turn instead of all transactions being rolled back when one fails.

The other scenario where this also becomes helpful (which is the only other spot I've experienced it) is where many small transactions are compiled into one large script. For example

Dev 1 makes script 1

Dev 2 makes script 2

Dev 1 makes script 3

In order to deploy them a python script is written to combine the scripts so Script Master = script1 + script 2 + script 3.

GO statements would be required in the 3 scripts otherwise there could be errors where the scripts use conflicting variables or if script 3 fails the transactions from scripts 1 and 2 would be rolled back.

Now this process is probably archaic given current CI/CD solutions out there now but that would probably be another scenario where I could see GO being helpful/expected.

Palaver

Answer 11

GO means asking SQL repeat this whatever number you add next to it. Just like saying in English; "Hey GO there 3 times.". Try below in SQL and the result will be rendering table 3 times.

    SELECT * FROM Table
    GO 3

Living

Answer 12

According to docs "GO signals the end of a batch of Transact-SQL statements to the SQL Server utilities". But other than that, it has [count] argument to repeat preceding batch [count] time. Very useful for sequential inserts :)

Orethaorferd

Answer 13

It is a command to separate queries. If you are doing multiple selects it doesn't make a huge difference, the main use for me for example is when I am creating scripts and you need to create stored procedures and after give access or execute them. For example:

CREATE OR ALTER PROCEDURE dbo.select1
AS
BEGIN
    SET NOCOUNT ON
        
    SELECT 1
END

EXEC dbo.select1

This one it will create the stored procedure with everything on it, including the EXEC and it would end-up in a loop. So that GO it will say that after end create the stored proc and after execute it.

CREATE OR ALTER PROCEDURE dbo.select1
AS
BEGIN
    SET NOCOUNT ON
        
    SELECT 1
END
GO
EXEC dbo.select1

Engstrom

Answer 14

If you are coming from a C# background. GO statement is just like our Task.Wait command. Just like Task.Wait, it tells SQL server compiler to first complete the execution of SQL query before GO keyword and then continue to execute the following queries after that. Hope that helps :)

Spindling