Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
schroeds
Starting Member
1 Post |
 Posted - 2013-09-26 : 21:26:04
|
| 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 ONGOSET QUOTED_IDENTIFIER ONGOAgain, 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
30421 Posts |
Posted - 2013-09-27 : 02:36:18
|
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 |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-09-27 : 02:36:19
|
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 |
|
|
|
|
|
|
|
|
Using GO after USE (and GO in general)