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

SQL Server Management Studio always inserts a GO command when I cr开发者_JS百科eate a query using the right click "Script As" menu. Why? What does GO actually do?

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

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.

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.

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

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

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.

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

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 MyBigTable SET somecol1='someval1' where somecol2='someval2'
GO
-- Second Query
Update MyBigTable1 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.

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.

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