CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Nestor
Nestor

Reputation: 13990

What is the difference between ";" and "GO" in T-SQL?

I use ADO.NET as well as the sqlcmd utility to send SQL scripts to SQL Server 2008. What is the difference between using ; and GO to separate chunks of SQL?

Upvotes: 60

Views: 45453

Answers (7)

keithwarren7
keithwarren7

Reputation: 14280

GO is not actually a T-SQL command. The GO command was introduced by Microsoft tools as a way to separate batch statements such as the end of a stored procedure. GO is supported by the Microsoft SQL stack tools but is not formally part of other tools.

You cannot put a GO into a string of SQL and send it as part of a ADO.NET command object as SQL itself does not understand the term. Another way to demonstrate this is with the profiler: set up some statements that use GO in Query Analyzer/Management Studio and then run the profiler when you execute. You will see they are issued as separate commands to the server.

The semi-colon is used to signify the end of a statement itself, not necessarily a whole batch.

http://msdn.microsoft.com/en-us/library/ms188037.aspx

Upvotes: 63

alejandrob
alejandrob

Reputation: 681

  1. Under SQL Server TSQL (2005 - 2016) bear in mind that:

    • Semicolon (;) is a block terminator.
    • GO is a batch terminator.

  2. Additionally, GO can be used to invoke the same DML block multiple times using the following syntax:

GO [count]

Where [count] is a positive integer that indicates how many times the TSQL block of commands preceding said GO are to be carried out over and over.

  1. Also, unlike semicolon, GO is mandatory before a new DDL, say, when you create a new view, since a semicolon separating previous commands will trigger an error. For example:

drop view #temporary_view
GO
create view #another_view...
--> NO ERRORS

If you replaced GO with a semicolon in the previous example, it will raise the following error message:

'CREATE VIEW' must be the first statement in a query batch.

Upvotes: 16

valerius.kaiser
valerius.kaiser

Reputation: 21

The command GO means the end of a batch. Therefore all variables declared before GO are invalid after the GO command. Against the semicolon does not end the batch.

If You will use a DML command in a procedure, use the semicolon instead GO. For example:

CREATE PROCEDURE SpMyProc
@myProcParam VARCHAR(20)
AS
DECLARE @myOtherParam INT = 5
;DISABLE TRIGGER ALL ON tMyTable
UPDATE tMyTable SET myVar = @myProcParam, mySecondVar = @myOtherParam
;ENABLE TRIGGER OLL ON tMyTable

Upvotes: 2

Bob Kaufman
Bob Kaufman

Reputation: 12815

semicolon is a statement separator. The previous statement(s) is not necessarily executed when a semicolon is encountered.

GO

Signifies the end of a batch. Executes the previous batch of statements, as does encountering the end of the block.

GO 2

Means execute the batch that many times. I think I've used that option maybe twice in my life. Then again, I'm not a DBA by trade.

Upvotes: 23

Phillip Knauss
Phillip Knauss

Reputation: 678

"GO" is similar to ; in many cases, but does in fact signify the end of a batch.

Each batch is committed when the "GO" statement is called, so if you have:

SELECT * FROM table-that-does-not-exist;
SELECT * FROM good-table;

in your batch, then the good-table select will never get called because the first select will cause an error.

If you instead had:

SELECT * FROM table-that-does-not-exist
GO
SELECT * FROM good-table
GO

The first select statement still causes an error, but since the second statement is in its own batch, it will still execute.

GO has nothing to do with committing a transaction.

Upvotes: 52

Mike J
Mike J

Reputation: 3149

'GO' is typically used to indicate the end of a batch of SQL statements which means that you could have a begin transaction and end transaction wrapped up into a single collection of statements that could fail or succeed together.

';' is generally used to separate multiple SQL statements from one another. This is noticable in SQL scripts that need to return multiple recordsets, such as `select * from table1; select * from table2;' which would result in two separate recordsets on the client's side.

Upvotes: 4

Phil.Wheeler
Phil.Wheeler

Reputation: 16848

I thought the ; character separates a list of SQL commands, GO just instructs SQL Server to commit all the previous commands.

Upvotes: 0

Related Questions