SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Using GO after USE (and GO in general)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

schroeds
Starting Member

1 Posts

Posted - 09/26/2013 :  21:26:04  Show Profile  Reply with Quote
As a standard practice, I begin my queries with the following:

USE <dbname>;
GO

<rest of my query>

When dbname exists, this works fine, and including GO after a USE statement seems standard practice in lots of "authoritative" places, like all of the BOL examples. However, the whole point of the USE statement is to set the database that the code that follows it will run against, but by including GO, the statements that follow GO will run against whatever database was previously set if dbname doesn't exist. I understand that this is exactly as GO is designed - it separates batches, and the batches are executed independently. But given that, it seems that including GO after a USE statement is a decidedly bad idea - if the database indicated in the USE statement doesn't exist, but the current database happens to be one with matching table and column names (such as when switching between test and production environments), the subsequent statements will execute against a different database than the one intended.

So given that behavior, why do all of the examples use the "USE <dbname>...GO...<statements>" paradigm instead of simply "USE <dbname>...<statements>"?

There are other places where this same type of structure exists. For instance, if you create a new stored procedure by right-clicking Stored Procedures > New Stored Procedure, the following code is generated at the beginning of the procedure template:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Again, it seems like an unnecessary use of GO. In this case, the only info I could find on scope is that these options are typically set in stored procs, and the scope in that case is limited to the stored proc. But I couldn't find any more general info on what the thinking/best practices are around when to use GO. Put another way, it seems to be used a lot where I don't see what it accomplishes, and I haven't seen anything that explains what helpful purpose it serves. I see two possibilities from what I've read: (1) that an execution plan is created for each batch (= group of statements separated by GO), and I can see how grouping statements to influence the execution plan could be helpful in some instances; and (2) to have a group of statements execute multiple times (GO <n>). But I see GO used all over the place, and I've never seen it used for either of these two reasons.

Thanks in advance for any insight anyone can provide!

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 09/27/2013 :  02:36:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
GO is a batch separator. It executes all statements "above" until previous batch separator.
If the batch fail, you will have to use error handling code for that. Otherwise SSMS will continue with next batch almost no matter what.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 09/27/2013 :  02:36:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
GO is a batch separator. It executes all statements "above" until previous batch separator.
If the batch fail, you will have to use error handling code for that. Otherwise SSMS will continue with next batch almost no matter what.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000