129

I’ve always been confused with when I should use the GO keyword after commands and whether a semi-colon is required at the end of commands. What is the differences and why/when I should use them?

When I run the Generate-script in SQL Server Management Studio, it seems to use GO all over the place, but not the semi-colon.

Palec
  • 12,743
  • 8
  • 69
  • 138
HAdes
  • 16,713
  • 22
  • 58
  • 74
  • 3
    possible duplicate of [What is the difference between ";" and "GO" in TSQL (SQL 2008) ?](http://stackoverflow.com/questions/1517527/what-is-the-difference-between-and-go-in-tsql-sql-2008) – JohnFx Sep 13 '10 at 14:16

5 Answers5

110

GO only relates to SSMS - it isn't actual Transact SQL, it just tells SSMS to send the SQL statements between each GO in individual batches sequentially.

The ; is a SQL statement delimiter, but for the most part the engine can interpret where your statements are broken up.

The main exception, and place where the ; is used most often is before a Common Table Expression Statement.

cjk
  • 45,739
  • 9
  • 81
  • 112
  • 8
    Emphasis on the `GO` isn't T-SQL Part while `;` is – msarchet Sep 13 '10 at 14:15
  • 2
    A `MERGE` statement must be terminated by a semi-colon. – onedaywhen Sep 15 '10 at 07:43
  • 15
    Update: in SQL Server 2012 NOT using semicolons is deprecated, meaning they will be required in next version. So it is a good practice to use semicolons, as it will require less work in case of migration (and because it is a standard). Source: http://technet.microsoft.com/en-us/library/ms143729.aspx or the book on SQL Server 2012: http://shop.oreilly.com/product/0790145321978.do – Paweł Bulwan May 04 '13 at 10:38
  • 2
    SQL 2014 still lists semicolon as required for next version. I wonder when it will be deprecated. http://technet.microsoft.com/en-us/library/ms143729.aspx – Frank Jul 28 '14 at 16:18
  • 1
    i dont think they can ever deprecated ";" that would cause a massive break – Furkan Gözükara Aug 17 '16 at 10:55
  • 2
    +buli is not stating that `;` is deprecated, NOT using it is deprecated, i.e. it is going to become mandatory according to the source. – cjk Aug 17 '16 at 15:23
  • 1
    No matter what version you choose, the link always points to `ms143729.aspx` and displays the same information. – Álvaro González Mar 14 '17 at 16:03
98

The reason why you see so many GO's in Generated DDL scripts is because of the following rule about batches.

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

One of the use cases for Generated DDL is to generate multiple objects in a single file. Because of this a DDL generator must be able to generate batches. As others have said the GO statement ends the batch.

Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • 12
    This is the only one who really answered the "... when should you use GO...?" part of the question. – Roimer Feb 04 '13 at 16:29
  • It seems to me that GO terminates statements that create objects containing SQL that may contain the ; statement terminator. In another DBMS I have seen the ability to set the termination string. For example, you might specify that statements end with two pipe characters ||. You could then issue CREATE PROCEDURE ... much SQL ending in ; ... || and the double pipe ends the CREATE PROCEDURE statement. So my question is whether this is analgous to the MS GO statement? Secondly, can you redefine the termination string in SQLSERVER? – youcantryreachingme Sep 13 '18 at 23:10
  • @youcantryreachingme I don't know if this was true in 2010 but it seems that you can. See [SSMS: Batch Separator](http://www.sqlservercentral.com/blogs/discussionofsqlserver/2018/04/18/ssms-batch-separator-day-35/). I'm not sure what the limits of this are – Conrad Frix Sep 14 '18 at 19:44
  • While answering the GO portion, it failed to address the difference between it and the semicolon. – pbaldridge Mar 06 '21 at 15:38
44

GO

Go is a batch separator. This means that everything in that batch is local to that particular batch.

Any declarations of Variables, Table Variables, etc do not go across GO statements.

#Temp tables are local to a connection, so they span across GO statements.

Semicolon

A Semicolon is a statement terminator. This is purely used to identify that a particular statement has ended.

In most cases, the statement syntax itself is enough to determine the end of a statement.

CTE's however, demand that the WITH is the first statement so you need a semicolon before the WITH.

Raj More
  • 47,048
  • 33
  • 131
  • 198
11

You should use a semi-colon to terminate every SQL statement. This is defined in the SQL Standards,

Sure, more often than not SQL Server allows you to omit the statement terminator but why get into bad habits?

As others have pointed out, the statement preceding a common table expression (CTE) must be terminated with a semi-colon. As a consequence, from folk who have not fully embraced the semi-colon terminator, we see this:

;WITH ...

which I think looks really odd. I suppose it makes sense in an online forum when you can't tell the quality of code it will be pasted into.

Additionally, a MERGE statement must be terminated by a semi-colon. Do you see a pattern here? These are a couple of the newer additions to TSQL which closely follow SQL Standards. Looks like the SQL Server team are going down the road of mandating the use of the semi-colon terminator.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • 2
    "*which I think looks really odd*" - I couldn't agree more. And the constant repetition of this leads to strange questions like [this](http://stackoverflow.com/questions/26670414/how-use-with-in-subquery-ms-sql) or [this](http://stackoverflow.com/questions/26233895/cte-execute-commands-before-using-the-cte-table) –  May 19 '15 at 06:24
  • SQL Server doesn't allow you to be sloppy - it allows you to omit a redundant statement terminator; T-SQL, like most other SQLs, is not pure standard SQL, which is more like an abstract base class - not used in practice in favour of the DB engine's own dialect. – ProfK Dec 03 '15 at 06:18
  • @ProfK: thank you for including punctuation in your comment, making it easy for me to read and understand, particularly the sentence terminator characters. Although, your usage of hyphens doesn't comply with standard English... – onedaywhen Dec 03 '15 at 09:39
  • Still, SQL Management Studio script generation wizard generates INSERTs without semicolons and with GOs. If I want my datadump to be imported efficiently and outside of SSMS, maybe I should replace all GOs with COMMIT (and add corresponding BEGIN TRANSACTION). – JustAMartin May 26 '21 at 14:22
8

GO is a batch terminator, a semi-colon is a statement terminator.

you will use GO when you want to have multiple create proc statements in 1 script because create proc has to be the first statement in a batch. If you use common table expressions then the statement before it needs to be terminated with a semi-colon

SQLMenace
  • 132,095
  • 25
  • 206
  • 225