What is a “batch”, and why is GO used?

by

GO is not properly a TSQL command.

Instead it’s a command to the specific client program which connects to an SQL server (Sybase or Microsoft’s – not sure about what Oracle does), signalling to the client program that the set of commands that were input into it up till the “go” need to be sent to the server to be executed.

Why/when do you need it?

  • GO in MS SQL server has a “count” parameter – so you can use it as a “repeat N times” shortcut.

  • Extremely large updates might fill up the SQL server’s log. To avoid that, they might need to be separated into smaller batches via go.

    In your example, if updating for a set of country codes has such a volume that it will run out of log space, the solution is to separate each country code into a separate transaction – which can be done by separating them on the client with go.

  • Some SQL statements MUST be separated by GO from the following ones in order to work.

    For example, you can’t drop a table and re-create the same-named table in a single transaction, at least in Sybase (ditto for creating procedures/triggers):

> drop table tempdb.guest.x1          
> create table tempdb.guest.x1 (a int)
> go
  Msg 2714, Level 16, State 1
  Server 'SYBDEV', Line 2
  There is already an object named 'x1' in the database.   
  
> drop table tempdb.guest.x1          
> go
> create table tempdb.guest.x1 (a int)
> go
>

More Related Contents:

  1. Levenshtein distance in T-SQL
  2. How to get sp_executesql result into a variable?
  3. How can I use optional parameters in a T-SQL stored procedure?
  4. Select query to remove non-numeric characters
  5. T-SQL Decimal Division Accuracy
  6. Selecting a Record With MAX Value
  7. T-SQL Pivot? Possibility of creating table columns from row values
  8. Getting the minimum of two values in SQL
  9. TSQL divide by zero encountered despite no columns containing 0
  10. T-SQL loop over query results
  11. How to insert default values in SQL table?
  12. How to Round a Time in T-SQL
  13. T-SQL CASE Clause: How to specify WHEN NULL
  14. How to convert a “dd/mm/yyyy” string to datetime in SQL Server?
  15. Return a value if no rows are found in Microsoft tSQL
  16. Check if a varchar is a number (TSQL)
  17. How to pivot table with T-SQL?
  18. Using Aliases in Where Clause or an Alternative Option?
  19. Extract the first word of a string in a SQL Server query
  20. Testing for inequality in T-SQL
  21. Can I use the MERGE statement in SQL Server 2005?
  22. Replace a newline in TSQL
  23. Search for one value in any column of any table inside a database
  24. How to use an Alias in a Calculation for Another Field
  25. Check if a varchar is a number (T-SQL)
  26. Using a cursor with dynamic SQL in a stored procedure
  27. Truncate Datetime to Second (Remove Milliseconds) in T-SQL
  28. Convert unix epoch timestamp to TSQL datetime
  29. Poor Man’s SQL Pivot. List Questions as Columns and Answers per User in one row
  30. How to parse string into date?

Leave a Comment