What does "GO" do or why would it be used. 2

[grnzbra]

I've been assigned a task of modifying an existing T-SQL script in which "GO" has been liberally sprinkled. What exactly are all of these things doing?
Example:

USE DatabaseName
GO

EXEC master.dbo.sp_addlinkedserver
GO

DELETE TABLE1
GO

DELETE TABLE2
GO

CREATE TABLE1
GO

CREATE TABLE2
GO

 

[Olaf Doschke]

GO splits execution of a script into several batches. It's useful for whenever some action needs to be the first action in a script, for example, CREATE or ALTER of a PROCEDURE must be the first command in a batch.

What is kept between all the batches is a) the connection and b) several settings. Besides several things that also means temp tables are kept between batches, but declared variables are not.

In your shortened example I see it overly used, you actually don't need to put each CREATE TABLE into one batch, there could be one batch with multiple CREATE TABLE.

GO also can be used as GO [count], GO 10 would run something 10 times. For example to generate some test data:

Create Table #TestData (ID INT IDENTITY (1,1), CreatedDate DATETIME);
GO

INSERT INTO #TestData (CreatedDate) SELECT GetDate()
GO 20

Select  * From #TestData

GO doesn't compare to a COMMIT, though, and it seems a bit somebody thought it would be that. On the other hand when variables are released, as said, you can rely on two scripts you concatenate and still keep two batches split by GO won't influence each other with variables reused or cause errors due to redeclarations. So whether GOs are utterly overused also is a matter of taste or stringent usage.

Bye, Olaf.

 

[grnzbra]

Thank you.
You've solved my problem.

 

[gbaughma]

Huh. All these years working with SQL, and I didn't know about the GO <number>

Shows you can teach an old dog new tricks.


Just my $.02

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[gmmastros]

An important thing to understand is that GO is not part of T-SQL, it's actually part of SQL Server Management Studio. For example, if you try to execute GO from another application, you will get an error.

If you ever want to play a prank on a co-worker... change GO to SELECT. In SQL Server Management Studio, click Tools -> Options. On the Options window, Expand "Query Execution", and then expand "SQL Server". On the right, change "Batch Separator" to "SELECT" instead of GO.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

Good point.

Other application and clients also include sqlcmd.exe, but that's not the only incompatibility with SSMS anyway.

Bye, Olaf.