GO After every T-SQL statement

Question

What is the reasoning behind using the GO statement after every SQL statement? I understand that GO signals the end of batch and/or allows the reputation of statements but what advantage does it have using it after every statement.

I am just curious as a lot of Microsoft documentation etc. have started using it after every statement or maybe I've just started to notice.

Also what's considered best practice?

Answer 1

Before answering when to use it and why, it's first paramount in understanding exactly what GO is, and what it isn't.

The keyword GO is used by SQL Server Management Studio and SQLCMD in order to signify one thing and only one thing: The end of a batch of statements. In fact, you can even change what you use to terminate batches to something other than "GO":

That above screenshot is an option within SSMS that is configurable.

But what is a batch?? This BOL reference says it best:

A batch is a group of one or more Transact-SQL statements sent at the same time from an application to SQL Server for execution.

Simple as that. It's just a custom way an application (yes... an application) sends statements to SQL Server. Let's see an application-looking example of this. I'll use PowerShell to mimic what an application would do to send statements and batches to SQL Server:

$ConnectionString = "data source = SomeSQLInstance; initial catalog = AdventureWorks2012; trusted_connection = true; application name = BatchTesting;"

try {
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.Connection = $SqlConnection

    # first batch of statements
    #
    $SqlCmd.CommandText = "
        select * from humanresources.department where departmentid = 1;
        select * from humanresources.department where departmentid = 2;
        select * from humanresources.department where departmentid = 3;
        select * from humanresources.department where departmentid = 4;"

    # execute the first batch
    #
    $SqlConnection.Open()
    $SqlCmd.ExecuteNonQuery()
    $SqlConnection.Close()

    # second batch of statements
    #
    $SqlCmd.CommandText = "
        select * from humanresources.department where departmentid = 5;
        select * from humanresources.department where departmentid = 6;
        select * from humanresources.department where departmentid = 7;
        select * from humanresources.department where departmentid = 8;"

    # execute the second batch
    #
    $SqlConnection.Open()
    $SqlCmd.ExecuteNonQuery()
    $SqlConnection.Close()
}
catch {
    $SqlCmd.Dispose()
    $SqlConnection.Dispose()
    Write-Error $_.Exception
}

The comments give it away, but you can see above that we're programmatically sending two batches to SQL Server. Let's verify that, though. My choice here is to use Extended Events:

create event session BatchTesting
on server
add event sqlserver.sql_batch_starting
(
    set
        collect_batch_text = 1
    where
    (
        sqlserver.client_app_name = N'BatchTesting'
    )
),
add event sqlserver.sql_batch_completed
(
    set
        collect_batch_text = 1
    where
    (
        sqlserver.client_app_name = N'BatchTesting'
    )
),
add event sqlserver.sql_statement_starting
(
    set
        collect_statement = 1
    where
    (
        sqlserver.client_app_name = N'BatchTesting'
    )
),
add event sqlserver.sql_statement_completed
(
    set
        collect_statement = 1
    where
    (
        sqlserver.client_app_name = N'BatchTesting'
    )
)
add target package0.event_file
(
    set
        filename = N'<MyXelLocation>\BatchTesting.xel'
);
go

alter event session BatchTesting
on server
state = start;
go

All this XEvents session is doing is capturing the statements and batches that start and complete from an application named "BatchTesting" (if you notice my connection string in my PowerShell code example, it's a quick way to look at a particular originator of events by using the "application name" connection string parameter and filtering off of that).

After I execute the PowerShell code to send those batches and statements, I see the following results:

As you can see from the screenshot, it's clear how the statements are divided up into the two different batches, also evident by the means we used to call the batches. And if we look in the batch_text of the first occurrence of sql_batch_starting, we can see all of the statements included in that batch:

    select * from humanresources.department where departmentid = 1;
    select * from humanresources.department where departmentid = 2;
    select * from humanresources.department where departmentid = 3;
    select * from humanresources.department where departmentid = 4;

With that explanation of what a batch is, now comes the answer to your question of when to terminate batches. The rules for batches are found on this BOL reference regarding batches:

CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must start the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement.

A table cannot be changed and then the new columns referenced in the same batch.

If an EXECUTE statement is the first statement in a batch, the EXECUTE keyword is not required. The EXECUTE keyword is required if the EXECUTE statement is not the first statement in the batch.

Likewise, certain runtime errors (compile errors won't allow the execution of a batch to start) that occur during a batch can cause different behaviors: to abort the batch totally, or to continue the batch and only abort the offending statement (the above link gives two really good examples: An arithmetic overflow error, for instance, will stop the execution of the batch, whereas a constraint violation error will only prevent the current statement from completing but the batch will continue executing).

Like many things in our profession, though, personal preference will be a huge driving force behind how you as an individual and writer of T-SQL code terminate batches. Some people only explicitly define batches when they absolutely have to (see above for those requirements), and others terminate batches programmatically 100% of the time, even when they are only executing a single statement in a Query Window in SSMS. Most people typically fall somewhere in the middle of those two boundaries. For what it's worth, statement terminators have a same following with also very few enforced requirements. A big part of all of this is code style, where it isn't enforced (in SSMS and SQLCMD).